例: JSONExtractValueとともにJSON_KEYSを使用してすべての値を抽出 - Teradata Database - Teradata Vantage NewSQL Engine - 次の例では、JSONExtractValueとともにJSON_KEYS を使用してJSON文書のすべての値を抽出します。

次の例では、JSONExtractValueとともにJSON_KEYS を使用してJSON文書のすべての値を抽出します。

SELECT CAST(JSONKeys AS VARCHAR(30)), 
T.json_data.JSONExtractValue('$.'||JSONKeys) from json_table T, JSON_KEYS
(
ON (SELECT json_data FROM json_table WHERE id=1) USING QUOTES('N'))
AS json_data
where T.id=1
ORDER BY 1;

結果:

JSONKeys                       json_data.JSONEXTRACTVALUE(('$.'||JSONKeys))
---------------------------------------------------------------------------
base                           global stations
clouds                         {"all":0}
clouds.all                     0
cod                            200
coord                          {"lon":145.766663,"lat":-16.91667}
coord.lat                      -16.91667
coord.lon                      145.766663
dt                             1375292971
id                             2172797
main                           {"temp":288.97,"humidity":99,"pressure":1007,"temp_min":288.71,"temp_max":289.15}
main.humidity                  99
main.pressure                  1007
main.temp                      288.97
main.temp_max                  289.15
main.temp_min                  288.71
name                           Cairns
sys                            {"country":"AU","sunrise":1375216946,"sunset":1375257851}
sys.country                    AU
sys.sunrise                    1375216946
sys.sunset                     1375257851
weather                        [{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]
weather[0]                     {"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}
weather[0].description         Sky is Clear
weather[0].icon                01n
weather[0].id                  800
weather[0].main                Clear
wind                           {"speed":5.35,"deg":145.001}
wind.deg                       145.001
wind.speed                     5.35

SELECT CAST(JSONKeys AS VARCHAR(30)), 
T.json_data.JSONExtractValue('$.'||JSONKeys) from json_table T, JSON_KEYS
(
ON (SELECT json_data FROM json_table WHERE id=2) USING QUOTES('N'))
AS json_data
where T.id=2
ORDER BY 1;

結果:

JSONKeys                       json_data.JSONEXTRACTVALUE(('$.'||JSONKeys))
---------------------------------------------------------------------------
base                           global stations
clouds                         {"all":0}
clouds.all                     0
cod                            200
coord                          {"lon":245.766663,"lat":-16.91667}
coord.lat                      -16.91667
coord.lon                      245.766663
dt                             1375292971
id                             2172797
main                           {"temp":288.97,"humidity":99,"pressure":1007,"temp_min":288.71,"temp_max":289.15,"temp_scale":"Fahrenheit"}
main.humidity                  99
main.pressure                  1007
main.temp                      288.97
main.temp_max                  289.15
main.temp_min                  288.71
main.temp_scale                Fahrenheit
name                           Los Angeles
sys                            {"country":"US","sunrise":1375216946,"sunset":1375257851}
sys.country                    US
sys.sunrise                    1375216946
sys.sunset                     1375257851
weather                        [{"id":800,"main":"Clear","description":"Sky is Cloudy","icon":"01n"}]
weather[0]                     {"id":800,"main":"Clear","description":"Sky is Cloudy","icon":"01n"}
weather[0].description         Sky is Cloudy
weather[0].icon                01n
weather[0].id                  800
weather[0].main                Clear
wind                           {"speed":5.35,"deg":145.001}
wind.deg                       145.001
wind.speed                     5.35