How to get unique / distinct elements inside JSON array in MySQL 5.7

Question

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.

For e.g: ["a", "b", "b", "a", "c"] expected output should be ["a", "b", "c"];

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

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

Answer 1

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.

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.

Schema (MySQL v5.7)

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

Query

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;

Result

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

---

View on DB Fiddle