求助：PostgreSQL中拆分JSON数组字段为多列的实现方法

方法一：直接链式JSON提取 #

基于你现有的查询逻辑，直接深入嵌套的JSON对象提取字段即可，无需子查询：

select
  order_id,
  (address::jsonb)->0->>'purchase' as purchasetype,
  (address::jsonb)->0->'address'->>'city' as city,
  (address::jsonb)->0->'address'->>'state' as state,
  (address::jsonb)->0->'address'->>'order_create_date' as order_create_date
from orders;

• 逻辑说明：(address::jsonb)->0 获取JSON数组的第一个元素，->'address' 定位到嵌套的address对象，最后用->>提取对应字段的文本值。

方法二：使用横向连接（Lateral Join）解析 #

如果后续数组可能包含多个元素，这种方式扩展性更好，代码可读性也更强：

select
  o.order_id,
  item->>'purchase' as purchasetype,
  addr.city,
  addr.state,
  addr.order_create_date
from orders o
-- 展开JSON数组为行
cross join lateral jsonb_array_elements(o.address::jsonb) as item
-- 解析嵌套的address对象为关系型字段
cross join lateral jsonb_to_record(item->'address') as addr(
  city text,
  state text,
  order_create_date text
);

方法三：直接解析顶层JSON对象 #

由于你的数组只有一个元素，也可以直接定位到数组第一个元素后，用jsonb_to_record一次性解析所有需要的字段：

select
  o.order_id,
  rec.purchase as purchasetype,
  rec.address->>'city' as city,
  rec.address->>'state' as state,
  rec.address->>'order_create_date' as order_create_date
from orders o
cross join lateral jsonb_to_record(o.address::jsonb->0) as rec(
  purchase text,
  address jsonb
);

关于子查询失败的原因 #

你之前尝试子查询未成功，大概率是因为子查询返回的address字段是文本类型（而非JSONB），导致无法继续用->/->>操作符解析嵌套字段。如果一定要用子查询，需确保将address字段重新转换为JSONB：

select
  sub.order_id,
  sub.purchasetype,
  (sub.address::jsonb)->>'city' as city,
  (sub.address::jsonb)->>'state' as state,
  (sub.address::jsonb)->>'order_create_date' as order_create_date
from (
  select
    order_id,
    (address::jsonb)->0->>'purchase' as purchasetype,
    (address::jsonb)->0->>'address' as address
  from orders
) as sub;

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