Question 275603 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to update a part of a json value

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

Tags:

2

Answers


  1. 0 
    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

    Login or Signup to reply.
  2. 0

    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%';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search