如何提取PostgreSQL jsonb数组中的嵌套对象？jsonb_to_recordset使用问题

• 原有写法存在两个核心错误：
  1. jsonb_to_recordset 属于返回集合的函数，不能直接写在SELECT输出列表中绑定字段结构，必须放在FROM子句中定义返回字段
  2. 额外调用jsonb_agg将全表的cluster_restrictors数组再包裹了一层数组，不符合按原表行展开的需求

SELECT
  j.restrictor_type,
  j.restrictor_reqs
FROM clusters c
CROSS JOIN LATERAL jsonb_to_recordset(c.cluster_restrictors) AS j (
  restrictor_type text,
  restrictor_reqs jsonb
);

逻辑说明 #

• CROSS JOIN LATERAL 会遍历clusters表的每一行，将该行的cluster_restrictors jsonb数组传入jsonb_to_recordset展开为结构化行
• 在AS子句中定义需要提取的两个字段的名称和类型，就可以直接在SELECT中读取对应字段值，输出结果和要求的目标格式完全匹配。

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