PostgreSQL 12中如何查询JSONB数组的所有元素？

基础方案：展开数组所有元素 #

使用jsonb_array_elements结合LATERAL连接，可将JSONB数组的每个元素拆分为单独的行，同时保留原表的date字段：

SELECT
  t.date,
  elem -> 'item_info' ->> 'field_1' AS field_a,
  elem -> 'item_info' ->> 'field_2' AS field_b,
  elem -> 'status' -> 'substatus' ->> 'subsubstatus' AS field_c
FROM mytable t
CROSS JOIN LATERAL jsonb_array_elements(t.stats) AS elem;

筛选第1至第n个元素 #

如果仅需要数组的前n个元素，可通过WITH ORDINALITY为每个元素添加从1开始的序号，再用WHERE子句筛选序号范围：

-- 将n替换为你需要的具体数字，比如3
SELECT
  t.date,
  elem -> 'item_info' ->> 'field_1' AS field_a,
  elem -> 'item_info' ->> 'field_2' AS field_b,
  elem -> 'status' -> 'substatus' ->> 'subsubstatus' AS field_c
FROM mytable t
CROSS JOIN LATERAL jsonb_array_elements(t.stats) WITH ORDINALITY AS elem(pos)
WHERE pos BETWEEN 1 AND n;

关键说明 #

• jsonb_array_elements：专门用于拆分JSONB数组，返回数组中的每个元素作为单独行。
• LATERAL：允许子查询引用主表字段（此处为t.stats），确保每一行的数组都被正确拆分。
• WITH ORDINALITY：为拆分后的元素附加自增序号（从1开始），序号1对应原数组索引0的元素，序号n对应原数组索引n-1的元素。
• 若某行数组长度小于n，查询会自动返回该行数组的所有实际元素，不会生成空行。

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