Modify column data stored as JSON

Question

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;

Answer 1

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

If you know the index you can use JSON_REPLACE

UPDATE mytable 
  SET JSON_COLUMN = JSON_REPLACE(JSON_COLUMN,'$[2].Value','CALI') WHERE ID = 1

Rows matched: 1  Changed: 1  Warnings: 0

SELECT JSON_EXTRACT(JSON_COLUMN,'$[2].Value') FROM mytable

JSON_EXTRACT(JSON_COLUMN,'$[2].Value')
"CALI"

fiddle

Answer 2

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