SQL Query CONTAINS property value in array of objects

Question

I am trying to create a SQL query to get a list of companies that a User belongs to. The database is Cosmos DB Serverless, and the container is called "Companies" with multiple company items inside:

The structure of the company items are as follows:

    {
        "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
        "name": "Company Name",
        "users": [
            {
                "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
                "name": "Susan Washington",
                "email": "[email protected]",            
                "createdBy": "[email protected]",
                "createdServerDateUTC": "2022-01-12T19:21:10.0644424Z",
                "createdLocalTime": "2022-01-12T19:21:09Z"
            },
            {
                "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
                "name": "Kerwin Evans",
                "title": "Test Dev",
                "email": "[email protected]",
                "createdBy": "[email protected]",
                "createdServerDateUTC": "2022-01-12T19:21:10.0644424Z",
                "createdLocalTime": "2022-01-12T19:21:09Z"
            },
        
            ETC.
        ]
    }

And this is the SQL query I was trying to use, where user is an email that I pass in:

    SELECT *
    FROM c
    WHERE IS_NULL(c.deletedServerDateUTC) = true
      AND CONTAINS(c.users, user)
    ORDER BY c.name DESC
    OFFSET 0 LIMIT 10

This doesn't work, because the users property is an array. So I believe I need to check each object in the users array to see if the email property matches the user I enter in.

Which dbms are you using? (The above query is product specific.) — jarlh
– jarlh, Commented May 31, 2022 at 12:37
@jarlh We are using SQL to query data from a Cosmos DB. This query is run in Visual Studio (C#) in an API function. (Microsoft.Azure.Cosmos.Client, Version=3.26.1.0) Does that answer your question? Sorry, I'm not great with the vocabulary. — PythonIsCool
– PythonIsCool, Commented May 31, 2022 at 12:46

David Makogon · Accepted Answer · 2022-05-31 13:56:33Z

3

You can query the array via ARRAY_CONTAINS(). Something like this to return company names for a given username that you specify:

SELECT c.name
FROM c
WHERE ARRAY_CONTAINS(c.users,{'name': username}, true)

The 3rd parameter set to true means the array elements are documents, not scalar values.

answered May 31, 2022 at 13:56

David Makogon

71k22 gold badges145 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

The Muffin Man Over a year ago

Also, to expand on this, if one of your arrays contains a nested array you need to join on the first array and then do an ARRAY_CONTAINS on the [joined first level array.nested array].

Collectives™ on Stack Overflow

SQL Query CONTAINS property value in array of objects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related