PostgreSQL中解析text列存储的JSON数据遇到问题求助

问题场景 #

我有一张PostgreSQL表，结构及数据如下：

尝试解析时遇到两个问题：

• 使用jsonb_array_elements或json_to_recordset函数时，报错function jsonb_array_elements doesn't exist，但用静态JSON字符串测试这些函数可正常运行：

select *
from json_to_recordset('[{"operation":"U","taxCode":1000},{"operation":"U","taxCode":10001}]')
as x("operation" text, "taxCode" int);

• 将Value列转为JSON类型后，用->>'username'查询返回null：

select v."Value"::json->>'username' --- username返回null

问题原因及解决方法 #

1. 函数报错的解决

jsonb_array_elements仅支持jsonb类型，而你的Value列是text类型，直接调用会报错。另外需确保PostgreSQL版本在9.4及以上（jsonb特性从9.4开始支持）。

正确做法是先将text列转为json/jsonb类型，再调用函数：

• 用json_array_elements展开数组：

select 
  p."PreferenceId",
  elem->>'username' as username,
  elem->>'customerId' as customerId
from 
  your_table p,
  json_array_elements(p."Value"::json) as elem;

• 用json_to_recordset映射字段：

select 
  p."PreferenceId",
  x.username,
  x.customerId
from 
  your_table p,
  json_to_recordset(p."Value"::json) as x(username text, customerId text);

2. 返回null的解决

Value存储的是JSON数组，不是单个JSON对象，直接用->>'username'无法从数组中提取元素字段。需要先定位数组中的元素，再取值：

-- 获取数组第一个元素的username
select v."Value"::json->0->>'username' from your_table v;

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