Question 273775 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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...
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    It needed parentheses around the select:

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

  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search