Search code examples
postgresqljsonb

Can I use jsonb_set with the new value (3rd argument) coming from a select statement?

I have a table with a jsonb column and I want to migrate every record to a new schema.

Old schema: {"foo": "bar"} New schema: {"foo": {"value": "bar"}

The bar value varies by row and I want to preserve that value. I need some way to lookup the value of foo from each record.

WITH data AS (
  SELECT id AS id, col -> 'foo' AS foo
  FROM t
) UPDATE t SET col = jsonb_set('{"foo": {"value": ""}}', '{foo,value}', SELECT foo FROM data WHERE data.id = t.id);

I get this error with Postgres 11.21.

ERROR:  syntax error at or near "SELECT"
LINE 4: ...jsonb_set('{"foo": {"value": ""}', '{foo,value}', SELECT foo...
Solution

  • No need for a CTE, and you can move the current content without a subquery:

    UPDATE t 
SET col = jsonb_set(col, '{foo}', jsonb_build_object('value', col->>'foo'))
WHERE id = ?; -- if needed