如何在oracle中重申select中的某个语句

Question

有一列包含JSON表达式的文本。根本不清楚JSON数组有多长，在下面的示例代码中，我最多重复了六次这个短语(可以重复六次以上(。如何根据最长的数组长度重复重复(case when(？我还想用变量d_I和a_I指定列名(这里I是计数器(。我可以用一段时间还是循环？如果是，如何？注意：如果在任何一行中，JSON表达式中的第一个值不大于0，则该行中JSON数组的长度为零，并且这种情况一直持续到表示结束。这意味着，如果JSON数组的第一个单元格有值，第二个单元格可能有值，如果第二个单元没有值，那么数组的长度肯定是1。如果出现这种情况，循环必须重新开始。我希望我说得对。

select t.tx_id,
--00
case WHEN t.fee[0]:amount>0 then  t.fee[0]:denom end as d_0,
case when t.fee[0]:amount>0 then t.fee[0]:amount/1000000 end as a_0,
--01
case WHEN t.fee[1]:amount>0 then  t.fee[1]:denom end as d_1,
case when t.fee[1]:amount>0 then t.fee[1]:amount/1000000 end as a_1,
--02
case WHEN t.fee[2]:amount>0 then  t.fee[2]:denom end as d_2,
case when t.fee[2]:amount>0 then t.fee[2]:amount/1000000 end as a_2,
--03
case WHEN t.fee[3]:amount>0 then  t.fee[3]:denom end as d_3,
case when t.fee[3]:amount>0 then t.fee[3]:amount/1000000 end as a_3,
--04
case WHEN t.fee[4]:amount>0 then  t.fee[4]:denom end as d_4,
case when t.fee[4]:amount>0 then t.fee[4]:amount/1000000 end as a_4,
--05
case WHEN t.fee[5]:amount>0 then  t.fee[5]:denom end as d_5,
case when t.fee[5]:amount>0 then t.fee[5]:amount/1000000 end as a_5,
--06
case WHEN t.fee[6]:amount>0 then  t.fee[6]:denom end as d_6,
case when t.fee[6]:amount>0 then t.fee[6]:amount/1000000 end as a_6
from terra.transactions t 
where t.tx_id not in (select s.tx_id from terra.swaps s)   and fee[0].amount>0 limit 1000

Answer 1

假设您有表：

CREATE TABLE transactions (
tx_id NUMBER PRIMARY KEY,
fee   JSON
);

带数据：

INSERT INTO transactions (tx_id, fee) VALUES (
1,
'[{"denom":"ABC","amount":100},{"denom":"DEF","amount":0},{"denom":"GHI","amount":1}]'
);

然后最简单的方法是将数据输出为行(而不是列(：

select t.tx_id,
j.*
from   terra.transactions t
CROSS JOIN JSON_TABLE(
t.fee,
'$[*]'
COLUMNS
denom  VARCHAR2(20) PATH '$.denom',
amount NUMBER       PATH '$.amount'
) j
where  t.tx_id not in (select s.tx_id from terra.swaps s)
and    j.amount>0

哪个输出：

TX_ID	DENOM
1	ABC	100
1

我使用扁平表：

with flattenTable as (
SELECT
tx_id,
fee,
b.value as fee_parsed,
b.value:amount as fee_amount,
b.value:denom as fee_denom
FROM terra.transactions, TABLE(FLATTEN(terra.transactions.fee)) b 
where tx_id not in (select s.tx_id from terra.swaps s ) and fee_amount>0)
SELECT f.*,
case when f.fee_denom='uusd' then f.fee_amount/1000000 else f.fee_amount/1000000*(select 
avg(price_usd) 
from terra.oracle_prices o,flattenTable f
where o.CURRENCY = f.fee_denom  and o.block_timestamp=CURRENT_DATE) end as Fee_USD
from flattenTable f
limit 100