如何将PostgreSQL中JSONB数组数据展开为同一行的多列？

没问题，这种把拆分后的多行数据合并为单行、按Register值生成对应列的需求，用条件聚合就能轻松搞定——刚好你已经提前知道所有Register的可能值，操作起来更直接。

核心思路 #

你已经通过jsonb_to_recordset把JSONB数组拆成了多行数据，现在只需要按id和name分组，针对每个已知的Register值，用CASE语句配合聚合函数（比如MAX，因为每个id+Register组合只会有一行数据），把对应行的字段值提取到目标列中。

完整SQL语句 #

SELECT
  id,
  name,
  -- 处理Register为xyz的相关列
  MAX(CASE WHEN register = 'xyz' THEN TRUE ELSE FALSE END) AS "register_xyz?",
  MAX(CASE WHEN register = 'xyz' THEN "AgeFrom" END) AS "xyz_age_from",
  MAX(CASE WHEN register = 'xyz' THEN "AgeTo" END) AS "xyz_age_to",
  MAX(CASE WHEN register = 'xyz' THEN "MaximumNumber" END) AS "xyz_maximum_number",
  -- 处理Register为abc的相关列
  MAX(CASE WHEN register = 'abc' THEN TRUE ELSE FALSE END) AS "register_abc?",
  MAX(CASE WHEN register = 'abc' THEN "AgeFrom" END) AS "abc_age_from",
  MAX(CASE WHEN register = 'abc' THEN "AgeTo" END) AS "abc_age_to",
  MAX(CASE WHEN register = 'abc' THEN "MaximumNumber" END) AS "abc_maximum_number",
  -- 如果有第三个Register值（比如def），直接复制上面的块替换即可
  MAX(CASE WHEN register = 'def' THEN TRUE ELSE FALSE END) AS "register_def?",
  MAX(CASE WHEN register = 'def' THEN "AgeFrom" END) AS "def_age_from",
  MAX(CASE WHEN register = 'def' THEN "AgeTo" END) AS "def_age_to",
  MAX(CASE WHEN register = 'def' THEN "MaximumNumber" END) AS "def_maximum_number"
FROM (
  -- 你原来的JSONB拆分查询
  SELECT items.id, items.name, care_ages.* 
  FROM items, 
       jsonb_to_recordset(items.care_ages) AS care_ages ("AgeFrom" integer, "AgeTo" integer, "Register" text, "MaximumNumber" integer)
) AS split_data
GROUP BY id, name;

关键细节说明 #

1. 为什么用MAX？
   对于同一个id和name，每个Register值只会对应一行数据，MAX会精准提取这一行的字段值；如果某个Register不存在，对应的列会返回NULL，你可以根据需求修改ELSE部分（比如改成0或者''）。

2. 你之前尝试CASE失败的原因
   单独使用CASE而不配合分组和聚合函数的话，SQL只会返回第一行的匹配结果，无法把同一id的多行数据合并成单行。通过GROUP BY id, name+聚合函数，才能把多行的结果整合到同一行的对应列中。

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