PostgreSQL 10如何提取JSON数组中letter值并拼接为字符串？

嘿，我来帮你搞定这个需求！你说得没错，最终确实要用ARRAY_TO_STRING函数，但关键是先把JSON里的"letter"值提取出来聚合成数组，下面给你详细的步骤和示例：

核心步骤拆解 #

• 展开JSON数组：用json_array_elements（针对json类型字段）或jsonb_array_elements（针对jsonb类型字段）把JSON数组拆成单行的JSON对象。
• 提取"letter"值：用->>运算符直接提取对象中的"letter"字段为文本类型（避免后续聚合时出现类型问题）。
• 聚合成数组：用array_agg()把提取到的所有"letter"值聚合为一个数组。
• 拼接成字符串：最后用array_to_string()指定分隔符（比如逗号、空格等）把数组转成字符串。

示例SQL代码 #

假设你的表名为my_table，存储JSON数据的字段为json_data，字段里的JSON结构类似[{"letter": "A"}, {"letter": "B"}, {"letter": "C"}]：

基础版（单条数据或全表聚合）

SELECT array_to_string(array_agg(elem->>'letter'), ', ') AS concatenated_letters
FROM my_table,
     json_array_elements(json_data) AS elem;

分组版（按表中主键/标识字段分组处理）

如果你的表有多行数据，每行对应一个独立的JSON数组，需要按行分组提取拼接的话：

SELECT id, 
       array_to_string(array_agg(elem->>'letter'), ', ') AS concatenated_letters
FROM my_table,
     json_array_elements(json_data) AS elem
GROUP BY id;

注意事项 #

• 如果你的JSON字段是jsonb类型，只需要把json_array_elements替换成jsonb_array_elements即可。
• ->>运算符返回的是文本类型，相比->返回的JSON类型，更适合直接用于聚合和拼接。

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