Question 286131 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

Modify column data stored as JSON

Lets say i have json column as JSON_COLUMN in which i am storing the data in below format

Table structure: (2 columns only)

ID - Autoincrement

JSON_COLUMN - Varchar2(1000)

Sample Data:

ID : 1

JSON_COLUMN :

[
   {
      "Name":"Number",
      "Id":"PhoneNumber",
      "Value":"+393123456789"
   },
   {
      "Name":"Name",
      "Id":"FirstName",
      "Value":"John"
   },
   {
      "Name":"City",
      "Id":"CityID",
      "Value":"NYC"
   }
]

Now i want update query of mariadb which should replace value of City from NYC to CALI

update tablename set JSON_COLUMN = ???? Not sure what to keep here where id = 1;

Tags:

2

Answers


  1. 0

    mysql and mariadb haven’t the datatype varchar2 but oracle has.

    Using a simple REPLACE can help you.

    IF the term NYC is only one in your json, this is enough

    CREATE TABLE mytable (ID  int  Auto_increment primary key,

JSON_COLUMN  Varchar(1000))

    INSERT INTO mytable VALUES (NULL,'[
   {
      "Name":"Number",
      "Id":"PhoneNumber",
      "Value":"+393123456789"
   },
   {
      "Name":"Name",
      "Id":"FirstName",
      "Value":"John"
   },
   {
      "Name":"City",
      "Id":"CityID",
      "Value":"NYC"
   }
]')

    UPDATE mytable SET JSON_COLUMN = REPLACE(JSON_COLUMN,'NYC','CALI') WHERE ID = 1

    Rows matched: 1  Changed: 1  Warnings: 0

    SELECT * FROM mytable
    ID JSON_COLUMN
    1 [
       {
          "Name":"Number",
          "Id":"PhoneNumber",
          "Value":"+393123456789"
       },
       {
          "Name":"Name",
          "Id":"FirstName",
          "Value":"John"
       },
       {
          "Name":"City",
          "Id":"CityID",
          "Value":"CALI"
       }
    ]

    fiddle

    Login or Signup to reply.
  2. 0

    Here’s a solution that updates only the ‘NYC’ corresponding to the key ‘Value’ in an array.

    with cte as (
  select ID, j.* from tablename
  cross join json_table(JSON_COLUMN, '$[*]' columns(
    ord for ordinality, 
    Value varchar(100) path '$.Value')
  ) as j 
  where j.Value = 'NYC'
) 
update tablename cross join cte
set tablename.json_column = json_set(json_column, concat('$[', cte.ord-1, '].Value'), 'CALI')
where cte.id = tablename.id;

    This works in MySQL 8.0 or later, but not in MariaDB, because MariaDB doesn’t support a common table expression before update.

    Demo: https://dbfiddle.uk/tN9Q9-Od

    Here’s a solution for MariaDB 10.6 or later (won’t work with older versions):

    update tablename
cross join (
  select ID, j.* from tablename
  cross join json_table(JSON_COLUMN, '$[*]' columns(
    ord for ordinality,
    Value varchar(100) path '$.Value')
  ) as j
  where j.Value = 'NYC'
) as t
set tablename.json_column = json_set(json_column, concat('$[', t.ord-1, '].Value'), 'CALI');

    Demo: https://dbfiddle.uk/fKHp8BmZ

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

Back To Top
Search