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

z18hc3ub 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(311)

我有一个带有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 似乎是我需要的，但那只得到个别元素。。。感谢您的帮助！

mysql JSON Arrays mysql-5.7

来源：https://stackoverflow.com/questions/51772547/mysql-get-distinct-values-from-arbitrarily-long-arrays-of-strings-across-rows

1条答案

按热度按时间

yh2wf1be1#

在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小提琴。
但是，在旧版本中，并不是那么简单，一种选择可能是使用临时表和准备好的语句：

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

请参阅测试仪。
在任何情况下，请记住5.1.7服务器系统变量：：group\u concat\u max\u len。

赞(0）回复(0）举报 2021-06-23

我来回答

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

1条答案

相关问题

热门标签

最新问答