Here就是一个例子。我们的想法是从list数组中拆分值，并使用REGEXP检查匹配。然后构建只包含匹配元素的新list数组。之后，更新原始表。

CREATE TABLE DataSource 
( 
    id INT AUTO_INCREMENT PRIMARY KEY
   ,RawData JSON
);

INSERT INTO DataSource (RawData) VALUES 
    ('{"name": "1", "list": ["abc", "xyz"]}'),
    ('{"name": "2", "list": ["b", "bc", "bcd"]}'),
    ('{"name": "3", "list": ["cd", "cdef", "def", "defg", "ef"]}');

SET @keepList = '["a","bc","def"]';
SET @keepList = REPLACE(REPLACE(REPLACE(REPLACE(@keepList, '[', ''), ']', ''), ',', '|'), '"', '');

WITH NewDataSource (ID, name, value) AS
(
      SELECT DS.ID
            ,JSON_EXTRACT(DS.RawData, '$.name')
            ,JSON_ARRAYAGG(j.value)
      FROM DataSource DS,
      JSON_TABLE(DS.RawData, '$.list[*]' COLUMNS (value VARCHAR(255) PATH '$')) AS j
      WHERE j.value REGEXP  @keepList
      GROUP BY DS.ID
              ,JSON_EXTRACT(DS.RawData, '$.name')
 ) 
UPDATE DataSource T
INNER JOIN NewDataSource S
    ON T.id = S.id
SET T.RawData = JSON_OBJECT
               (
                  'name', S.name
                 ,'list', S.value
               );
               
SELECT *
FROM DataSource;