mysql遍历json数组,从MySQL JSON数组获取不同的值

I got a MySQL data Table, with a JSON Column containing a list of Values:

CONSTRAINT_TABLE

ID | CONSTRAINT_TYPE | CONSTRAINT_VALUES

----+-----------------+--------------------------------

'2'| 'testtype' |'[801, 751, 603, 753, 803]'

...| ... | ...

What I want to have is a distinct, comma-seperated List of JSON-Values. I tried it with group_concat, but it applys to the arrays, not the single values.

SELECT group_concat(distinct constraint_values->>'$')

FROM constraint_table c

WHERE c.constraint_type = "testtype";

Actual result:

[801, 751, 603, 753, 803],[801, 751],[578, 66, 15],...

My target result:

801, 751, 603, 753, 803, 578, 66, 15 ...

without duplicates. As rows would be nice, too.

Ideas, anyone?

解决方案

Sorry for necromancing, but I've encountered similar issue. The solution is: JSON_TABLE() available since MySQL 8.0.

First, merge the arrays in rows into one-row single array.

select concat('[', -- start wrapping single array with opening bracket

replace(

replace(

group_concat(vals), -- group_concat arrays from rows

']', ''), -- remove their opening brackets

'[', ''), -- remove their closing brackets

']') as json -- finish wraping single array with closing bracket

from (

select '[801, 751, 603, 753, 803]' as vals

union select '[801, 751]'

union select '[578, 66, 15]'

) as jsons;

# gives: [801, 751, 603, 753, 803, 801, 751, 578, 66, 15]

Second, use json_table to convert the array into rows.

select val

from (

select concat('[',

replace(

replace(

group_concat(vals),

']', ''),

'[', ''),

']') as json

from (

select '[801, 751, 603, 753, 803]' as vals

union select '[801, 751]'

union select '[578, 66, 15]'

) as jsons

) as merged

join json_table(

merged.json,

'$[*]' columns (val int path '$')

) as jt

group by val;

# gives...

801

751

603

753

803

578

66

15

Notice group by val for getting distinct values. You can also order them and everything...

Or you can use group_concat(distinct val) without the group by directive (!) to get one-line result.

Or even cast(concat('[', group_concat(distinct val), ']') as json) to get a proper json array: [15, 66, 578, 603, 751, 753, 801, 803].