在Cosmos DB中有没有一种方法可以使用SQL API来过滤字符串数组中的值？

Question

我是SQL API的新手。我正在尝试为文档编写查询，如下所示：

{
   "School" : "ABC Elementary"
   "Grades" : [
      {
          "gradeLevel" : "First",
          "subjects" : ["Drawing", "Music", "Numbers", "Words" ]
      },
      {
          "gradeLevel" : "Second",
          "subjects" : ["Art", "Music", "Numbers", "Phonetics" ]
      }
   ]
}

另一个文档可以是

{
   "School" : "DEF Elementary"
   "Grades" : [
      {
          "gradeLevel" : "First",
          "subjects" : ["Drawing", "Music", "Numbers", "Words" ]
      },
      {
          "gradeLevel" : "Second",
          "subjects" : ["Arts & Crafts", "Music", "Numbers", "Phonetics", "PE" ]
      }
   ]
}

我正在尝试编写一个查询，它将返回以"PE“为主题的文档(在上面的例子中只有一个是"DEF Elementary”学校文档)或"Music“(在这个例子中都是学校文档)。

我试过了

SELECT * FROM c 
JOIN o IN o.Grades 
WHERE "PE" IN o.subjects

但这不会返回任何结果。我不确定这个查询是否可能，除非每个主体也是一个宾语：

"subjects" : [
   {"name" : "Art"}, 
   {"name" : "Music"}
]

如果SQL API不能返回结果，是否可以使用存储过程和/或UDF，同时还分解分区边界？

Answer 1

请使用以下sql：

SELECT c.School,c.Grades FROM c 
JOIN o IN c.Grades 
WHERE ARRAY_CONTAINS(o.subjects,"PE",true)

输出：

[
    {
        "School": "DEF Elementary",
        "Grades": [
            {
                "gradeLevel": "First",
                "subjects": [
                    "Drawing",
                    "Music",
                    "Numbers",
                    "Words"
                ]
            },
            {
                "gradeLevel": "Second",
                "subjects": [
                    "Arts & Crafts",
                    "Music",
                    "Numbers",
                    "Phonetics",
                    "PE"
                ]
            }
        ]
    }
]