PostgreSQL调用JSON_ARRAY_ELEMENTS()无法获取全部值的问题求解

• 生成tempJSData表时使用的json_object_agg(field_name, field_value)函数特性导致数据丢失：JSON对象不允许重复key，聚合时遇到相同的field_name会直接覆盖旧值，仅保留最后出现的value。你场景中p_id=102的多个Name字段最后被覆盖为null，所以只能查到空值。

方案1：直接从原表查询（性能最优，无需生成临时表） #

select 
  p_id,
  field_value as Namess
from tbl_jsdata
where field_name = 'Name';

该语句直接过滤出所有Name字段的行，返回结果和你预期完全一致。

方案2：调整临时表生成逻辑，聚合存储所有Name值 #

如果必须生成临时表使用，建议直接聚合同p_id下的所有Name值为JSON数组：

-- 生成临时表
drop table if exists tempJSData;
select 
  p_id,
  json_agg(field_value) filter (where field_name = 'Name') as name_list
into tempJSData
from tbl_jsdata
group by p_id;

-- 查询结果
select 
  p_id,
  json_array_elements_text(name_list) as Namess
from tempJSData;

方案3：保留全字段JSON存储的调整方法 #

如果需要在临时表中存储同p_id的所有字段信息，不要聚合为单个JSON对象，改为聚合为JSON对象数组避免key覆盖：

-- 生成临时表
drop table if exists tempJSData;
select 
  p_id,
  json_agg(json_build_object(field_name, field_value)) as all_fields
into tempJSData
from tbl_jsdata
group by p_id;

-- 查询结果
select 
  t.p_id,
  (j.field->>'Name') as Namess
from tempJSData t
left join json_array_elements(t.all_fields) j(field) on j.field ? 'Name';

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