Oracle转PostgreSQL JSON查询遇标量提取错误，求修正

错误原因：cannot extract elements from a scalar 是因为你的restrict字段中存在非JSON数组的标量值（比如单个字符串、数字，或非JSON格式内容），PostgreSQL无法从这些标量中提取数组元素。可以通过先过滤合法的JSON数组记录，再进行解析来解决。

方案1：快速匹配符合条件的记录 #

SELECT DISTINCT u_id
FROM your_table
WHERE 
  -- 先确保restrict是合法的JSON数组
  jsonb_typeof(restrict::jsonb) = 'array'
  -- 检查数组中是否存在符合条件的元素
  AND restrict::jsonb @> '[{"informationType": "MEASURE", "accessScope": "NONE"}]'

这个写法利用jsonb的@>包含操作符快速匹配，性能更优，适合只需要判断存在性的场景。

方案2：解析数组并精确筛选元素 #

如果需要明确提取数组中符合条件的元素对应的u_id，可以用横向连接解析数组，同时前置过滤非数组记录：

SELECT DISTINCT t.u_id
FROM your_table t
CROSS JOIN LATERAL jsonb_array_elements(t.restrict::jsonb) j
WHERE 
  jsonb_typeof(t.restrict::jsonb) = 'array'
  AND j->>'informationType' = 'MEASURE'
  AND j->>'accessScope' = 'NONE'

兼容非JSON格式的场景 #

如果restrict字段中存在完全不合法的JSON内容，可额外增加合法性校验避免转换报错：

SELECT DISTINCT t.u_id
FROM your_table t
CROSS JOIN LATERAL jsonb_array_elements(t.restrict::jsonb) j
WHERE 
  jsonb_valid(t.restrict) -- 确保字段是合法JSON格式
  AND jsonb_typeof(t.restrict::jsonb) = 'array'
  AND j->>'informationType' = 'MEASURE'
  AND j->>'accessScope' = 'NONE'

关键提示 #

• 优先用jsonb类型：PostgreSQL中jsonb比json支持更多操作符，查询性能更优，推荐转换为jsonb处理。
• 过滤前置：先排除非数组、非合法JSON的记录，从根源避免解析时的标量错误。

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