Блог

Мне нужно удалить несколько значений из массива JSON в MySQL

Я пробовал JSON_REMOVE с помощью JSON_SEARCH для каждого значения, но после удаления элемента ИНДЕКС изменяется

Массив JSON:

 ["1", "5", "18", "68"]
 

например: удалите «5» и «68».

ожидаемый результат :

 ["1", "18"]
 

ОТРЕДАКТИРОВАННЫЙ:

код, который я пробовал:

 JSON_REMOVE(`can_see`, JSON_UNQUOTE(JSON_SEARCH(`can_see`, 'one', '5')), JSON_UNQUOTE(JSON_SEARCH(`can_see`, 'one', '68')))
 

результат запроса:

 ["1", "18", "68"]
 

после удаления «5» в индексе $ [0] индекс «68» изменился на $ [2], но JSON_SEARCH возвращает $ [3] из исходного json

Я тоже попробовал вложенный :

 JSON_REMOVE(JSON_REMOVE(`can_see`, JSON_UNQUOTE(JSON_SEARCH(`can_see`, 'one', '5'))), JSON_UNQUOTE(JSON_SEARCH(JSON_REMOVE(`can_see`, JSON_UNQUOTE(JSON_SEARCH(`can_see`, 'one', '5'))), 'one', '68')))
 

это работает, но это становится беспорядочным, если я хочу удалить более 2 значений

мне нужно быть уверенным, что массив отсортирован

Не самый простой и понятный способ, но я думаю, вам придется использовать генератор чисел SQL для разбора массива json в виде токенов (записей), которые вы можете фильтровать и упорядочивать более простым способом.

Запрос

 SELECT 
  JSON_ARRAYAGG(
     JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']'))
  ) AS json                                    
FROM (

  SELECT 
   @row := @row   1 AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION   SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION  SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2
  CROSS JOIN (
    SELECT @row := -1 
  ) init_user_params 
) AS number_generator
CROSS JOIN (


SELECT 
    json
  , JSON_LENGTH(records.json) AS json_array_length    
FROM (

  SELECT 
   '["1", "5", "18", "68"]' AS json
  FROM 
   DUAL  
) AS records

) AS records 
WHERE
    number BETWEEN 0 AND  json_array_length - 1   
  AND
    JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']')) NOT IN(5, 68)                   
ORDER BY 
 REPLACE(JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']')), '"', '')                       
 

Результат

 | json        |
| ----------- |
| ["1", "18"] |
 

смотрите демонстрацию

MySQL 8 , с другой стороны, делает это намного проще

Запрос

 SELECT 
 JSON_ARRAYAGG (
    result_table.item
 ) AS json
FROM JSON_TABLE(
     '["1", "5", "18", "68"]'
   , "$[*]"

   COLUMNS (
       rowid FOR ORDINALITY
     , item VARCHAR(100) PATH "$"   
   )
) AS result_table
WHERE
 CAST(result_table.item AS UNSIGNED) NOT IN(5, 68) 
ORDER BY 
 CAST(result_table.item AS UNSIGNED) ASC
 

Результат

 | json        |
| ----------- |
| ["1", "18"] |
 

смотрите демонстрацию