如何在Oracle中的选择中重申某个语句

Question

有一个文本列，其中包含 JSON 表达式。根本不清楚 JSON 数组有多长，在下面的示例代码中，我重复了该短语最多六次（可能重复超过六次）。如何根据最长的数组长度重复重复（情况）？ 我还想用变量 d_i 和 a_i 指定列名（这里 i 是计数器）。 我可以使用 while 或循环吗？如果是，怎么办？ 注意：如果在任何行中，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	ghi	1

如果您想将行动态旋转到列，那么最好在任何中间层应用程序（PHP，C＃，Java，Python等）中完成，以访问数据库。如果您想在Oracle中进行操作，则可以查看这个问题的答案 。

db＆lt;＆gt; fiddle

Answer 2

我使用展平表：

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