I have json data stored in a blob. The data is from yahoo and it is structured as follows:
{
"chart": {
"result": [
{
"meta": {
"currency": "USD",
"symbol": "AMCR",
"exchangeName": "NYQ",
"instrumentType": "EQUITY",
"firstTradeDate": 1337068800,
"gmtoffset": -14400,
"timezone": "EDT",
"exchangeTimezoneName": "America/New_York",
"chartPreviousClose": 10.3,
"priceHint": 2,
"currentTradingPeriod": {
"pre": {
"timezone": "EDT",
"start": 1562313600,
"end": 1562333400,
"gmtoffset": -14400
},
"regular": {
"timezone": "EDT",
"start": 1562333400,
"end": 1562356800,
"gmtoffset": -14400
},
"post": {
"timezone": "EDT",
"start": 1562356800,
"end": 1562371200,
"gmtoffset": -14400
}
},
"dataGranularity": "1d",
"validRanges": [
"1d",
"5d",
"1mo",
"3mo",
"6mo",
"1y",
"2y",
"5y",
"10y",
"ytd",
"max"
]
},
"timestamp": [
1387377000,
1387463400,
1387549800,
1387809000,
1387895400
],
"indicators": {
"quote": [
{
"high": [
9.279999732971191,
9.319999694824219,
9.369999885559082,
9.479999542236328,
9.479999542236328
],
"close": [
9.279999732971191,
9.319999694824219,
9.369999885559082,
9.479999542236328,
9.479999542236328
],
"volume": [
200,
1000,
200,
1000,
0
],
"open": [
9.279999732971191,
9.319999694824219,
9.369999885559082,
9.479999542236328,
9.479999542236328
],
"low": [
9.279999732971191,
9.319999694824219,
9.369999885559082,
9.479999542236328,
9.479999542236328
]
}
],
"adjclose": [
{
"adjclose": [
7.631077766418457,
7.663969993591309,
7.705083847045898,
7.795538425445557,
7.795538425445557
]
}
]
}
}
],
"error": null
}
}
I would like to read the data which is stored in a blob field and iterate through it in a for loop to store it as relational data in a table.
I tried the following code:
with rws as (
SELECT j.*
FROM http_blob_test,JSON_TABLE(data, '$' COLUMNS (
symbol VARCHAR2(5) PATH '$.chart.result.meta.symbol',
NESTED PATH '$.chart.result.timestamp[*]'
COLUMNS (timestamp number PATH '$'
),
NESTED PATH '$.chart.result.indicators.quote.volume[*]'
COLUMNS (volume number PATH '$'
),
NESTED PATH '$.chart.result.indicators.quote.open[*]'
COLUMNS (open number PATH '$'
),
NESTED PATH '$.chart.result.indicators.quote.low[*]'
COLUMNS (low number PATH '$'
),
NESTED PATH '$.chart.result.indicators.quote.high[*]'
COLUMNS (high number PATH '$'
),
NESTED PATH '$.chart.result.indicators.quote.close[*]'
COLUMNS (close number PATH '$'
)
)
) j
)
I get the following result:
SYMBOL TIMESTAMP VOLUME OPEN LOW HIGH CLOSE
AMCR 1387377000
AMCR 1387463400
AMCR 1387549800
AMCR 200
AMCR 1000
AMCR 200
AMCR 9.27999973
AMCR 9.31999969
AMCR 9.36999989
AMCR 9.27999973
AMCR 9.31999969
AMCR 9.36999989
AMCR 9.27999973
AMCR 9.31999969
AMCR 9.36999989
AMCR 9.27999973
AMCR 9.31999969
AMCR 9.36999989
How can I fix the data to look like the following:
SYMBOL TIMESTAMP VOLUME OPEN LOW HIGH CLOSE
AMCR 1387377000 200 9.27 9.27 9.27 9.279
AMCR 1387463400 1000 9.31 9.31 9.31 9.31
AMCR 1387549800 200 9.36 9.36 9.36 9.36
Thank you and regards
You could add an ordinality column and join on that
SQL> with rws as (
2 SELECT j.*
3 FROM t,JSON_TABLE(c, '$' COLUMNS (
4 symbol VARCHAR2(5) PATH '$.chart.result.meta.symbol',
5 NESTED PATH '$.chart.result.timestamp[*]'
6 COLUMNS (timestamp number PATH '$', t_ord for ordinality
7 ),
8 NESTED PATH '$.chart.result.indicators.quote.volume[*]'
9 COLUMNS (volume number PATH '$', v_ord for ordinality
10 ),
11 NESTED PATH '$.chart.result.indicators.quote.open[*]'
12 COLUMNS (open number PATH '$', o_ord for ordinality
13 ),
14 NESTED PATH '$.chart.result.indicators.quote.low[*]'
15 COLUMNS (low number PATH '$', l_ord for ordinality
16 ),
17 NESTED PATH '$.chart.result.indicators.quote.high[*]'
18 COLUMNS (high number PATH '$', h_ord for ordinality
19 ),
20 NESTED PATH '$.chart.result.indicators.quote.close[*]'
21 COLUMNS (close number PATH '$', c_ord for ordinality
22 )
23 )
24 ) j
25 )
26 select *
27 from rws;
SYMBO TIMESTAMP T_ORD VOLUME V_ORD OPEN O_ORD LOW L_ORD HIGH H_ORD CLOSE C_ORD
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
AMCR 1387377000 1
AMCR 1387463400 2
AMCR 1387549800 3
AMCR 1387809000 4
AMCR 1387895400 5
AMCR 9.27999973 1
AMCR 9.31999969 2
AMCR 9.36999989 3
AMCR 9.47999954 4
AMCR 9.47999954 5
AMCR 9.27999973 1
AMCR 9.31999969 2
AMCR 9.36999989 3
AMCR 9.47999954 4
AMCR 9.47999954 5
AMCR 200 1
AMCR 1000 2
AMCR 200 3
AMCR 1000 4
AMCR 0 5
AMCR 9.27999973 1
AMCR 9.31999969 2
AMCR 9.36999989 3
AMCR 9.47999954 4
AMCR 9.47999954 5
AMCR 9.27999973 1
AMCR 9.31999969 2
AMCR 9.36999989 3
AMCR 9.47999954 4
AMCR 9.47999954 5
30 rows selected.
SQL> with rws as (
2 SELECT j.*
3 FROM t,JSON_TABLE(c, '$' COLUMNS (
4 symbol VARCHAR2(5) PATH '$.chart.result.meta.symbol',
5 NESTED PATH '$.chart.result.timestamp[*]'
6 COLUMNS (timestamp number PATH '$', t_ord for ordinality
7 ),
8 NESTED PATH '$.chart.result.indicators.quote.volume[*]'
9 COLUMNS (volume number PATH '$', v_ord for ordinality
10 ),
11 NESTED PATH '$.chart.result.indicators.quote.open[*]'
12 COLUMNS (open number PATH '$', o_ord for ordinality
13 ),
14 NESTED PATH '$.chart.result.indicators.quote.low[*]'
15 COLUMNS (low number PATH '$', l_ord for ordinality
16 ),
17 NESTED PATH '$.chart.result.indicators.quote.high[*]'
18 COLUMNS (high number PATH '$', h_ord for ordinality
19 ),
20 NESTED PATH '$.chart.result.indicators.quote.close[*]'
21 COLUMNS (close number PATH '$', c_ord for ordinality
22 )
23 )
24 ) j
25 )
26 select
27 t.symbol,
28 t.timestamp,
29 c.close,
30 o.open,
31 l.low,
32 h.high
33 from rws t,
34 rws c,
35 rws o,
36 rws l,
37 rws h
38 where t.t_ord = c.c_ord
39 and t.t_ord = o.o_ord
40 and t.t_ord = l.l_ord
41 and t.t_ord = h.h_ord;
SYMBO TIMESTAMP CLOSE OPEN LOW HIGH
----- ---------- ---------- ---------- ---------- ----------
AMCR 1387377000 9.27999973 9.27999973 9.27999973 9.27999973
AMCR 1387463400 9.31999969 9.31999969 9.31999969 9.31999969
AMCR 1387549800 9.36999989 9.36999989 9.36999989 9.36999989
AMCR 1387809000 9.47999954 9.47999954 9.47999954 9.47999954
AMCR 1387895400 9.47999954 9.47999954 9.47999954 9.47999954
5 rows selected.
SQL>