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

Answer 1

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.