Snowflake中如何将键值对JSON数组转换为单个JSON对象？

我有如下JSON数组：

[
    {"key": "cId", "value": "d03ce656"},
    {"key": "cName", "value": "Healthcare"},
    {"key": "pId", "value": "d869628b"},
    {"key": "pName", "value": "ConveYour"}
]

希望转换为单个JSON对象：

{
  "cId": "d03ce656",
  "cName": "Healthcare",
  "pId" : "d869628b",
  "pName": "ConveYour"
}

我尝试了以下SQL语句：

SELECT
    OBJECT_CONSTRUCT(
        parse_json(f.value):key::string, 
        parse_json(f.value):value::string
    ) AS json_object
FROM LATERAL FLATTEN(INPUT => PARSE_JSON('[
    {"key": "cId", "value": "d03ce656"},
    {"key": "cName", "value": "Healthcare"},
    {"key": "pId", "value": "d869628b"},
    {"key": "pName", "value": "ConveYour"}
]')) AS f;

但执行后返回4行独立的JSON对象：

JSON_OBJECT
{    "cId": "d03ce656"  }
{    "cName": "Healthcare"  }
{    "pId": "d869628b"  }
{    "pName": "ConveYour"  }

需要的是单个合并后的JSON对象，请问该如何解决？

使用OBJECT_AGG函数聚合拆分后的键值对即可，它能将多行的键值组合并为单个JSON对象。

修改后的SQL如下：

SELECT
    OBJECT_AGG(
        parse_json(f.value):key::string, 
        parse_json(f.value):value::string
    ) AS merged_json_object
FROM LATERAL FLATTEN(INPUT => PARSE_JSON('[
    {"key": "cId", "value": "d03ce656"},
    {"key": "cName", "value": "Healthcare"},
    {"key": "pId", "value": "d869628b"},
    {"key": "pName", "value": "ConveYour"}
]')) AS f;

关键说明： #

• FLATTEN仍负责将JSON数组拆分为单个键值对行；
• OBJECT_AGG会把所有行的键（key字段值）和对应值（value字段值）聚合，生成完整的合并JSON对象；
• 执行后将返回一行结果，即目标格式的单个JSON对象。

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