Cosmos DB SQL API > Filter out child array objects with specific values

Question

I have a document with a property which is an array of objects. I would like to write a query that filters out objects from the child array. I thought the array_ contains would do the trick but it does not appear to filter the child array.

Query

SELECT Families.id, Families.parents
FROM Families
WHERE ARRAY_CONTAINS(Families.parents, { givenName: "Ben", familyName: "Wakefield" })

Result

[
    {
        "id": "WakefieldFamily",
        "parents": [
            {
                "familyName": "Wakefield",
                "givenName": "Robin"
            },
            {
                "familyName": "Miller",
                "givenName": "Ben"
            }
        ]
    }
]

Desired Result

[
    {
        "id": "WakefieldFamily",
        "parents": [
            {
                "familyName": "Wakefield",
                "givenName": "Ben"
            }
        ]
    }
]

Is this possible with the Cosmos DB SQL API?

Thank you,

Scott

Steve Zhao Steve Zhao · Accepted Answer · 2021-03-25T01:21:28

It seems you use official sample data as the item in your Cosmos DB container.

{
  "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" },
  "creationDate": 1431620462,
  "isRegistered": false
}

Your SQL will get the items which contains { givenName: "Robin", familyName: "Wakefield" } in parents array and return the whole parents array. This is why you didn't get your Desired Result.

SELECT Families.id, Families.parents
FROM Families
WHERE ARRAY_CONTAINS(Families.parents, { givenName: "Robin", familyName: "Wakefield" })

You can use Array() function to construct an array to add subquery's result to do this. Something like this:

SELECT Families.id, ARRAY(SELECT p.familyName,p.givenName FROM p in Families.parents WHERE p.familyName ='Wakefield' and p.givenName = 'Robin') AS parents
FROM Families

Result:

[
    {
        "id": "WakefieldFamily",
        "parents": [
            {
                "familyName": "Wakefield",
                "givenName": "Robin"
            }
        ]
    }
]

Cosmos DB SQL API > Filter out child array objects with specific values

1 Answers