Given jsonb array and PostgreSQL 12:
[{"data":"42","type":"TEMPERATURE"},{"data":"1.1","type":"PRESSURE"}]
Need to convert it to:
[{"data":"42","type":"temperature"},{"data":"1.1","type":"pressure"}]
Is it possible somehow to iterate over jsonb array and downcase only “type” values?
I tried:
SELECT jsonb_agg( jsonb_build_object(k, CASE WHEN k <> 'type' THEN v ELSE lower(v::text)::jsonb END) ) FROM jsonb_array_elements( '[{"data":"42","type":"TEMPERATURE"},{"data":"1.1","type":"PRESSURE"}]'::jsonb ) e(e), lateral jsonb_each(e) p(k, v)
but it separates data and type pairs into separateelements.
[{"data": "42"}, {"type": "temperature"}, {"data": "1.1"}, {"type": "pressure"}]
Advertisement
Answer
You need an intermediate level of nesting to rebuild the objects before you aggregate them in the array: for this, you can use a lateral join.
I would also recommend keeping track of the position of each object in the original array, so you can propagate it the the final result – with ordinality
comes handy.
SELECT jsonb_agg(x.obj order by e.n) FROM jsonb_array_elements('[{"data":"42","type":"TEMPERATURE"},{"data":"1.1","type":"PRESSURE"}]'::jsonb) with ordinality e(e, n) CROSS JOIN LATERAL ( SELECT jsonb_object_agg(k, CASE WHEN k <> 'type' THEN v ELSE lower(v::text)::jsonb END) FROM jsonb_each(e) p(k, v) ) x(obj)
| jsonb_agg | | :--------------------------------------------------------------------------- | | [{"data": "42", "type": "temperature"}, {"data": "1.1", "type": "pressure"}] |