Cosmos SQL find items which has a string in array-CodePudding

I'm trying to write a query in CosmosDB using SQL for the following scenario. Let say I have a collection with items as follows

{
   "resource": "abc",
   "region": "east",
   "tags": ["tag1", "tag2"]
}

{
   "resource": "def",
   "region": "east",
   "tags": ["tag1", "tag2"]
}

{
   "resource": "xyz",
   "region": "east",
   "tags": ["tag3"]
}

now I want to write a query that return all items that has a string "tag1" in tags property (I want to get/project all the properties in that item)

CodePudding user response：

You can make use of ARRAY_CONTAINS system function for that. So your query would be something like:

SELECT * FROM Root r where ARRAY_CONTAINS(r.tags, "tag1")

and this would give a result like:

[
    {
        "id": "cd385b65c6b14b07eb157b72485b1260",
        "resource": "abc",
        "region": "east",
        "tags": [
            "tag1",
            "tag2"
        ],
    },
    {
        "id": "bb77e9ad1950467390cbc5ffac33cac3",
        "resource": "def",
        "region": "east",
        "tags": [
            "tag1",
            "tag2"
        ],
    }
]