Update JSONB with UUID value

September 21, 2022

I have a table with a column of jsonb type, the JSON object is of key:value pairs, the problem is, I need to update one of the keys to contain a UUID instead of the original value.

The update statement I’m using:

UPDATE
    public.applications a
SET
    data = jsonb_set(data, '{id}', (SELECT b.uuid FROM public.devices b WHERE b.id = (a.data ->> 'id')::integer)::text)

Postgres returns the following error:

ERROR:  function jsonb_set(jsonb, unknown, text) does not exist

I’ve tried to cast the value to different data types, but with same result.

Sample data

id | data
---------
1  | {"id": 1}
2  | {"id": 2}

Expected output

id | data
---------
1  | {"id": device_uuid_here}
2  | {"id": device_uuid_here}

DB_VERSION: PostgreSQL12.12

>Solution :

UPDATE public.applications a
SET data = jsonb_set(data, '{id}', 
  (SELECT '"'||b.uuid::text||'"' FROM public.devices b 
  WHERE b.id = (a.data ->> 'id')::integer)::jsonb);