Get distinct values from MySQL JSON array

Question

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?

moral of the story: it's always a bad idea to store comma seperated data in a column — e4c5
– e4c5, Commented Sep 15, 2016 at 10:15
For that you have to first normalize your data. Do you have a table containing constraint values normalized? — sagi
– sagi, Commented Sep 15, 2016 at 10:15

Δ O · Accepted Answer · 2020-06-23 13:48:31Z

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

See https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table

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

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

Collectives™ on Stack Overflow

Get distinct values from MySQL JSON array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related