Cosmos DB json array that matchs all the words

user_9532503 0
Viewed 118

I need a query that can get me the document from a list of words for example if I use


    select c from c join (SELECT distinct VALUE c.id FROM c JOIN word IN c.words WHERE  word in('word1',word2) and tag in('motorcycle')) ORDER BY c._ts desc

it will bring both documents, I want to retrieve only the first one cause it matches the two words and not only one.

Document 1

 "c": {
            "id": "d0f1723c-0a55-454a-9cf8-3884f2d8d61a",
            "words": [
                "word1",
                "word2",
                "word3",
             ]}

Document 2

 "c": {
            "id": "d0f1723c-0a55-454a-9cf8-3884f2d8d61a",
            "words": [
                "word1",
                "word4",
                "word5",
             ]}
azure-cosmosdb-sqlapi azure-cosmosdb azure
1 Answers

You should be able to cover this with two ARRAY_CONTAINS expressions in your WHERE clause (and no need for a JOIN):

SELECT c.id FROM c
WHERE ARRAY_CONTAINS(c.words, 'word1')
AND ARRAY_CONTAINS(c.words, 'word2')

This should return the id of your first document.

user_2721090
Related