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 ?

Solution 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")

Solution 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.

Comments

[Avinash Gadiraju]

Thanks for the answer.. I've learned I need to join only the array but not every node down. Thanks again, saved my day.

[Nathan Tregillus]

how would I do this using Linq?

[Tony Gutierrez]

select f.* is not valid for some reason? I have to specify all the attributes of the root element manually?

[Andrew Liu]

You can simply use SELECT f to get all attributes under the root document

[myusrn]

I'm trying this statement [ select * from c where array_contains(c.Parents.FirstName, "Ben") ] in azure docdb query explorer using sample record entries and getting empty result but [ select * from c where c.LastName = "Wakefield" ]. Unexpectedly the more complex join statement [ select f.LastName as familyName, p as parent from Familes f join p in f.Parents where p.FirstName = "Ben" ] works. Any insights as to what i'm overlooking to get the simpler array_contains approach working or where i should go to understand why this isn't working using what i'd expect given above q/a details?

[myusrn]

And similarly this simple order by [ select * from Familes f order by f._ts desc ] statement works but this one [ select * from Families f order by f.LastName desc ] returns an empty set which doesn't make sense. The help docs I've been reading say everything in json document is indexed and i even tried recreating collection with custom RangeIndex that has specific IndexingPolicy.IncludedPaths entry for "/LastName/?" and it didn't change the outcome.

[Krumelur]

This helped. But even that doesn't work for me as it's written. I had to put TimeBasis in quotes ("), like you did for "Value".

[Lynn]

@AndrewLiu Can you please give advice on my recent question? thank you

[Nicholas]

@Krumelur thanks for the heads up, just a typo in my answer. :-)