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

Question

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.

Is there somthing like JSON_REMOVE_ALL();

ggordon · Accepted Answer · 2021-07-03 06:49:25Z

1

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

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"]

answered Jul 3, 2021 at 6:49

ggordon

10.1k2 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I had to replace the last line with WHERE JSON_CONTAINS(@b,JSON_ARRAY(num),'$')=0; in 8.0.40