如何在Cosmos DB中查询含指定链与合约地址的嵌套数组文档？

问题背景 #

现有Cosmos DB文档结构如下：

{
  "id": "pst_bf6i9GM0JTN8W1m66N8F",
  "postTitle": "Post 1",
  "memberships": [
      [
          {
              "chain": 1,
              "contractAddress": "0xcB3165Df24BAe1aB590616DE7a950Dd804f39A3C",
              "id": "mbs_IUTG9kJG84Gv2jr17Y8y"
          },
          {
            "chain": 1,
            "contractAddress": "0x00758fc71Cee3AC591E69fF9010c88782E678De2",
            "id": "mbs_jUTG9kJG83Gv2jr17Y8y"
          }
      ],
      [
          {
              "chain": 80001,
              "contractAddress": "0x358738C2Fa36bbb3e983Dc0CC17798E0ef0653f2",
              "id": "mbs_cUTG9kJGx3Gv2jr17Y8l"
          }
      ]
  ]
}

其中memberships是数组的数组，代表帖子的访问权限要求。需要编写查询语句，提取所有包含指定contractAddress与chain配对的帖子ID。

之前尝试的伪代码无法生效，原因是ARRAY_CONTAINS直接作用于外层数组时，匹配的是子数组而非子数组内的对象。

解决方案 #

提供两种可行的查询方式：

方式一：使用ANY关键字结合ARRAY_CONTAINS #

利用ANY遍历外层数组的子数组，再用ARRAY_CONTAINS检查子数组内是否存在匹配对象（第三个参数true开启部分匹配，忽略id字段）：

SELECT c.id 
FROM c 
WHERE ARRAY_LENGTH(c.memberships) > 0
AND (
    ANY subArr IN c.memberships WHERE ARRAY_CONTAINS(subArr, {contractAddress: "0x00758fc71Cee3AC591E69fF9010c88782E678De2", chain: 1}, true)
    OR
    ANY subArr IN c.memberships WHERE ARRAY_CONTAINS(subArr, {contractAddress: "0x358738C2Fa36bbb3e983Dc0CC17798E0ef0653f2", chain: 80001}, true)
)

方式二：通过JOIN展开嵌套数组 #

两次JOIN将嵌套数组展开为单个权限对象，再直接匹配条件，用DISTINCT避免重复返回同一帖子ID：

SELECT DISTINCT c.id
FROM c
JOIN subArr IN c.memberships
JOIN membership IN subArr
WHERE (membership.chain = 1 AND membership.contractAddress = "0x00758fc71Cee3AC591E69fF9010c88782E678De2")
OR (membership.chain = 80001 AND membership.contractAddress = "0x358738C2Fa36bbb3e983Dc0CC17798E0ef0653f2")

查询结果 #

两种方式都会返回符合要求的帖子ID：

[
  {
    "id": "pst_bf6i9GM0JTN8W1m66N8F"
  }
]

内容的提问来源于stack exchange，提问作者gsscoder