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.

If you want to manipulate data within the tags column, then you should not store it as json, but in a normalised form. — Shadow, Nov 10 '19 at 11:40
*"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."* *"Thanks a lot it work in MySQL 8, but it's not working in 5.6, can you help? "* [this](https://stackoverflow.com/a/55359984/2548147) is how i tweaked or should i say *"hacked"* in `JSON_TABLE()` support in the past in MySQL versions under MySQL 8 ... — Raymond Nijland, Nov 10 '19 at 13:21
@Shadow I don't wanna create new table (others reason) so I will stay with json column — Kyaw Kyaw Soe, Nov 10 '19 at 14:22
@KyawKyawSoewait until you see the performance of your chosen solution on larger datasets... — Shadow, Nov 10 '19 at 18:01

score 5 · Accepted Answer · answered Nov 10 '19 at 12:03

5

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      |

answered Nov 10 '19 at 12:03

GMB

216,147
25
84
135

thanks, I don't fully understand but it work lol, I will find time to do research later. – Kyaw Kyaw Soe Nov 10 '19 at 14:20
MYSQL 10.5 syntax error: [dbfiddle](https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=c227e517e8510c4f1e692796d8f579bc) . Funny as the earlier version holds true ... – Brownrice Nov 21 '20 at 15:48
1

@Brownrice: that's *MariaDB* 10.5, not MySQL. As of now, `json_table()` is just not implemented in MariaDB, even in the latest version. – GMB Nov 21 '20 at 15:50

score 2 · Answer 2 · answered Nov 10 '19 at 11:50

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.

answered Nov 10 '19 at 11:50

Gordon Linoff

1,242,037
58
646
786

Thanks a lot it work in MySQL 8, but it's not working in 5.6, can you help? – Kyaw Kyaw Soe Nov 10 '19 at 12:01
1

@KyawKyawSoe . . . Alas, this functionality is not available pre-8. I might suggest that you upgrade; the JSON support is much better in the more recent versions. – Gordon Linoff Nov 10 '19 at 16:03

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

2 Answers2