Question 109411 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – 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.

Tags:

2

Answers


  1. 0

    If you are using SQL Server you can try SQL_MODIFY.

    The specific query for your case could be

    UPDATE table_name
SET info = JSON_MODIFY(info, '$.subtype.*~', 'subType')

    The only thing that needs to be tested is that .*~ return the dict key.

    Another solution could be using the json column as a simple string and operating a replace (maybe you should have to do some casting)

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

    REPLACE is supported by multiple SQL Dialects

    Login or Signup to reply.
  2. 0

    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 ||).

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

Back To Top
Search