1

I have table like this my_table

id    my_json
1     ['1','2','3']
2     ['2']
3     ['2','3']
...
12000 ....

I want to find all distinct values in json's arrays like this result '1' '2' '3'

I have this code but i need split values to rows

set @items = (SELECT 
     GROUP_CONCAT(
        REPLACE(REPLACE(lower(my_json), ']', ''), '[', '')
        SEPARATOR ','
    )
FROM my_table);
SELECT CONCAT ('[',@items,']') AS jarray;

result is
[1,2,3]

probably somebody have ideas?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Alexander Vedmed'
  • 154
  • 1
  • 16

1 Answers1

0

You can use json_table:

select json_arrayagg(t2.v) from (select distinct t1.v from tbl t 
  cross join json_table(t.my_json, '$[*]' columns( v int path '$')) t1) t2

See fiddle.

Ajax1234
  • 69,937
  • 8
  • 61
  • 102