How do I transform a JSONB array in postgres by moving values to a key if it doesn't exist?

Question

Let's say I have a table

create table test (id int, data jsonb)

with data

insert into test (id, data) values (1, '{ "foo": "bar", "coords": [ { "lat": 1.0, "lng": 1.0 } ] }');
insert into test (id, data) values (1, '{ "foo": "baz", "coords": [ { "lat": 1.0, "lng": 1.0 }, { "lat": 2.0, "lng": 2.0 } ] }');
insert into test (id, data) values (1, '{ "coords": [ { "position": { "lat": 1.0, "lng": 1.0 } }, { "position": { "lat": 2.0, "lng": 2.0 } } ] }');

And I want to normalize all the rows so that the coords array has the lat/lng inside a position key.

How would I accomplish this with postgres's jsonb functions? I want to preserve any other values in the object(s) as well.

Andrew · Accepted Answer · 2022-04-04 15:15:59Z

1

You can use jsonb_set:

select
  id,
  data,
  jsonb_set(data, array['coords'], data2, false) as result
from
  (
    select
      id,
      data,
      (
        select
          json_agg(case when e->'position' is null then jsonb_build_object('position', e) else e end)
        from
          jsonb_array_elements(data->'coords') e
      )::jsonb as data2
    from
      test
  ) t;

answered Apr 4, 2022 at 15:15

Andrew

1757 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I transform a JSONB array in postgres by moving values to a key if it doesn't exist?

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