如何使用Cosmos DB SQL在查询时重塑数据结构？

原始数据结构 #

{
   "id": "id-1",
   "Costs": {
            "Items": {
                "d1": {
                    "Title": "A1",
                    "Amount": 100
                },
                "d2": {
                    "Title": "A2",
                    "Amount": 200
                },
                "d3": {
                    "Title": "A3",
                    "Amount": 300
                }
   }
}

期望输出结构 #

{
  "id": "id-1",
  "Costs": {
                "d1": 100,
                "d2": 200,
                "d3": 300
  }
}

解决方案 #

根据需求，提供两种实现方式，分别适配键固定和键动态的场景：

方式一：动态遍历（适配键不固定的场景） #

利用Cosmos DB的ARRAY()、ARRAY_TO_OBJECT()函数实现动态转换，查询语句如下：

SELECT 
    c.id,
    ARRAY_TO_OBJECT(
        ARRAY(
            SELECT 
                {"key": k, "value": v.Amount}
            FROM 
                c.Costs.Items k
            JOIN 
                v IN [k]
        )
    ) AS Costs
FROM c

语句说明

• FROM c.Costs.Items k：遍历Costs.Items下的所有子对象，k代表每个子对象的键名对应的数据
• JOIN v IN [k]：将子对象拆解为可访问的键值对
• SELECT {"key": k, "value": v.Amount}：将每个键值对转换为key-value格式的数组元素
• ARRAY_TO_OBJECT()：把上述数组直接转换为目标对象结构

方式二：手动构造（适配键固定的场景） #

如果Items下的键是固定的，直接手动构造目标对象，性能更优：

SELECT 
    c.id,
    {
        "d1": c.Costs.Items.d1.Amount,
        "d2": c.Costs.Items.d2.Amount,
        "d3": c.Costs.Items.d3.Amount
    } AS Costs
FROM c

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