Search for a substring in a DB document

Question

this is an example documentDB document,

I want to receive all documents that failed in one or more subjects

I found something like

SELECT * FROM students s JOIN c IN s.subjects WHERE c.result = "pass" 

I want to get using C # code

 { "id": "0066a253-f042-4213-b06e-65b1ea1e49aa", "name": "Sunny", "rollNo": 123, "class": "2nd", "section": "B", "Department": { "name": "CSE", "id": "cse", "subjects": [ { "id": "subject-1", "marksObtained": 66, "maxMarks": 100, "result": "pass" }, { "id": "subject-2", "marksObtained": 56, "maxMarks": 75, "result": "pass" }, { "id": "subject-3", "marksObtained": 22, "maxMarks": 100, "result": "fail" }, { "id": "subject-4", "marksObtained": 36, "maxMarks": 50, "result": "pass" }, { "id": "subject-5", "marksObtained": 16, "maxMarks": 100, "result": "fail" } ] }, "Type": "Student" } 

I tried so

 var result = client.CreateDocumentQuery<dynamic>(dc.SelfLink, "SELECT s.id as id,s.Name as Name,s.Age as Age,s.section as section,s.subjects as subjects FROM students s JOIN c IN s.subjects WHERE c.result = \"pass\"").ToList(); List<Student> students = new List<Student>(); foreach(var std in result) { students.Add((Student)std); } 

Something like the above is my code that I get, but even I give pa or pas or pass or p or ass, or as then I should also get something that I need for LIKE in SQL

Is there any solution for this? I need LIKE functionality in SQL to retrieve data from documentDB

Answer 1

Update: from 5/6/15 , DocumentDB added a set of string functions, including STARTSWITH , ENDSWITH and CONTAINS . Please note that most of these functions do not start by index and will be forced to scan.

Wildcards such as SQL LIKE '%%' are not yet implemented in DocumentDB.

Please express your opinion and vote for this feature in the DocumentDB feedback forum .

Answer 2

In the past few months, some new features have been introduced. For your specific case, I think you can use:

 WHERE STARTSWITH(c.result, "p")