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

Question

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?

I have no idea what to try :) If I would need to filter by parents I would use: SELECT * FROM d WHERE ARRAY_CONTAINS(d.parents, {"firstName": "myValue" }, true) but in my case I need to: 1. Somehow apply "LIKE" keyword and use a regex expression, because I don't have particular value. And I don't know how to use LIKE inside of ARRAY_CONTAINS. 2. I have nested array so I can't use ARRAY_CONTAINS directly.. — Vladimir Glushkov, Aug 06 '21 at 13:50

Noah Stahl · Accepted Answer · 2021-08-06T14:44:54.623

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.

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

1 Answers1

Linked