从MySQL JSON数组中获取唯一值

Question

我有一个MySQL数据表，其中包含一个JSON列，其中包含一个值列表：

约束表

 ID | CONSTRAINT_TYPE | CONSTRAINT_VALUES
----+-----------------+--------------------------------
 '2'| 'testtype'      |'[801, 751, 603, 753, 803]'
 ...| ...             | ...

我想要的是一个清晰的、逗号分隔的 JSON 值列表。我尝试使用 group_concat，但它只适用于数组，而不是单个值。

SELECT group_concat(distinct constraint_values->>'$') 
FROM constraint_table c 
WHERE c.constraint_type = "testtype";

实际结果：

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

我的目标结果：

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

没有重复项。如果有行就更好了。

大家有什么想法吗？

- Boerger

---

故事寓意：在列中存储逗号分隔的数据始终是一个坏主意。 - e4c5

为此，您必须首先对数据进行规范化。您是否有包含约束值规范化的表？ - sagi

Answer 1

很抱歉打扰了，但我也遇到了类似的问题。解决方案是：自 MySQL 8.0 开始提供 JSON_TABLE()。

首先，将数组在行中合并为一行单个数组。

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]

其次，使用json_table将数组转换为行。

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

请参考https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table，在使用group by val获取不同的值时，请注意。您还可以对它们进行order等操作...

或者您可以使用group_concat(distinct val)而不需要使用group by指令(!)来获得一行结果。

甚至可以使用cast(concat('[', group_concat(distinct val), ']') as json)来获取一个合适的json数组：[15, 66, 578, 603, 751, 753, 801, 803]。

---

请阅读我的MySQL作为JSON存储的最佳实践 :)

- ΔO 'delta zero'