次の例では、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