如何在MySQL 5.7的JSON数组中获取唯一/不同的元素 [英] How to get unique / distinct elements inside JSON array in MySQL 5.7

问题描述

我有带有未规范化表的mysql 5.7，该表具有一些JSON列.我需要为数组列每行提取唯一/不同的值.

I have mysql 5.7 with a denormalized table that has some JSON columns. I need to extract unique / distinct values per row for an array column.

例如:["a", "b", "b", "a", "c"]预期输出应为["a", "b", "c"];

SET @json = '["a", "b", "b", "a", "c"]'; 我需要在此列表中获得唯一的值.

SET @json = '["a", "b", "b", "a", "c"]'; I need to get unique values in this list.

["a", "b", "c"];

推荐答案

在MySQL中，没有直接的方法可以从JSON数组中获取不同的值.一种方法是使用序列/数字生成器表概念.该序列表可以用作派生表(子查询)，也可以创建一个永久表来存储数据库中的数字.

There is no direct method to get distinct values out of a JSON array in MySQL. One method could be to utilize a Sequence/Number Generator table concept. This sequence table could be used as a Derived Table (subquery), or you can create a permanent table storing numbers in your database.

然后，我们将使用此序列表来 JSON_ARRAYAGG() 函数将这些唯一值重新聚合为JSON数组.

We will then use this sequence table to JSON_EXTRACT() values out from array at first key, second key, third key and so on. Once we have extracted out the values in separate row, we can simply use DISTINCT to get unique values out of them. Afterwards, we can use JSON_ARRAYAGG() function to re-aggregate these unique values back as a JSON array.

模式(MySQL v5.7)

SET @json = '["a", "b", "b", "a", "c"]';

查询

SELECT Json_arrayagg(dt.val) AS unq_json_array
FROM   (SELECT DISTINCT Json_extract(@json, Concat('$[', seq.n, ']')) AS val
        FROM   (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL
                SELECT 2 UNION ALL SELECT 3 UNION ALL 
                SELECT 4 UNION ALL SELECT 5 UNION ALL
                SELECT 6 UNION ALL SELECT 7 UNION ALL
                SELECT 8 UNION ALL SELECT 9) AS seq) AS dt
WHERE  dt.val IS NOT NULL;

结果

| unq_json_array  |
| --------------- |
| ["a", "b", "c"] |

---

在DB Fiddle上查看

这篇关于如何在MySQL 5.7的JSON数组中获取唯一/不同的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！