MySQL从跨行的任意长字符串数组中获取不同的值

Question

我有一个包含 JSON 列的表，我们称它为 json_data 并且列内容看起来像...

[{ "data": { ... }, "name": "name_1" }, { "data": { ... }, "name": "name_2" }]
[{ "data": { ... }, "name": "name_2" }]
[{ "data": { ... }, "name": "name_3" }, { "data": { ... }, "name": "name_5" }]
[{ "data": { ... }, "name": "name_4" }]

...我期待着回来

["name_1", "name_2", "name_3", "name_4", "name_5"]

或类似的东西。我可以使用 JSON_EXTRACT 轻松获取每一行的名称字段集...

SELECT JSON_EXTRACT(json_data, "$**.name") FROM my_table;

...所以现在我的每一行都包含一个逗号分隔字符串数组，并且可以使用 GROUP_CONCAT 来合并它们...

SELECT REPLACE(REPLACE(GROUP_CONCAT(names SEPARATOR ','), '[', ''), ']', '')
FROM (
    SELECT JSON_EXTRACT(json_data, '$**.name') as names 
    FROM my_table 
    WHERE json_data <> '' -- exclude empty entries
    LIMIT 10) x -- test on sample size as the table is quite large
ORDER BY NULL; -- get names from all rows

...此时我将我想要的所有数据作为逗号分隔的字符串放在一行中...

"name_1","name_2","name_2","name_3","name_4","name_5"

除了它有重复项(其中很多)。

看起来应该很容易接受它并在其上运行 distinct，但到目前为止我无法弄清楚如何将字符串拆分为所有元素并执行 distinct。 SUBSTRING_INDEX 似乎是我所需要的，但它只能获取单个元素......感谢任何帮助!

Answer 1

在现代版本的 MySQL (>= 8.0.4) 中，查询会相对简单:

SELECT
  GROUP_CONCAT(
    DISTINCT JSON_QUOTE(`der`.`names`)
  ) `names`
FROM
  `my_table`,
  JSON_TABLE(`my_table`.`json_data`,
    '$[*]' COLUMNS(
      `names` VARCHAR(10) PATH '$.name'
    )
  ) `der`
ORDER BY
  `names`;

参见 db-fiddle .

然而，在旧版本中，它并不是那么简单，一种选择可能是使用临时表和准备好的语句:

SET @`ddl` := CONCAT('INSERT INTO `my_table` VALUES ',
  (SELECT
    GROUP_CONCAT(
      REPLACE(
        REPLACE(
          REPLACE(
            `json_data` -> '$**.name',
          '[', '('),
        ']', ')'),
      ',', '),(')
    )
  FROM
    `my_table`
  )
);

参见 Rextester .

无论如何，记住5.1.7 Server System Variables::group_concat_max_len .