问题描述
我有json数据存储在blob中。数据来自yahoo，其结构如下:
我想读取存储在blob字段中的数据，并在for循环中进行迭代以将其存储为表中的关系数据。
我尝试了以下代码:
我得到以下结果:
如何将数据修复为如下所示:
谢谢你和问候
{
"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
}
}
我想读取存储在blob字段中的数据，并在for循环中进行迭代以将其存储为表中的关系数据。
我尝试了以下代码:
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
)
我得到以下结果:
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
如何将数据修复为如下所示:
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
谢谢你和问候
专家解答
您可以添加一个ordinality列并加入
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>
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
