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'

Δ O · Accepted Answer · 2020-06-23 14:12:39Z

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 :)

Bruno Medina · Accepted Answer · 2019-08-16 21:41:43Z

1

After too much thinking, and thanks to @lefred. I found a hack that can accomplish this. This is way too hacky, but i will publish it while someone else comes with a better implementation or the mysql guys make a proper function for this.

First, we replace the string strategically to create a json object instead of an array. Then, we use json_merge_path and finally we use json_keys to obtain an array :V

set @array1 ='["apple","grape","banana","banana","pear"]';
set @array2 ='["pear","banana","apple","kiwi","banana","apple"]';

set @aux1 = REPLACE(REPLACE(REPLACE(@array1, ',', ' : "1", '), ']', ' : "1" }'), '[', '{');
set @aux2 = REPLACE(REPLACE(REPLACE(@array2, ',', ' : "1", '), ']', ' : "1" }'), '[', '{');
select @aux1, @aux2;
select json_keys(json_merge_patch(json_extract(@aux1, '$'),json_extract(@aux2,'$')))

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

answered Aug 16, 2019 at 21:41

Bruno Medina

6816 silver badges22 bronze badges

1 Comment

Δ O Over a year ago

This works, but it's rather ugly. Please, see my answer based on JSON_TABLE.

Liam · Accepted Answer · 2021-02-11 10:50:13Z

1

Use SELECT DISTINCT:

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

select json_arrayagg(fruit) from (
    select 
        distinct fruit 
    from json_table(
        json_merge_preserve(@array1, @array2), 
        '$[*]' columns (fruit varchar(255) path '$')
    ) fruits
) f;

edited Feb 11, 2021 at 10:50

answered Feb 9, 2021 at 9:53

Liam

5,5463 gold badges33 silver badges42 bronze badges

1 Comment

Bruno Medina Over a year ago

Yeah, the distinct would work as well. you are missing the json_arrayagg to return the json array, like this:

select json_arrayagg(fruit) from (select      distinct fruit  from json_table(     json_merge_preserve(@array1, @array2),      '$[*]' columns (fruit varchar(255) path '$') ) fruits) f;

Sorix · Accepted Answer · 2019-08-16 18:23:18Z

0

According to the documentation, json_merge_preserve preserves duplicates. Also, if you are using MySQL 8.0.3 or over, json_merge is deprecated and json_merge_preserve should be used. I think that you need to use JSON_MERGE_PATCH.

More details here https://database.guide/json_merge_preserve-merge-multiple-json-documents-in-mysql/

edited Aug 16, 2019 at 18:23

answered Aug 16, 2019 at 18:19

Sorix

9486 silver badges19 bronze badges

5 Comments

Bruno Medina Over a year ago

No, that only replaces the first array with the second

Sorix Over a year ago

Yeah, I see that now... did you have a look at this stackoverflow.com/questions/39508478/… ? The question does not have an answer, but the question itself might help you.

Bruno Medina Over a year ago

So, are you suggesting is a bad idea to store json string arrays in MySQL?

Sorix Over a year ago

Is there a specific need for storing data that way? It is usually hard to deal with data that has been stored this way. And in my experience, data stored like that is not intended to be used in operations or filters.

Bruno Medina Over a year ago

XDevApi now supports collections. The fact MySQL supports that, ask for tools to manage json in more robust way.

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

1 Comment

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related