我是SQL API的新手。我正在尝试为这样的文档编写查询:
{
"School" : "ABC Elementary"
"Grades" : [
{
"gradeLevel" : "First","subjects" : ["Drawing","Music","Numbers","Words" ]
},{
"gradeLevel" : "Second","subjects" : ["Art","Phonetics" ]
}
]
}
另一个文档可能是
{
"School" : "DEF Elementary"
"Grades" : [
{
"gradeLevel" : "First","subjects" : ["Arts & Crafts","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来完成,同时还要考虑分区边界?