How to execute a query in DocumentDB based on the value of the internal json object?

Question

Suppose I have 3 objects in a DocumentDB like this.

This is a class entry.

And now I want to get all Id where the student exists with the name sunny.

{ "id": "111", "class": 1, "students": [ { "name": "sunny" }, { "name": "pinki" }, { "name": "bobby" }, { "name": "lucky" } ] } { "id": "222", "class": 2, "students": [ { "name": "pinki" }, { "name": "sunny" }, { "name": "bobby" } ] } { "id": "333", "class": 3, "students": [ { "name": "pinki" }, { "name": "lucky" }, { "name": "bobby" } ] } 

What will be the request to get the result?

Answer 1

You can use DocumentDB JOIN to create a cross-product in documents with array elements.

For example, the following query creates a cross product in documents with its students property to query students.name :

 select doc.id from doc join students in doc.students where students.name = 'sunny' 

returns the following data set:

 [{ id: 111 }, { id: 222 }] 

Link: http://azure.microsoft.com/en-us/documentation/articles/documentdb-sql-query/#joins