I have this simple table

create table orders 
(
  order_id int,
  address json
);

insert into orders(  order_id, address)
values
(1, '[{"purchase": "online", "address": {"state": "LA", "city": "Los Angeles", "order_create_date": "2022-12-01T14:37:48.222-06:00"}}]'),
(2, '[{"purchase": "instore", "address": {"state": "WA", "city": "Seattle", "order_create_date": "2022-12-01T14:37:48.222-06:00"}}]'),
(3, '[{"purchase": "online", "address": {"state": "GA", "city": "Atlanta", "order_create_date": "2022-12-01T14:37:48.222-06:00"}}]'); 

so far I was able to split purchase and address into two column

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

1   online  {"city": "Los Angeles", "state": "LA", "order_create_date": "2022-12-01T14:37:48.222-06:00"}
2   instore {"city": "Seattle", "state": "WA", "order_create_date": "2022-12-01T14:37:48.222-06:00"}
3   online  {"city": "Atlanta", "state": "GA", "order_create_date": "2022-12-01T14:37:48.222-06:00"}

but I was wondering if anyone can help with how I can also split the address into 3 column(state, city, order_created_date)

I tried to subquery but won't work

I would like to see something like this

1 | online | Los Angeles | LA | 2022-12-01T14:37:48.222-06:00

2 | instore | Seattl | WA | 2022-12-01T14:37:48.222-06:00

3 | online | Atlanta | GA | 2022-12-01T14:37:48.222-06:00

CodePudding user response：

Try this :

SELECT o.order_id
     , a->>'purchase' AS purchasetype
     , a->'address'->>'state' AS state
     , a->'address'->>'city' AS city
     , a->'address'->>'order_create_date' AS order_created_date
  FROM orders o
 CROSS JOIN LATERAL jsonb_path_query(o.address, '$[*]') a

Result :

see dbfiddle