Example: Get an Ordered List of Unique Keys From Documents

Teradata Vantageā„¢ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

The example uses JSON_KEYS to get an ordered list of all unique keys from all documents of all rows of a table.

SELECT distinct(JSONKeys) from JSON_KEYS
(
ON (SELECT json_data FROM json_table))
AS json_data
ORDER BY 1;
Result:
JSONKeys
--------
"base"
"clouds"
"clouds"."all"
"cod"
"coord"
"coord"."lat"
"coord"."lon"
"dt"
"id"
"main"
"main"."humidity"
"main"."pressure"
"main"."temp"
"main"."temp_max"
"main"."temp_min"
"main"."temp_scale"
"name"
"sys"
"sys"."country"
"sys"."sunrise"
"sys"."sunset"
"weather"
"weather"[0]
"weather"[0]."description"
"weather"[0]."icon"
"weather"[0]."id"
"weather"[0]."main"
"wind"
"wind"."deg"
"wind"."speed"