Postgres调用json_array_elements报非数组错误 如何兼容JSON格式查业务地址

兼容两种JSON格式的查询语句 #

select 
  h ->> 'dw:address_line_1' as address,
  h ->> 'dw:city' as city,
  h -> 'dw:region' -> 'dw:ID' -> 2 -> '#text' as state,
  h -> 'dw:Country_ISO_Code' as country
from testschema.dw_data_job t
join testschema.dw_data_demo t1 on t.id = t1.id,
json_array_elements(
  case 
    when json_typeof(t.addresses::json) = 'array' then t.addresses::json
    else json_build_array(t.addresses::json)
  end
) h
where h -> 'dw:address_type' -> 'dw:ID' -> 1 ->> '#text' = 'BUSINESS'

逻辑说明 #

• 通过json_typeof()判断addresses字段转JSON后的数据类型：如果是数组直接使用，否则用json_build_array()将单个地址对象包裹成长度为1的数组，保证传入json_array_elements的参数永远是数组类型，彻底解决非数组调用报错的问题
• 保留了原有的表关联逻辑、地址类型过滤规则，返回字段完全匹配需求
• 若存在非法JSON格式的脏数据，可以在WHERE条件中补充and t.addresses ~ '^\[.*\]$|^\{.*\}$'过滤无效记录，避免类型转换报错

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