使用 Azure Cosmos DB 匹配多个值

Question

我的Cosmos DB中有以下json:

[
    {
        "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
    }
]

现在我尝试对 Technology.id 和评级应用过滤器。这意味着我想选择所有条目，例如评级为1的C#和评级为2的SQL。

大约

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

因为技术代表是一个不起作用的数组。

我也玩过ARRAY_CONTAINS，但我没有让它工作。

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)

如何编写这样的查询？

Answer 1

以下是查询：

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

请注意，您必须确保在该查询中包含分区键，以避免查询的额外延迟和成本。

如果分区键是“id”字段，则查询如下所示：

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)
    

Answer 2

你可以试试这个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)

或者

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))