How to update a part of a json value

Question

I have the following data and MySQL table :

CREATE TABLE my_tbl(
  id INT,
  dataset_query longtext
);
INSERT INTO my_tbl(id, dataset_query) VALUES (1, '{"database":1,"native":{"query":"SELECT * FROM view_1.device","template-tags":{}},"type":"native"}');
INSERT INTO my_tbl(id, dataset_query) VALUES (2, '{"database":1,"native":{"query":"SELECT id, name FROM view_1.request","template-tags":{}},"type":"native"}');
INSERT INTO my_tbl(id, dataset_query) VALUES (3, '{"database":3,"native":{"query":"SELECT id, name, age FROM view_3.person","template-tags":{}},"type":"native"}');

I need to change the following data in the dataset_query column:

• From "database":1 to "database":2
• Replace view_1 with view_2

To update the database ID, I use the following SQL statement:

UPDATE
    my_tbl
SET
    dataset_query = JSON_SET(dataset_query, "$.database", 2) 
WHERE 
    json_extract(dataset_query, '$.database') = 1;

How can I update the dataset_query column in the my_tbl table to replace all occurrences of view_1 with view_2?

The expected result is as follows:

id	dataset_query
1	{"database":2,"native":{"query":"SELECT * FROM view_2.device","template-tags":{}},"type":"native"}
2	{"database":2,"native":{"query":"SELECT id, name FROM view_2.request","template-tags":{}},"type":"native"}
3	{"database":3,"native":{"query":"SELECT id, name, age FROM view_3.person","template-tags":{}},"type":"native"}

Db fiddle : https://www.db-fiddle.com/f/nw7sEBcF2i8eioWQawqdmD/0

Answer 1

Use the REPLACE() function. Use the path $.native.query to get the nested object property.

UPDATE my_tbl
SET dataset_query = JSON_REPLACE(
        dataset_query, 
        '$.native.query',
        REPLACE(dataset_query->>'$.native.query', 'view_1', 'view_2'))
WHERE dataset_query->>'$.native.query' LIKE '%view_1%';

Answer 2

update my_tbl
set dataset_query = JSON_SET(
  dataset_query, '$.native.query',
  REPLACE(
    JSON_UNQUOTE(JSON_EXTRACT(dataset_query, '$.native.query')),
    'view_1', 'view_2'
  )
);

Db fiddle: https://www.db-fiddle.com/f/t3jyZcYR33X9wHZPQ6sahf/1