PostgreSQL: From JSON to table and back

I have a jsonb column in Postgres and I would like to update parts of it based on some other parts of the value. In Clojure I would have used update, which does new-value = f(old-value). How can I achieve something similar - i.e. deriving the new value from the old one - in Postgres? It turns out I can, with the help of a temporary table and jsonb_to_recordset that can turn a jsonb value into a table and row_to_json + jsonb_agg that can turn that back to jsonb. Let’s see how it works.

The data

I have a table called report with the primary key _id and jsonb column columns. It looks like this:

Table 1. Table report
_idcolumns

123

[{"key": "name", "type": "field", "label": "Name"}, {"key": "Supports", "type": "reference-type-outgoing", "label": "Supports"}, …​]

456

[{"key": "name", "type": "custom", "label": "Name", "dataType": "Text", "sort": "ASC"}]

789

[]

Notice that the objects in the array may have different keys.

What I want to do is to update every column of the type reference-type-outgoing or reference-type-incoming by appending the direction - outgoing or incoming - to the key. F.ex. the column above would become {"key": "Supports—​outgoing", "type": "reference-type-outgoing", …​.

Part 1: jsonb → table

First I need to extract the individual column elements into a table, with keys becoming column names. I also include the source table’s _id to remember what belongs together. I will put them into a temporary table so that I can update the values in it and that it will be automatically removed when the transaction or session ends.

Extract each jsonb array element into a row in a temporary table
create temp table tmp_report_cols AS
 select r._id, rep_col.* from report r,
  lateral jsonb_to_recordset(r.columns)        (1)
   as rep_col(key text, type text, label text, (2)
              "dataType" text, sort text)      (3)
 where r."columns" @@ '$[*].type starts with "reference-type-"';
1while LATERAL allows us to run a subquery on the table column, jsonb_to_recordset will turn each object in a jsonb array into a table row
2the as <name>(<types def>) is crucial as it tells recordset what the types of the columns are
3double-quote names that are not all lowercase so that the casing will be preserved

The data there will be:

Table 2. Table tmp_report_cols
_idkeytypelabeldataTypesort

123

name

field

Name

null

null

123

Supports

reference-type-outgoing

Supports

null

null

456

name

custom

Name

Text

ASC

Part 2: apply the updates the temporary table

Now I want to find all column definitions with the type=reference-type-* and add the direction to their key if it isn’t there yet:

Update the deconstructed json parts
update tmp_report_cols
  set key = key || case when type = 'reference-type-outgoing'
                   then '--outgoing' else '--incoming' end
where type like 'reference-type-%' and key !~ '--(incoming|outgoing)$';

Part 3: table → jsonb

Finally I want to propagate the changes back to the source table. So I need to re-constitute the complete jsonb value. I leverage a WITH to put the rebuilt json into a temporary table to use in the update statement:

Reconstruct the whole json value and update the source table
with fixed as (
  -- Turn the table back to jsonb (note that the double `((` is crucial):
 select _id, jsonb_agg( (1)
  json_strip_nulls(     (2)
   row_to_json(         (3)
    (SELECT r FROM (SELECT key, type, label, "dataType", sort) r))))
    as cols (4)
 from tmp_report_cols group by _id)
update report r set "columns" = fixed.cols from fixed where r._id = fixed._id
-- returning r._id, "columns" -- add this to see what was changed
1jsonb_agg works with group by _id to combine all values for a particular _id into a jsonb array
2json_strip_nulls removes any keys with null values, which I want in this particular case
3row_to_json turns each row into a json object
4The two nested selects ensure that row_to_json gets correct column names instead of the f1, f2, etc. that it would use otherwise

It seems that the order of the resulting array will be the same as in the original input, which is exactly what I need.

Optional: explicit cleanup
drop table tmp_report_cols;

That’s it! The relevant .key values inside the jsonb column have been updated.


Tags: database PostgreSQL


Copyright © 2022 Jakub Holý
Powered by Cryogen
Theme by KingMob