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