是否可以在mysql中洗掉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"}]
我想通過一個日期，如：
{"date_begin": "2022-01-01 20:00:00","date_end": "2022-01-01 22:00:00"}
我想得到一個沒有那個date的最終陣列，所以在這種情況下，結果應該是：
[{"date_begin": "2022-01-01 14:30:00","date_end": "2022-01-01 20:00:00"}]
uj5u.com熱心網友回復：
使用 JSON_TABLE 和 JSON 函式過濾值
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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/419293.html
標籤：