Mysql - How can I merge two json arrays of strings without duplicates?

Question

If I have two json arrays of strings in mysql, is there a native(or not native) way to merge these two arrays into one with unique strings? If I try json_merge I get the following result with duplicates:

set @array1 =JSON_EXTRACT('["apple","pear","banana"]', '$');
set @array2 =JSON_EXTRACT('["pear","banana","apple","kiwi"]', '$');
select json_merge(@array1,@array2);
    > ["apple", "pear", "banana", "pear", "banana", "apple", "kiwi"]

And If is try json_merge_preserve gives me the same result:

set @array1 =JSON_EXTRACT('["apple","pear","banana"]', '$');
set @array2 =JSON_EXTRACT('["pear","banana","apple","kiwi"]', '$');
select json_merge_preserve(@array1,@array2);
    > ["apple", "pear", "banana", "pear", "banana", "apple", "kiwi"]

Is there a function that will return the unique array?

["apple",  "banana", "pear", "kiwi"]

Edit: json_merge_patch doesn't work because it only replaces the first array with the second:

set @array1 =JSON_EXTRACT('["apple","grape","banana"]', '$');
set @array2 =JSON_EXTRACT('["pear","banana","apple","kiwi"]', '$');
select json_merge_patch(@array1,@array2);
 > ["pear", "banana", "apple", "kiwi"]

In this case I lose "grape". I believe that the logic in patch is 0 : 'val', 1:'val2' merge with 0:val3 then 0 : 'val3', 1:'val2'

Answer 1

If the question still lives, here's a simple solution using MySQL 8.0's JSON_TABLE.

set @a1 ='["apple","grape","banana","banana","pear"]';
set @a2 ='["pear","banana","apple","kiwi","banana","apple"]';

select fruit
from json_table(
  json_merge_preserve(@a1, @a2),
  '$[*]' columns (
    fruit varchar(255) path '$'
  )
) as fruits
group by fruit; # get distinct values

# gives
apple
grape
banana
pear
kiwi

To get a one-line response, we have to drop group by and get a bit more creative.

Unfortunately, JSON_ARRAYAGG doesn't support distinct directive, so we'll have to use GROUP_CONCAT:

select group_concat(distinct fruit)
from json_table(
  json_merge_preserve(@a1, @a2),
  '$[*]' columns (
    fruit varchar(255) path '$'
  )
) as fruits;
# without group by directive!

# gives: apple,banana,grape,kiwi,peas

To get a proper json array on-line response, we just play around with CONCATs:

select cast(
  concat('["', group_concat(distinct fruit separator '", "'), '"]')
  as json)
...

# gives: ["apple", "banana", "grape", "kiwi", "pear"]

---

EDIT:

I've found out a proper JSON_ARRAYAGG solution using one more nested virtual table to group results in.

select json_arrayagg(fruit)
from (
  select fruit
  from json_table(
    json_merge_preserve(@a1, @a2),
    '$[*]' columns (
      fruit varchar(255) path '$'
    )
  ) as fruits
  group by fruit -- group here!
) as unique_fruits;

---

Read my Best Practices for using MySQL as JSON storage :)