Search code examples
sqljsonsql-updatejsonb

SQL - update info in a specified column for multiple rows

I have an SQL table laid out like so

Column Type Modifiers
uuid uuid not null
name character varying
type character varying
info jsonb
created bigint

Inside the info column is numerous things such as {"id": "1402417796043342360", "colour": "blue", "subtype": "test", "description": "8.7"}

However, I need to update subtype to subType inside the info column for numerous rows. Basically, anywhere that has subtype needs changed to subType

Is there an easy way to specify this change within the column?

I tried UPDATE table_name SET info = REPLACE('info', '"subtype"', '"subType"');

but got the error ERROR: column "info" is of type jsonb but expression is of type text LINE 1: UPDATE table_name SET info = REPLACE('info', '"su... ^ HINT: You will need to rewrite or cast the expression.

Solution

  • You seem to be using Postgres, as denoted by the use of datatype jsonb, which is specific to this database.

    Basically, anywhere that has subtype needs changed to subType.

    Here is one way to do this in Postgres :

    update t 
set info = info - 'subtype' || jsonb_build_object('subType', info -> 'subtype')
where info ? 'subtype'

    The where clause filters on rows whose jsonb that contain key 'subtype'. The set clause updates the object by removing the offending key (using operator -), then adding the relevant key/value pair (using ||).