Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table target_table(x int, y int, json jsonb);
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();

CREATE TABLE



CREATE TABLE



CREATE FUNCTION



CREATE TRIGGER


insert into json_data values
    ('{"x": 1}'::jsonb),
    ('{"x": 2, "y": 3}'),
    ('{"x": 8, "z": 3}'),
    ('{"x": 5, "y": 2, "z": 3}');

INSERT 0 4


select *
from target_table;


x
y
json


1
null
null

2
3
null

8
null
{"z": 3}

5
2
{"z": 3}



SELECT 4

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