insert into postgresql table jsonb data

Question

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

Edouard · Accepted Answer · 2022-10-25 06:34:26Z

1

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;

answered Oct 25, 2022 at 6:34

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

3 Comments

Henry Over a year ago

Consider adding an explanation of what you've changed and how this fixes the issue.

Liem Nguyen Over a year ago

thanks Edouard - that worked could you show me how to write it when its nested eg ``` "currentAddress": { "postal": { "type": "Street", "state": "MI", "zipcode": "90801", "number": "1", "StreetName": "First Ave" }, "residential": { "type": "Street", "state": "CA", "zipcode": "50891", "number": "167", "StreetName": "Fifth Ave" } } ```

Edouard Over a year ago

using row_to_json and/or json_build_object, you have to iterate as many times as the number of json nested objects. In your last example you have three levels so :

json_build_object('currentAddress', json_build_object('postal', '{}' :: json, 'residential', json_build_object('type', 'Street', 'state', 'NT', 'suburb', 'Darwin', 'postcode', '0801', 'numberlot', '1', 'StreetName', 'Smith', 'unitDetails', '1')))

Collectives™ on Stack Overflow

insert into postgresql table jsonb data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related