CODEWITHSUNDEEP
mysqljson
Peter Karlsson
Peter Karlsson

Reputation: 423

How do I get all distinct property names from a JSON column?

I have a MySQL JSON field with this content.

{"name": "Safari", "os": "Mac", "resolution": "1920x1080"}
{"name": "Firefox", "os": "PC", "resolution": "1920x1080"}
{"name": "Chrome", "os": "PC", "resolution": "1920x1080"}

I can get all browser names with this query.

SELECT browser->'$.name' browser FROM mytable;
Result = Safari, Firefox, Chrome

But how do I write a query that will give me "name", "os" and "resolution" as the result?

Result = name, os, resolution

Upvotes: 2

Views: 2552

Answers (2)

Nick
Nick

Reputation: 147166

You can use JSON_KEYS to get a JSON array with the key names in it (which you would then need to parse in your application):

SELECT JSON_KEYS('{"name": "Safari", "os": "Mac", "resolution": "1920x1080"}')

Output:

["os", "name", "resolution"]

To get separate rows with the keys, you'd need MySQL 8+ and JSON_TABLE:

SELECT *
FROM JSON_TABLE(JSON_KEYS('{"name": "Safari", "os": "Mac", "resolution": "1920x1080"}'),
                '$[*]' COLUMNS (key_name VARCHAR(20) PATH '$')
                ) AS k

Output:

key_name
os
name
resolution

Demo on dbfiddle

To apply this to a table, you would JOIN the JSON_TABLE code above to the table and use the JSON column name in place of the JSON string e.g.

SELECT DISTINCT k.key_name
FROM test t
JOIN JSON_TABLE(JSON_KEYS(t.j),
                '$[*]' COLUMNS (key_name VARCHAR(20) PATH '$')
                ) AS k

Demo on dbfiddle

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133370

select the related json attribute name 

SELECT browser->'$.name' name, browser->'$.os' os, browser->'$.resolution' resolution 
FROM mytable;

and if you need distinct result add the distinct clause 

SELECT DISTINCT browser->'$.name' name, browser->'$.os' os, browser->'$.resolution' resolution 
FROM mytable;

Upvotes: 0

Related Questions