PostgreSQL 10如何从JSON数组提取值为文本数组？

当然可以！针对你的需求——不管external数组里的对象有多少个未知名称的键，都能把所有内部值提取为文本数组，用PostgreSQL的JSON函数组合就能实现。

完整查询语句 #

SELECT 
    t.id,
    array_agg(v.value) AS internal_values
FROM 
    test_table t
CROSS JOIN LATERAL 
    jsonb_array_elements(t.data -> 'external') AS arr(obj)
CROSS JOIN LATERAL 
    jsonb_each_text(arr.obj) AS v(key, value)
GROUP BY 
    t.id
ORDER BY 
    t.id;

语句详解 #

• jsonb_array_elements(t.data -> 'external') AS arr(obj)：把每行data字段里的external JSON数组拆成单独的行，每一行对应数组里的一个JSON对象（别名obj）。这一步处理了数组结构，把嵌套的对象“拉平”。
• jsonb_each_text(arr.obj) AS v(key, value)：把每个JSON对象拆成键值对的行，value就是我们需要的文本格式的值。不管对象里有多少个键、键名是什么，都会全部提取出来。
• array_agg(v.value) + GROUP BY t.id：通过按原表的id分组，把同一原行的所有提取出的值聚合为一个文本数组，正好得到你想要的[value_1, some_value]格式的结果。

示例结果 #

针对你提供的测试数据，执行查询后会得到：

（注：PostgreSQL中数组默认用大括号显示，但实际返回的是标准文本数组，完全符合你的需求）

适配场景 #

这个方案完全支持你提到的“内部键数量、名称不确定”的情况——哪怕external数组里的对象有1个或N个键，或者不同行的键名不一样，都能正确提取所有值并聚合为数组。

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