如何编写正确的Cosmos DB SQL嵌套数组查询获取指定结构结果？

正确查询语句 #

SELECT 
    {'first': root.studentname.first} AS studentname,
    {'address': ARRAY(
        SELECT {'street': ARRAY(SELECT f.unit FROM f IN a.street)}
        FROM a IN root.information.address
    )} AS information
FROM root

结果说明 #

执行上述查询后，会得到你期望的结构：

{"studentname":{"first": "John"},"information":{"address":[{"street":[{"unit": "123"},{"unit": "098"}]},{"street":[{"unit": "456"},{"unit": "765"}]}]}}

原查询问题分析 #

你之前使用了JOIN c in root.information.address，这个操作会将information.address数组中的每个元素拆分为独立的返回文档，所以最终得到了两个重复studentname的结果，每个结果只包含一个address元素。

而正确的方式是在SELECT语句内部通过嵌套的ARRAY()函数直接处理数组：

1. 外层ARRAY(SELECT ... FROM a IN root.information.address)负责遍历所有address元素，构造完整的address数组
2. 内层ARRAY(SELECT f.unit FROM f IN a.street)负责过滤每个address下的street数组，仅保留unit字段

这样就能在单个文档中返回整合后的address数组，避免重复。

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