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

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.

CodePudding user response：

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;