Insert array into jsonb column from table postgresql

Question

Im trying to create an insert statement into a jsonb column that looks like this

  "EmpNames": [
    {
      "id": "5680",
      "Surname": "James",
      "GivenName": "Julie",
      "MiddleName": "Jane",
    }
  ]

my sql statement is

select    'EmpNames',   
                      Array[
                               'Surname', surname
                               'GivenName', first_name,
                               'MiddleName', middle_name
                            ]::jsonb[]      
from stg.employees

invalid input syntax for type json
                               'Surname' Token "Surname" is invalid.

Thanks

klin · Accepted Answer · 2022-11-02 01:23:37Z

2

Use jsonb_build_object() to create a json object and jsonb_agg() to aggregate objects into a json array:

select
    jsonb_build_object(
        'EmpNames',
        jsonb_agg(
            jsonb_build_object(
                'id', id,
                'surname', surname,
                'first_name', first_name,
                'middle_name', middle_name
            )
        )
    )
from employees

Test it in db<>fiddle.

If you want to create a json object for each id separately, the query doesn't require an aggregate:

select
    jsonb_build_object(
        'EmpNames',
        jsonb_build_array(
            jsonb_build_object(
                'id', id,
                'surname', surname,
                'first_name', first_name,
                'middle_name', middle_name
            )
        )
    )
from employees

Db<>fiddle.

edited Nov 2, 2022 at 1:23

answered Oct 28, 2022 at 1:25

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Liem Nguyen Over a year ago

thanks @klin it worked..is there a way to write it so it comes out on separate rows for each person?

Collectives™ on Stack Overflow

Insert array into jsonb column from table postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related