CODEWITHSUNDEEP
c#azure-cosmosdbazure-cosmosdb-sqlapi
hello world
hello world

Reputation: 425

Query cosmos database with an array to filter document

I'm trying to query cosmos to filter down document that contains any match of my array of integers.

Upvotes: 0

Views: 322

Answers (1)

Mohit Verma
Mohit Verma

Reputation: 5294

You can simply use In operator to query the docs like below:

Where c.Id in ("sdsd", "sdsds" )

SELECT * FROM c WHERE c.Id  in (listOfId )

It works.

For complex query ,You can use Array_Contains which is build in function. Suppose you have a document like below:

[
    {
        "videoGameId": "1",
        "videoGameName": "Battle Royale Kingdoms",
        "selectedLevels": [
            {
                "title": "Jungle Arena",
                "towers": 2,
                "towerPower": 40
            },
            {
                "title": "Legendary World",
                "towers": 5,
                "towerPower": 100
            }
        ]
    },
    {
        "videoGameId": "2",
        "videoGameName": "Fortnite vs Zombies",
        "selectedLevels": [
            {
                "title": "Rainbows after the storm",
                "maximumPlayers": 30,
                "minimumExperienceLevel": 60
            },
            {
                "title": "The last of us",
                "maximumPlayers": 10,
                "minimumExperienceLevel": 100
            }
        ]
    }
]

The two documents in the VideoGames1 collection have the tags key with an array of strings with tags related to the video game. The following query will build a new document for each videogame that includes monsters as one of the string values of the tags key. The query takes advantage of the ARRAY_CONTAINS built-in function. This function returns a Boolean indicating whether the array received as an argument contains the specified value. 

SELECT v.id AS videoGameId,
    v.name AS videoGameName,
    v.tags AS videoGameTags
FROM Videogames v
WHERE ARRAY_CONTAINS(v.tags, "monsters")

Similarly you can write a query for your doc.

Additional reference:

https://novacontext.com/writing-and-running-queries-on-nosql-document-databases/

Hope it helps.

Upvotes: 1

Related Questions