Extract an array from a postgres json column and map it
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!
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'
.