Postgres: Duplicate row but update a field in json

July 12, 2022

I’m trying to duplicate all rows in my table that contain a signalVersion: prod but in the duplicated row I’d like to set signalVersion to 0. There are two keys in my table, signal_key and signal_value and both are json objects. signalVersion is a property of the object in column signal_key. Below is an example of signal_key:

{
  "signalType": "OCR_ITEM", 
  "signalVersion": "prod"
}

This is the code I’ve written so far but it’s failing. Does anyone know why my syntax is incorrect?

insert into signals (signal_key, signal_value)
select signal_key -> '{"signalVersion": "0"}', signal_value
from signals
where signal_key @> '{"signalVersion": "prod"}';

>Solution :

You’ll want to use the || operator to merge {"signalVersion": "0"} into the signal_key, not ->:

insert into signals (signal_key, signal_value)
select signal_key || '{"signalVersion": "0"}', signal_value
from signals
where signal_key @> '{"signalVersion": "prod"}';

Alternatively, you could use json_set:

insert into signals (signal_key, signal_value)
select json_set(signal_key, array['signalVersion'], 0), signal_value
from signals
where signal_key @> '{"signalVersion": "prod"}';