如何在PostgreSQL中从JSON对象数组提取值数组？

有几种简便的方法可以实现这个需求，根据你的PostgreSQL版本和字段类型（json/jsonb）选择即可：

方法1：拆分聚合（兼容所有支持JSON的PostgreSQL版本） #

如果你的JSON字段是jsonb类型：

SELECT array_agg((item->>'amount')::numeric) AS amount_array
FROM your_table, jsonb_array_elements(your_json_column) AS item;

如果是json类型，替换成json_array_elements：

SELECT array_agg((item->>'amount')::numeric) AS amount_array
FROM your_table, json_array_elements(your_json_column) AS item;

说明： #

• jsonb_array_elements（或json_array_elements）会将JSON数组拆分为单行的JSON对象
• item->>'amount'提取每个对象中amount字段的文本值，::numeric将其转换为数值类型（可根据实际需求改为int等）
• array_agg把拆分后的单个值重新聚合为SQL数组

如果是直接处理单个JSON数组值（而非表字段）：

SELECT array_agg((elem->>'amount')::numeric)
FROM jsonb_array_elements('[{"id":123,"quantity":4,"amount":180},{"id":563,"quantity":1,"amount":675},{"id":563,"quantity":1,"amount":875}]'::jsonb) AS elem;

方法2：使用JSONPath（PostgreSQL 12+） #

PostgreSQL 12及以上版本支持JSONPath查询，写法更简洁：

SELECT jsonb_path_query_array(your_json_column, '$.amount') AS amount_array
FROM your_table;

这个语句会直接返回包含所有amount值的jsonb数组。如果需要转换为SQL原生数组（如numeric[]），PostgreSQL 14+支持直接强制转换：

SELECT jsonb_path_query_array(your_json_column, '$.amount')::numeric[] AS amount_array
FROM your_table;

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