SQL Query CONTAINS property value in array of objects-CodePudding

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.

CodePudding user response：

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.