Hi,
I'm trying to fetch nested JSON data from an URL. The JSON looks like this (I have shortened it a little):
{
"chart": {
"result": [
{
"meta": {
"currency": "EUR"
},
"timestamp": [
1621926000,
1622012400,
1622098800,
1622185200
],
"indicators": {
"quote": [
{
"open": [
206.60000610351562,
206.0500030517578,
205.5500030517578,
205.9499969482422
],
"volume": [
19358,
12824,
12168,
24910
],
"low": [
204.75,
205.3000030517578,
204.1999969482422,
205
],
"high": [
206.60000610351562,
206.8000030517578,
206.35000610351562,
207.25
],
"close": [
205.5,
205.9499969482422,
205.8000030517578,
206
]
}
]
}
}
],
"error": null
}
}
And here is my code to get the currency, timestamp and the indicators.quote.close values:
with json as (
select
apex_web_service.make_rest_request(
p_url => 'https://URL',
p_http_method => 'GET'
) as json
from dual)
select currency, preis, zeit
from json, json_table(
json,
'$.chart.result' columns(
currency varchar2 path '$.meta.currency',
NESTED PATH '$.indicators.quote[*].close[*]' COLUMNS (
preis number PATH '$'),
NESTED PATH '$.timestamp[*]' COLUMNS (
zeit number PATH '$')
)
)
If I run this I get the currency and timestamp values, but not the indicators.quote.close. If I remove the nested path for the timestamp from my SELECT I will get currency and indicators.quote.close values. But I will get timestamp and indicators.quote.close together in one SELECT statement.
Any idea please?