How to remove array elements from another array in mysql using JSON functions?

set @a = '["1","2","3"]';
set @b = '["4","2","3"]';

I have two json arrays.
Now I want to remove all the elements from @b from @a if it exists. else ignore.

Expected result:

["1"]

We can loop through @b and remove one by one from @a.

But I am looking for a best solution.
I am expection somthing like JSON_REMOVE_ALL();

Please add your thoughts…

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The following uses JSON functions to extract elements in @a that are not in @b.

  • JSON_TABLE is used to convert @a into a table before
  • JSON_CONTAINS is used to check whether the element in @a exists in @b.
  • Finally JSON_ARRAYAGG aggregates the results into a single json array value

Schema (MySQL v8.0)


Query #1

set @a = cast('["1","2","3"]' as json);

There are no results to be displayed.


Query #2

set @b = cast('["4","2","3"]' as json);

There are no results to be displayed.


Query #3

SELECT
    JSON_ARRAYAGG(num) result 
FROM
    JSON_TABLE(@a,"$[*]" COLUMNS(num json PATH "$" )) ta
WHERE JSON_CONTAINS(@b,num,'$')=0;
result
[“1”]

View on DB Fiddle


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x