By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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"}] |
SELECT 1