DocumentDB - WHERE clause within collection

Question

Given a sample document like this one from the Microsoft examples:

{
  "id": "AndersenFamily",
  "lastName": "Andersen",
  "parents": [
     { "firstName": "Thomas" },
     { "firstName": "Mary Kay"}
  ],
  "children": [
     {
         "firstName": "Henriette Thaulow", 
         "gender": "female", 
         "grade": 5,
         "pets": [{ "givenName": "Fluffy" }]
     }
  ],
  "address": { "state": "WA", "county": "King", "city": "seattle" },
  "creationDate": 1431620472,
  "isRegistered": true
}

We can see that there is a sub-collection children containing an array of documents.

Let's say I wanted to write a query using the SELECT ... FROM ... WHERE ... type syntax, how would I go about writing a query to find families with any daughters (any children with gender "female")

So something like

SELECT c.id
FROM c
WHERE c.children.contains(  // I'm stuck!

I'm wondering if I'm missing a JOIN or something but honestly I'm not sure where I go from here, and I'm struggling to find anything helpful in Google partially because I'm not sure how to phrase my search!

Aravind Krishna R. Aravind Krishna R. · Accepted Answer · 2017-03-03T21:14:02

You need the JOIN keyword to unwind the children, then apply the filter on gender like the query below:

SELECT f.id
FROM family f
JOIN child IN f.children
WHERE child.gender = "female"

DocumentDB - WHERE clause within collection

1 Answers