Extract an array from a postgres json column and map it

Question

create table test(a json);

insert into test(a) 
values('{"orders":[{"orderId":1}, {"orderId":2, "status":"done"}, {"orderId":3}]}');

Given the structure above, can I get an array or set of orderIds that are not 'done'? And by that, I mean can I get the orderIds from with sql or plpgsql?

Any advice would be great! Thank you very much!

That's not valid JSON, did you actually try inserting it? (edit: fixed it, but seriously, test your examples). — Craig Ringer
– Craig Ringer, Commented Jun 10, 2014 at 2:02

user229044 · Accepted Answer · 2022-01-13 18:33:21Z

25

Once I fixed the broken JSON, this was just a LATERAL query to unpack the array and filter it.

select (x->>'orderId')::integer
from test,
     json_array_elements(a->'orders') x 
where (x ->> 'status') IS DISTINCT FROM 'done';

I used IS DISTINCT FROM so I didn't have to test for both NULL (no key) and != 'done'.

edited Jan 13, 2022 at 18:33

user229044♦

241k41 gold badges347 silver badges350 bronze badges

answered Jun 10, 2014 at 2:06

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LikeMaBell Over a year ago

For anyone else running into the problem, you'll need to use jsonb_array_elements on a jsonb datatype (note the extra B).

Collectives™ on Stack Overflow

Extract an array from a postgres json column and map it

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related