PostgreSQL调用json_object_keys报错 如何将sub_mark键值插入临时表

错误原因 #

报错cannot call json_object_keys on an array的根因如下：

• 你传入的sub_mark对应值是JSON数组（参数示例中为[{"PAT45":60}]，外层为中括号包裹的数组结构）
• json_object_keys函数仅支持JSON对象类型的输入，不能直接作用于数组
• 原有SQL还存在逻辑错误：外层mark_coll本身是JSON对象而非数组，直接传入json_array_elements也会触发异常

调整后实现代码 #

你需要先展开sub_mark对应的数组，拿到数组内的单个JSON对象后，再提取键值对，适配你函数入参结构的代码如下：

CREATE TEMP TABLE temp_marks AS
SELECT 
  json_object_keys(sub_mark_item) AS mark_key,
  sub_mark_item -> json_object_keys(sub_mark_item) AS mark_value
FROM (
  -- 第一步：展开sub_mark对应的JSON数组，得到每个独立的评分对象
  SELECT json_array_elements(mark_coll -> 'sub_mark') AS sub_mark_item
  -- $2对应函数的第二个入参，即你传入的JSON格式评分数据
  FROM (SELECT $2::json AS mark_coll) AS input_json
) AS sub_mark_items;

如果你使用的是性能更好的jsonb类型，将所有json前缀的函数替换为jsonb前缀即可：jsonb_array_elements、jsonb_object_keys。

效果验证 #

执行后查询临时表即可拿到提取完成的键值对：

SELECT * FROM temp_marks;

返回结果示例：

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