CODEWITHSUNDEEP
postgresqljsonb
TimB
TimB

Reputation: 71

Postgresql Insert and Update JSONB Column

Would someone please show me how to do insert & update a jsonb column? Say I have this Permanent table that has 2 columns (ticker varchar(10), data(jsonb)), with ticker is the primary key: In it, it has this data

XYZ,{"sales":{"2022-06-30":1500,"2022-03-31":1200}}

Now, a new quarter has been reported and the Temp table has this data:

XYZ,{"sales":{"2022-09-30":1800}}

How can I do INSERT INTO so it update just the data(jsonb)

INSERT INTO perm_table(ticker,data)
FROM temp_table ticker,data
ON CONFLICT ????? DO NOTHING;

Thank you so much in advance for your guidance!

Upvotes: 0

Views: 66

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

One option is to use a Common table expression(or possibly a subquery) after outer full joining tables while reconstructing the objects and completing missing values through use of COALESCE() such as

WITH j AS
(
  SELECT id_, jsonb_build_object('sales', js) AS js
    FROM (SELECT COALESCE(t.id, p.id) AS id_,
                 jsonb_object_agg(COALESCE((js_t).key, (js_p).key),
                                  COALESCE((js_t).value, (js_p).value)) AS js
            FROM (SELECT id, jsonb_each(val) AS js_t
                    FROM temp_table
                   CROSS JOIN jsonb_each(data) AS arr(key, val)) AS p
            FULL JOIN (SELECT id, jsonb_each(val) AS js_p
                        FROM perm_table
                       CROSS JOIN jsonb_each(data) AS arr(key, val)) AS t
              ON (js_p).key = (js_t).key
           GROUP BY id_) AS q
)
UPDATE perm_table
   SET data = j.js
  FROM j 
 WHERE id = id_

Demo

Upvotes: 1

Related Questions