PostgreSQL如何将JSON中values数组的每个元素拆分为独立行

你当前的查询已经完成了顶层JSON数组的拆分，只需要对每个配置对象内的values下拉选项数组做第二次拆分即可，具体实现如下： #

最终查询语句 #

SELECT
  s.projectid,
  s.prompttype,
  el.inputs->>'name' AS name,
  el.inputs->>'desc' AS desc,
  el.inputs->>'type' AS type,
  -- 如需输出带引号的格式，改为 '"' || val.value || '"' AS option_value 即可
  val.value AS option_value,
  s.created,
  s.modified
FROM source_redshift.staticprompts AS s,
     -- 拆分顶层inputs配置数组
     jsonb_array_elements(s.inputs::jsonb) el(inputs)
-- 拆分每个配置项的values下拉选项数组
LEFT JOIN LATERAL jsonb_array_elements_text(
  -- 兼容无values字段的配置项（如type为string的输入项）
  COALESCE(el.inputs->'values', '[]'::jsonb)
) val(value) ON true;

说明 #

• 如果你仅需要保留有下拉选项的行，删除LEFT JOIN LATERAL和ON true，将jsonb_array_elements_text(...) val(value)直接加到FROM后的列表里即可，无values的配置项会自动过滤
• jsonb_array_elements_text会直接将JSON数组元素转换为文本类型，无需额外做类型转换
• 若你的字段存储类型是json而非jsonb，把所有jsonb前缀替换为json即可正常运行

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