Mysql - 如何合并两个 json 字符串数组而不重复？

Question

如果我在 mysql 中有两个 json 字符串数组，是否有 native (或非 native )方法将这两个数组合并为一个具有唯一字符串的数组？
如果我尝试 json_merge我得到以下重复结果:

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

如果是尝试 json_merge_preserve给我同样的结果:

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

是否有返回唯一数组的函数？

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

编辑: json_merge_patch不起作用，因为它只用第二个数组替换第一个数组:

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

在这种情况下，我输了 "grape" .我相信补丁中的逻辑是 0 : 'val', 1:'val2'与 0:val3 合并然后 0 : 'val3', 1:'val2'

Answer 1

如果问题仍然存在，这里有一个简单的解决方案，使用 MySQL 8.0 的 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

要获得单行响应，我们必须删除 group by并获得更多创意。
不幸的是， JSON_ARRAYAGG不支持 distinct指令，所以我们必须使用 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

为了得到一个正确的 json 数组在线响应，我们只是玩弄 CONCATs :

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

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

编辑:
我找到了一个合适的 JSON_ARRAYAGG使用多一个嵌套虚拟表的解决方案 group结果是。

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;

阅读我的 Best Practices for using MySQL as JSON storage :)