如何编写Azure CosmosDB NoSQL SQL查询转换文档数据结构

原文档结构 #

{"Name": "A1","Tag": "Extra","Daily_data": {"2010-01-01": {"Value1": {"P1": 200,"P2": 100,"P3": 250,"P4": 110},"Value2": {"P1": 200,"P2": 100,"P3": 250,"P4": 110}},"2010-01-02": {"Value1": {"P1": 200,"P2": 100,"P3": 250,"P4": 110},"Value2": {"P1": 200,"P2": 100,"P3": 250,"P4": 110}}}}

目标转换格式 #

{"Name": "A1","Tag": "Extra","Daily_data": [{"Date": "2010-01-01","Values": [{"Value": "Value1","P1": 200,"P2": 100,"P3": 250,"P4": 110},{"Value": "Value2","P1": 200,"P2": 100,"P3": 250,"P4": 110}]},{"Date": "2010-01-02","Values": [{"Value": "Value1","P1": 200,"P2": 100,"P3": 250,"P4": 110},{"Value": "Value2","P1": 200,"P2": 100,"P3": 250,"P4": 110}]}]}

问题分析 #

你之前的查询仅提取了日期键，未处理每个日期下的Value1/Value2层级结构，且在Cosmos DB中遍历对象键值对需要借助OBJECT_KEYS来获取属性名，再通过嵌套展开完成结构转换。

正确查询语句 #

SELECT 
    c.Name,
    c.Tag,
    ARRAY(
        SELECT {
            "Date": dateKey,
            "Values": ARRAY(
                SELECT {
                    "Value": valueKey,
                    "P1": valueObj.P1,
                    "P2": valueObj.P2,
                    "P3": valueObj.P3,
                    "P4": valueObj.P4
                }
                FROM valueKey IN OBJECT_KEYS(c.Daily_data[dateKey])
                JOIN valueObj = c.Daily_data[dateKey][valueKey]
            )
        }
        FROM dateKey IN OBJECT_KEYS(c.Daily_data)
    ) AS Daily_data
FROM c

代码解释 #

1. 外层日期数组处理：通过OBJECT_KEYS(c.Daily_data)获取所有日期属性名，遍历每个dateKey生成日期节点。
2. 内层Value数组处理：对每个日期对应的对象，再次用OBJECT_KEYS获取Value1/Value2的属性名，遍历valueKey生成Value项。
3. 属性合并组装：将valueKey作为Value字段，同时提取对应对象中的P1-P4属性，组合成目标结构。

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