PostgreSQL json列提取数组属性时如何转为数组类型而非文本？

假设你的表名为your_table，offer为json类型列，possible_amounts是JSON结构中的数组属性：

转为text类型PostgreSQL数组 #

方式1：子查询+聚合函数 #

SELECT 
  id,
  array_agg(elem) AS possible_amounts_array
FROM your_table,
     json_array_elements_text(offer->'possible_amounts') AS elem
GROUP BY id;

方式2：数组构造器（更简洁） #

SELECT 
  id,
  array(SELECT json_array_elements_text(offer->'possible_amounts')) AS possible_amounts_array
FROM your_table;

转为特定数据类型数组（如numeric） #

如果JSON数组内是数值，可在提取时做类型转换，得到对应类型的PostgreSQL数组：

SELECT 
  id,
  array(SELECT (json_array_elements_text(offer->'possible_amounts'))::numeric) AS possible_amounts_numeric_array
FROM your_table;

基于jsonb的高效方案（推荐） #

如果能将offer列改为jsonb类型（jsonb在数组操作上性能更优），可以用内置便捷函数：

转text数组 #

SELECT 
  id,
  jsonb_array_to_text_array(offer::jsonb->'possible_amounts') AS possible_amounts_array
FROM your_table;

转数值数组（PostgreSQL 12+支持） #

SELECT 
  id,
  jsonb_array_to_array(offer::jsonb->'possible_amounts', 'numeric') AS possible_amounts_numeric_array
FROM your_table;

处理null场景 #

如果possible_amounts可能为null，用COALESCE避免返回null，替换为空数组：

SELECT 
  id,
  COALESCE(array(SELECT json_array_elements_text(offer->'possible_amounts')), '{}'::text[]) AS possible_amounts_array
FROM your_table;

内容的提问来源于stack exchange，提问作者Eugenio.Gastelum96