Postgres使用jsonb_array_elements报“无法从标量提取元素”错误排查

问题背景 #

我有一张Postgres表day，包含jsonb类型的plan_activities列。其中id=18的记录，该字段的JSON内容是合法数组：

[
  {
    "activity": "Gym",
    "cardio": false,
    "strength": true,
    "quantity": 20,
    "units": "mins",
    "timeOfDay": "Evening",
    "summary": "Gym - 20 mins - Evening",
    "timeOfDayOrder": 4
  },
  {
    "activity": "Walk",
    "cardio": true,
    "strength": false,
    "quantity": 15,
    "units": "minutes",
    "timeOfDay": "morning",
    "summary": "Walk - 15 minutes - Lunchtime",
    "timeOfDayOrder": 1
  }
]

执行以下查询时报错：

select jsonb_array_elements(day.plan_activities) as activities 
from day 
where day.id = 18; 

错误信息：

Failed to run sql query: cannot extract elements from a scalar

我需要提取数组元素，生成包含所有字段且关联day记录的独立数据，想知道问题出在哪？

问题原因 #

你看到的JSON虽然是数组格式，但数据库里这条记录的plan_activities字段实际存储的不是JSON数组类型的jsonb，而是标量（scalar）——最常见的情况是插入数据时，把JSON数组当成字符串存进去了（比如用单引号包裹后转成jsonb，结果存的是字符串标量，而非真正的数组）。

你可以先执行这条SQL验证实际存储的类型：

SELECT jsonb_typeof(plan_activities), plan_activities FROM day WHERE id = 18;

如果jsonb_typeof返回string，就说明确实是把JSON数组以字符串形式存成了标量，而非真正的JSON数组。

解决方案 #

1. 先修复数据（推荐） #

如果验证后是字符串标量，先把它转换成真正的JSON数组：

UPDATE day 
SET plan_activities = plan_activities::text::jsonb 
WHERE id = 18 AND jsonb_typeof(plan_activities) = 'string';

修复后再执行原来的jsonb_array_elements查询就能正常展开数组了。

2. 查询时临时处理（不修改数据） #

如果暂时不想修改数据，可以在查询时先把标量转成数组：

SELECT 
  jsonb_array_elements(
    CASE WHEN jsonb_typeof(plan_activities) = 'string' 
         THEN plan_activities::text::jsonb 
         ELSE plan_activities 
    END
  ) AS activities,
  day.* -- 关联day表的其他字段
FROM day 
WHERE id = 18;

3. 实现最终目标：拆分红字段并关联day记录 #

如果要把JSON里的字段拆成独立列，同时保留day表的记录信息，用jsonb_to_recordset更合适：

SELECT 
  day.id, -- 关联day表的id
  rec.activity,
  rec.cardio,
  rec.strength,
  rec.quantity,
  rec.units,
  rec.timeOfDay,
  rec.summary,
  rec.timeOfDayOrder
FROM day,
     jsonb_to_recordset(
         CASE WHEN jsonb_typeof(plan_activities) = 'string' 
              THEN plan_activities::text::jsonb 
              ELSE plan_activities 
         END
     ) AS rec(
         activity text,
         cardio boolean,
         strength boolean,
         quantity int,
         units text,
         timeOfDay text,
         summary text,
         timeOfDayOrder int
     )
WHERE day.id = 18;

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