Can I get unique/distinct values of json column in MySQL?

Question

It's a little bit hard to explain so I will explain by giving example,

Let say I have table like this (tags is json column)

+----+-------------------+--------------------+
| id |    occupation     |        tags        |
+----+-------------------+--------------------+
|  1 | Mold Maker        | [Men, Shop, Shoes] |
|  2 | Software Engineer | [Men, Lifestyle]   |
|  3 | Electrician       | [Shop, Lifestyle]  |
|  4 | Software Engineer | [Men, Lifestyle]   |
|  5 | Software Engineer | [Shoes]            |
+----+-------------------+--------------------+

When I want to get unique value of occupation I simply just query like this.

SELECT DISTINCT occupation FROM customers;
OR
SELECT occupation FROM customers GROUP BY occupation;

result
+-------------------+
|    occupation     |
+-------------------+
| Mold Maker        |
| Software Engineer |
| Electrician       |
+-------------------+

I want unique values of tags by rows like below

+-----------+
|   tags    |
+-----------+
| Men       |
| Shop      |
| Shoes     |
| Lifestyle |
+-----------+

so far I try to read all JSON_* funcions and JSON_TABLE in MySQL manual and google, but can't find a way to do that, is there anyway around to get the result I want.

Answer 1

In MySQL 8.0, json function json_table() comes handy for this task:

select distinct tag
from 
    mytable,
    json_table(
        tags,
        "$[*]"
        columns (tag varchar(50) PATH "$")
    ) t
order by tag

In earlier versions, a solution is to use a number table. This supposes that you know in advance the maximum number of elements in your json array:

select distinct replace(
    json_extract(tags, concat('$[', nums.n, ']')),
    '"',
    ''
) tag
from 
    (
        select 0 n 
        union all select 1 
        union all select 2 
        union all select 3 
        union all select 4
    ) nums
    inner join mytable t
        on json_extract(t.tags, concat('$[', nums.n, ']')) is not null 
    order by tag

Demo on DB Fiddle

| tag       |
| :-------- |
| Lifestyle |
| Men       |
| Shoes     |
| Shop      |

Answer 2

You can use JSON_TABLE() in MySQL 8+:

select DISTINCT tag.tag
from t cross join
     json_table(t.tags, '$[*]' COLUMNS (tag varchar(255) path '$')) tag

Here is a db<>fiddle.