PostgreSQL: From JSON to table and back

October 22, 2022

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`
_id	columns
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-"';

1	while 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
2	the `as <name>(<types def>)` is crucial as it tells recordset what the types of the columns are
3	double-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`
_id	key	type	label	dataType	sort
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

1	jsonb_agg works with `group by _id` to combine all values for a particular _id into a jsonb array
2	`json_strip_nulls` removes any keys with null values, which I want in this particular case
3	`row_to_json` turns each row into a json object
4	The 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