CODEWITHSUNDEEP
mysqljson
Double_O_Seven
Double_O_Seven

Reputation: 97

How to remove multiple values FROM MySQL JSON

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

Upvotes: 2

Views: 2023

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

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

Upvotes: 1

Related Questions