How to remove multiple values FROM MySQL JSON

Question

I need to remove multiple values from JSON Array in MySQL

I've tried JSON_REMOVE with JSON_SEARCH on each value but after removing element the INDEX changes

JSON Array:

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

for example : remove "5" and "68"

expected result :

["1", "18"]

EDITED:

code that I've tried:

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

query result:

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

after removing "5" at index $[0], "68" index changed to $[2] but the JSON_SEARCH return $[3] from the original json

I also tried nested :

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')))

that works but it gets messy if i want to remove more than 2 values

Can you show us what you have tried. It will help to provide context to your question. And what INDEX changes? — RiggsFolly, Apr 18 '19 at 10:43
Silly answer is of course to do 68 first and 5 second then the index would no be an issue — RiggsFolly, Apr 18 '19 at 10:55
*"i need to be sure that the array is sorted "* i posted a answer does that help? — Raymond Nijland, Apr 18 '19 at 11:19

Raymond Nijland · Answer 1 · 2019-04-18T11:30:18.880

i need to be sure that the array is sorted

Not the most easy understandable way but i think you would have to use a SQL number generator to parse out the json array as tokens (records) which you can filter and order by more easy.

Query

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 , ']')), '"', '')

Result

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

see demo

MySQL 8+ on the other hand makes it much more easy

Query

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

Result

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

see demo

Thank you very much for answering i'll be honest, this is a bit much for me to understand and manipulate for my purposes. this is a part from a bigger UPDATE query that SET multiple columns The MySQL version is 5.6 — Double_O_Seven, Apr 18 '19 at 11:46
Yes the problem is that making a JSON parser in MySQL 5.6 is much harder i advice you to upgrade your MySQL version. Because MySQL 5.6 didn't have that much JSON support. @Double_O_Seven — Raymond Nijland, Apr 18 '19 at 12:20

How to remove multiple values FROM MySQL JSON

1 Answers1

Linked