How to use JSON_REMOVE with several rows from a subquery?

Question

I have a table with json-column in the column data and a table with keys to delete from the first table. MySQL 5.7.

id	data
1	{"key1": "value1", "key2": "value2", "key10": "value10", "key100": "value100"}
20	{"key1": "value1", "key18": "value18", "key150": "value150"}
57	{"key5": "value5", "key10": "value10"}

id	key	value
1	key1	value1
18	key2	value2
30	key150	value150

I want to delete all elements with keys from the second table. I tried JSON_ARRAYAGG() but JSON_REMOVE() requires the "path[, path]"-structure with keys according to docs.

UPDATE `table1` as `t1`
SET `t1`.`data` =  JSON_REMOVE(`t1`.`data`, (
    SELECT JSON_ARRAYAGG(`key`)
    FROM `table2` as `t2`
    )
);

I haven't found examples or docs about json paths from subquery rows. How can I transform rows or maybe array values from returned rows to correct paths?

A result I expect after the query:

id	data
1	{"key10": "value10", "key100": "value100"}
20	{"key18": "value18"}
57	{"key5": "value5", "key10": "value10"}

score 0 · Answer 1 · answered Aug 30 '21 at 09:13

You could prepare a query to be executed. The query could be dynamically created.

In this case you'll need to create this query to update the table (I call it json_stuff):

UPDATE json_stuff
   SET jsons = JSON_REMOVE(jsons, '$.key1','$.key2','$.key150')

Which can be dynamically created and put into a variable using:

SELECT CONCAT("UPDATE json_stuff SET jsons = JSON_REMOVE(jsons, ",
              GROUP_CONCAT(CONCAT('\'$.',keyname,'\'')),
              ")")
  INTO @sql_delete_string
  FROM delete_stuff

..where delete_stuff is your table containing the keys to be removed.

You can then execute it using this procedure with call execute_immediate(@sql_delete_string):

CREATE PROCEDURE execute_immediate(IN query MEDIUMTEXT)
    MODIFIES SQL DATA
    SQL SECURITY DEFINER
BEGIN
    SET @q = query;
    PREPARE stmt FROM @q;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

Note: this procedure has been taken from EXECUTE IMMEDIATE MySQL

dbfiddle

How to use JSON_REMOVE with several rows from a subquery?

1 Answers1