Can I refer to data in jsonb table if no matching field in cross join table

user_1480018 0
Viewed 71

I have json data in table that I use to insert new data into final table as follows

CREATE TABLE musicbrainz.acoustid_track (
    id int NOT NULL,
    created timestamp with time zone DEFAULT current_timestamp,
    gid uuid NOT NULL,
    new_id varchar(30)
);

CREATE TABLE musicbrainz.acoustid_track_json (
    data jsonb
);

...... tables loaded ......

The json column data is visible in the query and you can refer to it in the WHERE clause, e.g.:

insert into musicbrainz.acoustid_track 
select id, created, gid, new_id 
from musicbrainz.acoustid_track_json 
cross join jsonb_populate_record(null::musicbrainz.acoustid_track, data);

and this works except acoustid_track_json can contains new records or replacement records, and this is detemrined by if they have an updated field

e.g

New record

{"id":67028798,"gid":"18575a2d-bc9c-48c0-b5d7-f815b97421ed","created":"2020-02-03T00:02:11.315629+00:00"}

Updated record

{"id":66277512,"gid":"a31e1ecc-af48-4b8f-ba65-de5187a5c9a7","new_id":65603612,"created":"2019-11-17T12:37:49.81505+00:00","updated":"2020-02-03T13:12:58.043985+00:00"}

but I cant seem to modify INSERT to refer to updated field, possibly because no updated field in the final table, how do I do this.

jsonb postgresql
1 Answers

The json column data is visible in the query and you can refer to it in the WHERE clause, e.g.:

insert into musicbrainz.acoustid_track 
select id, created, gid, new_id 
from musicbrainz.acoustid_track_json 
cross join jsonb_populate_record(null::musicbrainz.acoustid_track, data)
where data->'updated' is null;
user_19957380
Related