CODEWITHSUNDEEP
mysqlsqljson
Pavol
Pavol

Reputation: 572

Extract all JSON keys from MySQL

I have a JSON column properties like:

{'a': 2, 'b': 5}
{'c': 3, 'a': 5}
{'d': 1, 'c': 7}
{'e': 1, 'f': 7}

How can I get all distinct (top-level) key names from MySQL?

Like this:

['a', 'b', 'c', 'd', 'e', 'f']

Thank You!

Upvotes: 7

Views: 14603

Answers (3)

eshizhan
eshizhan

Reputation: 4635

test_tbl:

+----+------------------+
| id | json_col         |
+----+------------------+
|  2 | {"a": 2, "b": 5} |
|  3 | {"c": 3, "a": 5} |
|  4 | {"d": 1, "c": 7} |
|  5 | {"e": 1, "f": 7} |
+----+------------------+

Using JSON_TABLE in Mysql 8.0:

SELECT DISTINCT json_key FROM test_tbl,
    json_table(
        json_keys(json_col),
        '$[*]' COLUMNS(json_key JSON PATH '$')
    ) t;

Results:

+----------+
| json_key |
+----------+
| a        |
| b        |
| c        |
| d        |
| e        |
| f        |
+----------+

Upvotes: 8

Anton
Anton

Reputation: 1057

Well, not the best solution but works for json data type

select distinct(json_extract(json_keys(*YOUR_VALUE*),'$[0]')) from *YOUR_TABLE*
union
select distinct(json_extract(json_keys(*YOUR_VALUE*),'$[1]')) from *YOUR_TABLE*

Upvotes: 1

Dave Stokes
Dave Stokes

Reputation: 823

Start with JSON_KEYS to get the keys. From there you have some choices but probably the best would be JSON_ARRAY.

Upvotes: -1

Related Questions