如何从OBJECT_CONSTRUCT生成对象提取键并转为非JSON格式单列展示

原问题背景 #

你当前使用的SQL语句为：

SELECT ID, OBJECT_CONSTRUCT("name", NAME, "age", AGE, "city", CITY) AS ALL_ATTRIBUTES, OBJECT_KEYS(ALL_ATTRIBUTES) AS ALL_FIELDS FROM MY_TABLE

生成的表数据如下：

需要将ALL_FIELDS列从JSON数组格式转为逗号分隔的大写字符串，期望结果：

修改后的SQL语句 #

可以通过ARRAY_TO_STRING将数组转为字符串，再用UPPER统一转为大写，优化后的SQL如下：

SELECT 
    ID,
    OBJECT_CONSTRUCT("name", NAME, "age", AGE, "city", CITY) AS ALL_ATTRIBUTES,
    UPPER(ARRAY_TO_STRING(OBJECT_KEYS(OBJECT_CONSTRUCT("name", NAME, "age", AGE, "city", CITY)), ', ')) AS ALL_FIELDS
FROM MY_TABLE

如果想避免重复调用OBJECT_CONSTRUCT，也可以用子查询简化：

SELECT 
    ID,
    ALL_ATTRIBUTES,
    UPPER(ARRAY_TO_STRING(OBJECT_KEYS(ALL_ATTRIBUTES), ', ')) AS ALL_FIELDS
FROM (
    SELECT 
        ID,
        OBJECT_CONSTRUCT("name", NAME, "age", AGE, "city", CITY) AS ALL_ATTRIBUTES
    FROM MY_TABLE
) AS subquery

逻辑说明 #

• OBJECT_KEYS函数会提取JSON对象的键，返回一个字符串数组（如['name']或['age', 'name']）
• ARRAY_TO_STRING将数组元素用, 作为分隔符拼接成单个字符串
• UPPER函数将拼接后的字符串转为大写，匹配你期望的格式

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