CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
okolobaxa
okolobaxa

Reputation: 342

Search in array by array of values in Azure Cosmos DB

Document contains field Tags with array of strings, e.g ["tag1", "tag2", "tag3"]. How can I get all documents both with tag1 and tag2?

I've tried ARRAY_CONTAINS, but with this query, CosmosDB returns all documents with tag1 and all documents with tag2, not only where tag1 and tag2 are together.

SELECT VALUE files FROM files JOIN tag IN files.Tags WHERE IS_DEFINED(files.Tags) AND ARRAY_CONTAINS(["tag1", "tag2"], tag, false)

My sample data

[
    {
        "id": "23244d53-f4ba-4bf5-90a4-a4073a759232",
        "Tags": [
            "tag1",
            "tag2"
        ]
    },
    {
        "id": "23244d53-f4ba-4bf5-90a4-a4073a759232",
        "Tags": [
            "tag1",
            "tag3"
        ]
    },
    {
        "id": "0bbd57d1-38cb-4094-81cf-33703448bbcd",
        "Tags": [
            "tag1"
        ]
    }
 ]

Upvotes: 1

Views: 1194

Answers (1)

Steve Johnson
Steve Johnson

Reputation: 8660

You can try something like this SQL:

SELECT * 
FROM files 
WHERE IS_DEFINED(files.Tags) AND ARRAY_CONTAINS(files.Tags, "tag1",false) AND ARRAY_CONTAINS(files.Tags, "tag2",false)

Result:

[
    {
        "id": "23244d53-f4ba-4bf5-90a4-a4073a759232",
        "Tags": [
            "tag1",
            "tag2"
        ]
    }
]

Upvotes: 2

Related Questions