WHERE clause on an array in Azure DocumentDb

Question

In an Azure Documentdb document like this

{ "id": "WakefieldFamily", "parents": [     { "familyName": "Wakefield", "givenName": "Robin" },     { "familyName": "Miller", "givenName": "Ben" } ], "children": [     {         "familyName": "Merriam",          "givenName": "Jesse",          "gender": "female",          "grade": 1,         "pets": [             { "givenName": "Goofy" },             { "givenName": "Shadow" }         ]     },     {        "familyName": "Miller",        "givenName": "Lisa",        "gender": "female",        "grade": 8      } ],   "address": { "state": "NY", "county": "Manhattan", "city": "NY" },   "isRegistered": false }; 

How do I query to get children whose pets given name is "Goofy" ?

Looks like the following syntax is invalid

Select * from root r WHERE r.children.pets.givenName="Goofy" 

Instead I need to do

Select * from root r WHERE r.children[0].pets[0].givenName="Goofy" 

which is not really searching through an array.

Any suggestion on how I should handle queries like these ?

Answer 1

You should take advantage of DocumentDB's JOIN clause, which operates a bit differently than JOIN in RDBMs (since DocumentDB deals w/ denormlaized data model of schema-free documents).

To put it simply, you can think of DocumentDB's JOIN as self-joins which can be used to form cross-products between nested JSON objects.

In the context of querying children whose pets given name is "Goofy", you can try:

SELECT      f.id AS familyName,     c AS child,     p.givenName AS petName  FROM Families f  JOIN c IN f.children  JOIN p IN c.pets WHERE p.givenName = "Goofy" 

Which returns:

[{     familyName: WakefieldFamily,     child: {         familyName: Merriam,         givenName: Jesse,         gender: female,         grade: 1,         pets: [{             givenName: Goofy         }, {             givenName: Shadow         }]     },     petName: Goofy }] 

Reference: http://azure.microsoft.com/en-us/documentation/articles/documentdb-sql-query/

Edit:

You can also use the ARRAY_CONTAINS function, which looks something like this:

SELECT food.id, food.description, food.tags FROM food WHERE food.id = "09052" or ARRAY_CONTAINS(food.tags.name, "blueberries") 

Answer 2

I think the ARRAY_CONTAINS function has changed since this was answered in 2014. I had to use the following for it to work.

SELECT * FROM c WHERE ARRAY_CONTAINS(c.Samples, {"TimeBasis":"5MIN_AV", "Value":"5.105"},true) 

Samples is my JSON array and it contains objects with many properties including the two above.