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

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

CodePudding user response：

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 (temporary) 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

Test it in Db<>Fiddle.

Read about JSON Functions and Operators in the documentation.

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