How can reiterate a certain statement in select in oracle-CodePudding

There is a column of text with JSON expressions in it. It is not at all clear how long the JSON array is, and in the example code below I have repeated the phrase up to six times (it can be more than six repetitions). How can I repeat a duplicate (case when) based on the longest array length? I also want to specify the column names with the variables d_i and a_i (here i is the counter). Can I use a while or loop? If Yes, HOW? Note: If in any row, the first value in the JSON expression is not greater than 0, then the length of the JSON array in that row is zero, and this continues until the end of the representation. This means that if the first cell of the JSON array has a value, the second cell may have a value, and if the second cell has no value, then the length of the array is definitely 1. If this condition occurs, the loop must start again. I hope I have stated what I mean correctly.

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

CodePudding user response：

Assuming that you have the table:

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

With the data:

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

Then the simplest method is to output the data as rows (and not as columns):

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

Which outputs:

TX_ID DENOM AMOUNT

1 ABC 100

1 GHI 1

TX_ID	DENOM	AMOUNT
1	ABC	100
1	GHI	1

If you want to dynamically pivot the rows to columns then this is best done in whatever middle-tier application (PHP, C#, Java, Python, etc.) that you are using to access the database. If you want to do it in Oracle then you can look at the answers to this question.

db<>fiddle here

CodePudding user response：

I use flatten table:

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