如何编写PSQL查询将JSON对象的值转换为数组形式？

需求回顾 #

现有表结构及数据：

需要转换为：

解决方案 #

1. 查询转换结果

若仅需查看转换后的数据，执行以下SQL：

SELECT 
    id,
    json_object_agg(key, json_build_array(value)) AS tags
FROM 
    your_table,
    json_each_text(tags)
GROUP BY 
    id;

2. 永久更新表数据

若要修改表中tags字段的实际存储，使用更新语句：

UPDATE your_table
SET tags = (
    SELECT json_object_agg(key, json_build_array(value))
    FROM json_each_text(tags)
);

关键函数说明 #

• json_each_text(tags)：把JSON对象拆分为键值对行，每行包含key（键名）和value（字符串类型的值）。
• json_build_array(value)：将单个字符串值包装成JSON数组。
• json_object_agg(key, ...)：将处理后的键值对重新聚合为JSON对象，此时每个值已转为数组格式。

提示：如果tags字段类型是jsonb，只需将所有json_前缀替换为jsonb_（如jsonb_each_text、jsonb_object_agg）即可适配。

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