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!

Answer 1

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'.