如何用SQL优雅地将对象数组转换为字符串数组？

输入的JSON数据：

[{"id":"Bloomberg","selected":true,"displayName":"Bloomberg"}, {"id":"some_other_company","selected":true,"displayName":"some_other_company"}]

期望转换后的输出：

["Bloomberg", "some_other_company"]

当前使用的查询语句：

SELECT
    REPLACE(JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(after), 'works', 'string'), '\"', '"')
    FROM <table>
    ...

询问是否存在更优雅的实现方式？

针对支持JSON函数的SQL引擎（如Snowflake），可以直接通过JSON解析+聚合的方式实现，避免字符串替换这类易出错的操作：

SELECT JSON_ARRAYAGG(JSON_VALUE(item, '$.id')) AS result
FROM <table>,
LATERAL FLATTEN(INPUT => PARSE_JSON(after:works:string)) AS item
-- 可选：仅保留selected为true的条目
WHERE JSON_VALUE(item, '$.selected') = 'true'

核心逻辑说明： #

• PARSE_JSON(after:works:string)：将字符串类型的JSON数组解析为可操作的JSON对象数组
• LATERAL FLATTEN：把数组拆分为单个JSON对象的行数据
• JSON_VALUE(item, '$.id')：精准提取每个对象中的id字段值
• JSON_ARRAYAGG：将所有提取出的id重新聚合为目标格式的JSON数组

如果不需要过滤selected为true的条目，直接移除WHERE子句即可。这种方式比字符串替换更健壮，能适配JSON格式的微小变动，避免解析异常。

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