PostgreSQL中拆分jsonb数组列至新列：food_order表处理需求

你当前使用jsonb_array_elements会将JSON数组的每个元素拆分为单独行，这不符合把状态字段横向展开为同一行多列的需求。以下两种方法可以实现目标：

方法一：固定元素数量直接提取 #

如果确认status_events的元素数量固定（比如最多2个），可以直接通过数组索引提取对应字段：

SELECT 
    _id,
    status,
    status_events->0->>'status_name' AS status_name_1,
    status_events->0->>'status_time' AS status_time_1,
    status_events->1->>'status_name' AS status_name_2,
    status_events->1->>'status_time' AS status_time_2
FROM food_order;

注：JSON数组索引从0开始，若数组元素不足对应数量，该列会返回NULL。

方法二：通用行转列（支持可变元素数量） #

如果status_events的元素数量不固定，可结合聚合函数和WITH ORDINALITY实现行转列：

SELECT 
    _id,
    status,
    MAX(CASE WHEN position = 1 THEN item_object->>'status_name' END) AS status_name_1,
    MAX(CASE WHEN position = 1 THEN item_object->>'status_time' END) AS status_time_1,
    MAX(CASE WHEN position = 2 THEN item_object->>'status_name' END) AS status_name_2,
    MAX(CASE WHEN position = 2 THEN item_object->>'status_time' END) AS status_time_2
    -- 如需支持更多元素，继续添加对应position的CASE语句即可
FROM food_order,
     jsonb_array_elements(status_events::jsonb) WITH ORDINALITY arr(item_object, position)
GROUP BY _id, status;

注：若status_events字段本身是jsonb类型，可去掉::jsonb转换。

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