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

PostgreSQL: How to update JSON (jsonb data type) using value in another column of the same table?

I have the following table defined in Postgres (version 12.12):

                Table "public.test"
  Column   | Type  | Collation | Nullable | Default
-----------+-------+-----------+----------+---------
 uuid      | uuid  |           |          |
 name      | text  |           |          |
 json_data | jsonb |           |          |

Here is a sample row:

                 uuid                 | name |  json
 12345678-3ffd-472b-ac39-31eacc4a6785 | foo  | {"id": 1132}

I want to update json_data using a value from a different column in the same table. Something like this:

UPDATE test SET json_data = json_data || '{"xyz":{"enabled": false, "uuid": test.uuid}}';

where the value of uuid is the "uuid" value from the same row. So, I would like the desired updated row to look like this:

                uuid                 | name |  json
 12345678-3ffd-472b-ac39-31eacc4a6785 | foo  | {"id": 1132, "xyz":{"enabled": false, "uuid": "12345678-3ffd-472b-ac39-31eacc4a6785"}}

Tried this, but got a syntax error:

    UPDATE test SET json_data = json_data || '{"xyz":{"enabled": false, "uuid": test.uuid}}';

ERROR:  invalid input syntax for type json
LINE 1: update test set json_data = json_data || '{"xyz":{"e...
                                                 ^
DETAIL:  Token "test" is invalid.
CONTEXT:  JSON data, line 1: {"xyz":{"enabled": false, "uuid": test...
Tags:

2

Answers


  1. 0

    You can’t put an SQL expression like test.uuid in a literal and expect it to be evaluated. You could use string interpolation and casts, but that’s ugly and easy to mess up.

    Rather, build the json(b) value using the jsonb_build_object function:

    UPDATE test
SET json_data = json_data || jsonb_build_object(
  'xyz', jsonb_build_object(
    'enabled', FALSE,
    'uuid', test.uuid
  )
);

    Alternatively, you could use jsonb_set instead of concatenation:

    UPDATE test
SET json_data = jsonb_set(
  jsonb_set(
    json_data,
    '{xyz,enabled}',
    FALSE,
  ),
  '{xyz,uuid}',
  test.uuid
);

    However this is rather ugly, I would recommend it only when setting a single property.

    Login or Signup to reply.
  2. 0

    Because you use json instead of jsonb, you need a little casting to use the json-functions for updating json content:

    UPDATE test
SET json_data = jsonb_set(COALESCE(json_data,'{}')::jsonb
        , '{xyz}' -- target key
        , jsonb_build_object( -- new content for the targeted key
                'enabled'   , FALSE
                , 'uuid'    , uuid -- from your column "uuid"
            )
      , TRUE -- create key if it doesn't exist yet
      );

    Now you don’t need any text functions and you’re safe when you have some NULL’s in some column. Because

    NULL || ‘some content’

    results in a NULL

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search