PostgreSQL中查询以整数为键的JSON数据方法咨询

问题分析 #

1. json_each报错cannot deconstruct a scalar：json_each仅能解析JSON对象类型的数据，若sc.data字段中存在标量（如纯字符串、数字）或数组类型的值，就会触发该错误。
2. sc.data::json->'1'返回null：大概率是两种情况导致：
   • 数据中不存在键为"1"的字段，或者键的格式是数字而非字符串（此时需用->1替代->'1'）；
   • sc.data本身不是JSON对象类型，无法通过键名取值。

正确查询方式 #

假设你的数据确实符合示例结构，要获取键"1"对应数组里的指定字段（比如"2"字段），可以使用以下SQL：

-- 展开目标数组并提取指定字段
SELECT elem->'2' AS field_2
FROM your_table sc,
     json_array_elements(sc.data->'1') elem
-- 过滤无效数据，避免报错
WHERE json_typeof(sc.data) = 'object'
  AND sc.data->'1' IS NOT NULL
  AND json_typeof(sc.data->'1') = 'array';

注意：如果sc.data字段本身已经是json类型，无需额外加::json转换，直接用sc.data->'1'即可。若键"1"实际是数字类型而非字符串，将查询中的->'1'改为->1即可。

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