Azure Cosmos SQL - working with nested arrays and using LIKE keyword

user_2328132 0
Viewed 457

For some time when I had to find particular element of array by particular value I've been using ARRAY_CONTAINS function. Now I have documents with nested arrays where I have to search not but particular value, but using regex.

As an example of document let me use one from official documentation:

{
  "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
}

What I need is to select and fully get all documents where at least one of children has at least one pets element where givenName contains "fluf".

What SQL query do I build to achieve it?

azure-cosmosdb-sqlapi azure-cosmosdb
1 Answers

Here's a query that uses JOINs to flatten out the inner pets arrays and apply a filter, then return the entire matching family items:

SELECT VALUE f
FROM Families f
JOIN c IN f.children
JOIN p IN c.pets
WHERE p.givenName LIKE "%Fluf%"

The complexity of figuring out such queries is one reason why I think it's worth considering modeling data to be as flat as possible, including normalizing out to have separate pets items for example, which can be queried with direct property filters without dealing with nesting. Combining everything into a large Family object as the examples do isn't necessarily a good idea in practice depending on your goals.

user_24117130
Related