Modifying json_populate_record before insertion-CodePudding

Lets say I have som data that looks like

create table test.from
(
    id   integer primary key,
    data json
);

insert into test.from
values
       (4, '{ "some_field": 11 }'),
       (9, '{ "some_field": 22 }');

create table test.to
(
    id        integer primary key,
    some_field int
);

I would like the rows in the "to" table to have the same id key as the "from" row, and expand the json into separate columns. But using json_populate_record like below, will unsurprisingly give me null as key.

Method 1:
insert into test.to
select l.*
from test.from fr
         cross join lateral json_populate_record(null::test.to, fr.data) l;

I can achieve what I'm looking for by naming columns like below

Method 2:
insert into test.to (id, some_field)
select 
  fr.id as id,
  l.some_field
from test.from fr
         cross join lateral json_populate_record(null::test.to, fr.data) l;

The challenge is that I want to avoid naming any columns other than the id column, both since it gets tedious, but also since I'd like to do this in a function where the column names are not known.

What modifications do I have to do to Method 1 to update the record with the correct id?

CodePudding user response：

Just append the id key to your data like this:

insert into test.to
select l.*
from
    test.from fr
        cross join lateral jsonb_populate_record(
            null::test.to,
            fr.data::jsonb || jsonb_build_object('id', fr.id)) l;