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

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_arrayFROM   (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 dtWHERE  dt.val IS NOT NULL;

Result

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

View on DB Fiddle

The following may help those using MySQL 8

You could use a combination of JSON_TABLE, JSON_OBJECTAGG, and JSON_KEYS to do it without too much trouble. It's too bad we couldn't do something like JSON_ARRAYAGG(DISTINCT item).

For example:

SET @json = '["a", "b", "b", "a", "c"]';SELECT JSON_KEYS(JSON_OBJECTAGG(item, ""))FROM JSON_TABLE(@json, '$[*]'    COLUMNS(        item TEXT PATH '$'    )) as items;

I also made a DBFiddle to show my work: https://www.db-fiddle.com/f/6hgSEXPJWcm2CH8K16z6gQ/0

JSON_TABLE https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

JSON Search Functions: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html