How to remove an entire entry of a JSON array in mysql?
Is it possible to remove an entire entry of a JSON array in mysql?
For example, I have an array like:
[{"date_begin": "2022-01-01 14:30:00","date_end": "2022-01-01 20:00:00"},
{"date_begin": "2022-01-01 20:00:00","date_end": "2022-01-01 22:00:00"}]
I want to pass a date like:
{"date_begin": "2022-01-01 20:00:00","date_end": "2022-01-01 22:00:00"}
And I want to get a final array without that date, so in this case the result should be:
[{"date_begin": "2022-01-01 14:30:00","date_end": "2022-01-01 20:00:00"}]
Solution 1:
Use JSON_TABLE and JSON functions to filter the values
mysql> set @json = '[{"date_begin": "2022-01-01 14:30:00","date_end": "2022-01-01 20:00:00"}, {"date_begin": "2022-01-01 20:00:00","date_end": "2022-01-01 22:00:00"}]';
Query OK, 0 rows affected (0.00 sec)
mysql> set @input = '{"date_begin": "2022-01-01 20:00:00","date_end": "2022-01-01 22:00:00"}';
Query OK, 0 rows affected (0.00 sec)
mysql> select json_arrayagg(j1) from json_table(@json, '$[*]' columns ( j1 json path '$')) as jt where json_extract(j1, '$.date_end') <> json_extract(@input, '$.date_end') and json_extract(j1, '$.date_begin') <> json_extract(@input, '$.date_begin');
+----------------------------------------------------------------------------+
| json_arrayagg(j1) |
+----------------------------------------------------------------------------+
| [{"date_end": "2022-01-01 20:00:00", "date_begin": "2022-01-01 14:30:00"}] |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)