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

Question

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

klin · Accepted Answer · 2022-05-31 00:17:54Z

1

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.

edited May 31, 2022 at 0:17

answered May 30, 2022 at 23:05

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

Yorai Levi Over a year ago

I am aware of this article: konbert.com/blog/import-json-into-postgres-using-copy I want something to also work on insertion time. not just from already existing table

Yorai Levi Over a year ago

Thanks for the edit! I tried it on Fiddle. the solution works perfectly. I just changed return new to return NULL so I don't have duplicate data!

klin Over a year ago

@YoraiLevi - Good idea with return null, then json_data acts like a pipe.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related