CODEWITHSUNDEEP
azureazure-cosmosdbazure-cosmosdb-sqlapi
Dani
Dani

Reputation: 1047

Matching multiple values using Azure Cosmos DB

I have following json in my Cosmos DB:

[
    {
        "FirstName": "FirstName",
        "LastName": "LastName",
        "TechnologyRatings": [
            {
                "Technology": {
                    "Name": "C#",
                    "id": "d76d59a7-c9a3-404d-91dd-cf2596ee7501"
                },
                "Rating": 1
            },
            {
                "Technology": {
                    "Name": "SQL",
                    "id": "5686189b-ccfc-41c6-bcdb-b56f80130b45",
                },
                "Rating": 2
            }
        ],
        "id": "7c34718f-ef01-4b40-9a03-f0880f424fd4",
        "ModifiedAt": "2021-05-28T09:55:37.6260562Z",
        "_rid": "GyRkALN-kZcCAAAAAAAAAA==",
        "_self": "dbs/GyRkAA==/colls/GyRkALN-kZc=/docs/GyRkALN-kZcCAAAAAAAAAA==/",
        "_etag": "\"00000000-0000-0000-53a7-9c3d693501d7\"",
        "_attachments": "attachments/",
        "_ts": 1622195737
    }
]

Now I try to apply a filter on Technology.id and Rating. Meaning I want to select all entries for example with C# with Rating = 1 and SQL with Rating = 2.

Something like

(Technology.id = "d76d59a7-c9a3-404d-91dd-cf2596ee7501" and Rating = 1) OR (Technology.id = "5686189b-ccfc-41c6-bcdb-b56f80130b45" and Rating = 2)

As TechnologyRatings is an array that doesn't work.

I also played around with ARRAY_CONTAINS but I didn't get it to work.

SELECT VALUE c FROM c JOIN t IN c.TechnologyRatings WHERE ARRAY_CONTAINS([{"id": "d76d59a7-c9a3-404d-91dd-cf2596ee7501", "Rating": 1}, {"id": "5686189b-ccfc-41c6-bcdb-b56f80130b45", "Rating": 2}], {"id": t.Technology.id, "Rating": t.Rating}, true)

How can I write such a query?

Upvotes: 0

Views: 2205

Answers (2)

Steve Johnson
Steve Johnson

Reputation: 8660

You can try this SQL:

SELECT 
    Distinct VALUE c 
FROM c 
JOIN t IN c.TechnologyRatings
WHERE (t.Technology.id = "d76d59a7-c9a3-404d-91dd-cf2596ee7501" and t.Rating = 1) OR (t.Technology.id = "5686189b-ccfc-41c6-bcdb-b56f80130b45" and t.Rating = 2)

or

SELECT 
    VALUE c 
FROM c 
WHERE 
    (ARRAY_CONTAINS(c.TechnologyRatings,{"Technology": {"id":"d76d59a7-c9a3-404d-91dd-cf2596ee7501"}},true) and ARRAY_CONTAINS(c.TechnologyRatings,{"Rating":1},true))
OR
    (ARRAY_CONTAINS(c.TechnologyRatings,{"Technology": {"id":"5686189b-ccfc-41c6-bcdb-b56f80130b45"}},true) and ARRAY_CONTAINS(c.TechnologyRatings,{"Rating":2},true))

Upvotes: 1

DevRacker
DevRacker

Reputation: 264

Here's the query:

SELECT VALUE root FROM root JOIN (SELECT VALUE EXISTS(SELECT VALUE tRatings FROM root JOIN tRatings IN root["TechnologyRatings"] 
WHERE ((tRatings["Technology"]["id"] = "5686189b-ccfc-41c6-bcdb-b56f80130b45") OR (tRatings["Technology"]["id"] = "d76d59a7-c9a3-404d-91dd-cf2596ee7501")))) AS found WHERE found

Note that you must make sure to include a partition key on that query to avoid extra delays and costs on the query.

If the partition key was the 'id' field, the query would look like this:

SELECT VALUE root FROM root JOIN (SELECT VALUE EXISTS(SELECT VALUE tRatings FROM root JOIN tRatings IN root["TechnologyRatings"]
WHERE ((tRatings["Technology"]["id"] = "5686189b-ccfc-41c6-bcdb-b56f80130b45") OR (tRatings["Technology"]["id"] = "d76d59a7-c9a3-404d-91dd-cf2596ee7501")))) AS found 
WHERE ((root["id"] = "5686189b-ccfc-41c6-bcdb-b56f80130b45") AND found)

The query with the partition key has the following stats enter image description here

Upvotes: 0

Related Questions