postgresql triggers to insert json data into columns and keep left over fields

I have found this question PostgreSQL: Efficiently split JSON array into rows
I have a similar situation but for inserts instead. Considering I do not have a table but raw json in a ndjson file...

{"x": 1}
{"x": 2, "y": 3}
{"x": 8, "z": 3}
{"x": 5, "y": 2, "z": 3}

I want to insert the data into a table of the form (where json fields which do not have a column are stored in the json column)

x	y	json
1	NULL	NULL
2	3	NULL
8	NULL	{"z": 3}
5	2	{"z": 3}

How do I define my table such that postgresql does it automatically on insert or \copy

Solution

Use the operator -> and cast the value to the proper type for values of existing regular columns. Use the delete operator to get the remaining JSON values.

I have used CTE in the example. Instead, create the table json_data with a single JSONB column and copy the JSON file to it with \copy

with json_data(json) as (
values
    ('{"x": 1}'::jsonb),
    ('{"x": 2, "y": 3}'),
    ('{"x": 8, "z": 3}'),
    ('{"x": 5, "y": 2, "z": 3}')
)

select 
    (json->'x')::int as x, 
    (json->'y')::int as y, 
    nullif(json- 'x'- 'y', '{}') as json
from json_data

Read about JSON Functions and Operators in the documentation.

Note. In Postgres 10 or earlier use the ->> operator instead of ->.

To automate the conversion when importing json data, define a trigger:

create table json_data(json jsonb);

create or replace function json_data_trigger()
returns trigger language plpgsql as $$
begin
    insert into target_table
    select
        (new.json->>'x')::int, 
        (new.json->>'y')::int, 
        nullif(new.json- 'x'- 'y', '{}');
    return new;
end $$;

create trigger json_data_trigger
before insert on json_data
for each row execute procedure json_data_trigger();

Test it in Db<>Fiddle.