PostgreSQL如何提取指定路径下JSON对象的item_sku字段

问题场景 #

你通过(gr.request_body -> 0 #>> '{lines}')获取到包含多个商品对象的JSON数组，需要从中提取每个对象的item_sku字段并添加为单独列。

解决方案 #

根据需求不同，有两种处理方式：

1. 将每个item_sku转为单独行

如果需要把每个商品的item_sku拆分成独立行，使用jsonb_array_elements（若列类型为json则替换为json_array_elements）展开数组，再提取字段：

select 
    o.reference,
    o.id as "ord_id",
    o.created_at,
    o.aasm_state,
    o.payment_details -> 'payment_method' as "payment_method", 
    max(gr.updated_at) as "last_updated_at", 
    o.shipping_address -> 'country' as "country",
    line ->> 'item_sku' as item_sku
from 
    -- 替换为你的主表实际名称
    orders o
join 
    -- 替换为gr表的实际连接条件
    gr_table gr on o.id = gr.order_id
cross join 
    jsonb_array_elements((gr.request_body -> 0 -> 'lines')::jsonb) as line
group by 
    o.reference, o.id, o.created_at, o.aasm_state, 
    o.payment_details -> 'payment_method', o.shipping_address -> 'country', 
    line ->> 'item_sku';

2. 将同一订单的item_sku合并为数组

如果需要把同一个订单下的所有item_sku合并成一个数组列，用array_agg函数聚合：

select 
    o.reference,
    o.id as "ord_id",
    o.created_at,
    o.aasm_state,
    o.payment_details -> 'payment_method' as "payment_method", 
    max(gr.updated_at) as "last_updated_at", 
    o.shipping_address -> 'country' as "country",
    array_agg(line ->> 'item_sku') as item_skus
from 
    orders o
join 
    gr_table gr on o.id = gr.order_id
cross join 
    jsonb_array_elements((gr.request_body -> 0 -> 'lines')::jsonb) as line
group by 
    o.reference, o.id, o.created_at, o.aasm_state, 
    o.payment_details -> 'payment_method', o.shipping_address -> 'country';

关键说明 #

• gr.request_body -> 0 -> 'lines'：定位到request_body数组第一个元素中的lines商品数组
• jsonb_array_elements()：将JSON数组展开为多行记录，每行对应一个商品对象
• line ->> 'item_sku'：从单个商品对象中提取item_sku并转为文本类型

内容的提问来源于stack exchange，提问作者Aurélio Furlan