How to iterate over PostgreSQL jsonb array of objects and modify elements?

Question

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 separate elements.

[{"data": "42"}, {"type": "temperature"}, {"data": "1.1"}, {"type": "pressure"}]

GMB · Accepted Answer · 2020-09-29 11:04:19Z

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)

Demo on DB Fiddle:

| jsonb_agg                                                                    |
| :--------------------------------------------------------------------------- |
| [{"data": "42", "type": "temperature"}, {"data": "1.1", "type": "pressure"}] |

Collectives™ on Stack Overflow

How to iterate over PostgreSQL jsonb array of objects and modify elements?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related