azure - azure-cosmosDB 中的 ARRAY_CONTAINS 与 JOIN

Question

我们计划摄取到 DocumentDb 中的 JSON 文档如下所示......

[
{"id":"id1","LastName": “user1”, "GroupMembership":["g1","g2"]},
{"id":"id2","LastName": “user2”, "GroupMembership":["g1","g4","g5"]},
{"id":"id3","LastName": “user3”, "GroupMembership":["g3","g4","g2"]},
…
]

我们想要回答诸如统计“g1”或“g2”组成员的所有用户等查询……。用户数量非常大(几百万)……实现此查询并使用索引并避免任何扫描的最佳方法是什么......我应该使用 ARRAY_CONTAINS 还是 JOIN(ARRAY_CONTAINS 内部使用索引还是进行扫描)...

选项1)

SELECT VALUE COUNT(1) FROM Users WHERE ARRAY_CONTAINS(Users.GroupMembership, "g1") or ARRAY_CONTAINS(Users.GroupMembership, "g2")

选项2)

SELECT VALUE COUNT(1) FROM Users JOIN Membership in Users.GroupMembership WHERE Membership = "g1" or Membership = "g2"

Answer 1

两个查询都应以相同的方式使用索引，但与 JOIN 相比，ARRAY_CONTAINS 可能会提供更好的执行时间。您可以根据本文使用查询指标来分析这两个查询:https://learn.microsoft.com/en-us/azure/cosmos-db/documentdb-sql-query-metrics#query-execution-metrics