CosmosDb 查询嵌套数据按根字段筛选

Question

我有一个json结构，如下所示：

{
"id": "123",
"date": "2020-06-02",
"product": {
"code": "A1",
"name": "Product 1",
"parents": [
{
"code": "P1",
"name": "Parent 1",
"children": [
{
"code": "C1",
"name": "Child 1",
}
]
}
]
}

}

我需要返回一个平面结构，例如。

date,       product.code, parents[].code, parents[].children[].code
----------  ------------  --------------  -------------------------
2020-06-02  A1            P1              C1

但是过滤 json 文档根目录中的日期字段。 任何帮助表示赞赏

Answer 1

请尝试以下 sql：

SELECT c.date,c.product.code,d.code AS parentCode,e.code AS childrenCode FROM c join d in c.product.parents join e in d.children

结果如下：

[
{
"date": "2020-06-02",
"code": "A1",
"parentCode": "P1",
"childrenCode": "C1"
}
]

希望这能帮助你。