PostgreSQL查询存储对象数组的JSON列返回null值如何解决？

问题原因 #

你表中data列存储的是JSON对象数组，而非单个JSON对象。直接对数组使用->取对象键dateTime、value时，数组本身不存在这些键，因此返回的所有值都是null。

解决方案 #

需要先将JSON数组拆分为多行单个JSON对象，再提取对应字段：

适配jsonb类型（PostgreSQL推荐使用的JSON存储类型） #

SELECT 
  arr_item ->> 'dateTime' AS datetime,
  arr_item -> 'value' ->> 'bpm' AS bpm,
  arr_item -> 'value' ->> 'confidence' AS confidence 
FROM heart_rate_json,
     jsonb_array_elements(data) AS arr_item;

适配json类型 #

如果你的data字段定义为json而非jsonb，将函数替换为json_array_elements即可：

SELECT 
  arr_item ->> 'dateTime' AS datetime,
  arr_item -> 'value' ->> 'bpm' AS bpm,
  arr_item -> 'value' ->> 'confidence' AS confidence 
FROM heart_rate_json,
     json_array_elements(data) AS arr_item;

可选扩展 #

• 如果需要保留data为空数组、null的原行，改用左连接lateral写法：

SELECT 
  arr_item ->> 'dateTime' AS datetime,
  arr_item -> 'value' ->> 'bpm' AS bpm,
  arr_item -> 'value' ->> 'confidence' AS confidence 
FROM heart_rate_json
LEFT JOIN LATERAL jsonb_array_elements(data) AS arr_item ON true;

• 如果需要记录每个元素在原数组中的下标，添加WITH ORDINALITY参数即可：

SELECT 
  arr_item_index,
  arr_item ->> 'dateTime' AS datetime,
  arr_item -> 'value' ->> 'bpm' AS bpm,
  arr_item -> 'value' ->> 'confidence' AS confidence 
FROM heart_rate_json,
     jsonb_array_elements(data) WITH ORDINALITY AS arr(arr_item, arr_item_index);

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