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)