insert into postgresql table jsonb data-CodePudding

I have a table called emp with a column called info as type jsonb

now i want to insert data into it from a table

so it looks like this

{
  "birth": {"date": "1980-04-28"},"Name": {"surname": "James","firstname": "Jacob"}
}

I issue the SQL

insert into emp(info)
select 
  row_to_json(x)::jsonb from 
            (select birth_date as date, lastname as surname, given_name as firstname FROM stg.employees) x;

but it returns

{
  "date": "1975-11-29T00:00:00",
  "surname": "James",
  "firstname":"Jaconb"
}

and not

    {
      "birth": {"date": "1980-04-28"},
      "Name": {"surname": "James","firstname": "Jacob"}
    }

could you please point me in the right direction? thank you

CodePudding user response：

insert into emp(info)
select 
  row_to_json(x)::jsonb from 
            (select jsonb_build_object('date', birth_date) as birth, jsonb_build_object('surname', lastname, 'firstname', given_name) as name FROM stg.employees) x;