CODEWITHSUNDEEP
jsonnosqlazure-cosmosdb
Jeppe Christensen
Jeppe Christensen

Reputation: 1890

Query document with a particular value in array of objects

I'm having a series of documents in cosmos DB where the structure is as follows:

{
    "id" : "s2912vdla"
    "allowed-usrs": [
        {
            "id": "myId"
        }
    ],
    ....
}

I need to be able to get all the documents, which has the id myId in allowed-usrs.id.

Unfortunately, its unclear for me how the SQL Cosmos API would achieve this - I've read in the docs that I should use the IN clause, but I can't seem to get it to work:

SELECT * FROM c IN t.allowed-usrs WHERE c.id = "myId"

Upvotes: 0

Views: 52

Answers (1)

NotFound
NotFound

Reputation: 6157

Try the following query:

SELECT VALUE c --because we have JOIN we can't use *
FROM c --we need c because we want to return the full object
JOIN a IN c['allowed-usrs'] --brackets required because the char '-' is not allowed
WHERE a.id = 'myId'

or maybe more correctly for your use the query below. The first query will yield duplicate items if myId appears multiple times in the allowed user list (because to the JOIN), while the below only yields one.

SELECT *
FROM c
WHERE ARRAY_CONTAINS(c['allowed-usrs'], { 'id': 'myId' }, true)
--looks for a partial match in the allowed user array

Upvotes: 1

Related Questions