How can reiterate a certain statement in select in oracle

Question

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

So, the values that come from your array must become **columns** in the output? You won't even know how many columns the output has, until you see the JSON and you count the array elements? This will require dynamic SQL, an advanced subject (and generally not a good practice for this kind of use case anyway). Or do you need something else, but you just wrote your attempted code in a way you didn't mean to? — , Mar 27 '22 at 19:27
Please [edit] your question to include a [MRE] with: the `CREATE TABLE` statement for your table; the `INSERT` statements for some sample data; and the expected output from your query. Sometimes it is easier to show us what you are starting with and what you would like to end up with rather than trying to explain some code. — MT0, Mar 27 '22 at 21:22

score 0 · Answer 1 · answered Mar 27 '22 at 21:52

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

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

Thanks mate. you are right. I compelete this answer. – Piroozman Mar 29 '22 at 07:18 — Piroozman, Mar 29 '22 at 07:18

score 0 · Answer 2 · answered Mar 29 '22 at 07:52

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

How can reiterate a certain statement in select in oracle

2 Answers2