Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE transactions (
  tx_id NUMBER PRIMARY KEY,
  fee   JSON
)
CREATE TABLE swaps (
  tx_id NUMBER PRIMARY KEY
)
INSERT INTO transactions (tx_id, fee) VALUES (
  1,
  '[{"denom":"ABC","amount":100},{"denom":"DEF","amount":0},{"denom":"GHI","amount":1}]'
);
1 rows affected

select t.tx_id,
       --00
       case WHEN t.fee[0].amount.number()>0 then t.fee[0].denom.string() end as d_0,
       case when t.fee[0].amount.number()>0 then t.fee[0].amount.number()/1000000 end as a_0,
       --01
       case WHEN t.fee[1].amount.number()>0 then t.fee[1].denom.string() end as d_1,
       case when t.fee[1].amount.number()>0 then t.fee[1].amount.number()/1000000 end as a_1,
       --02
       case WHEN t.fee[2].amount.number()>0 then t.fee[2].denom.string() end as d_2,
       case when t.fee[2].amount.number()>0 then t.fee[2].amount.number()/1000000 end as a_2
from   /*terra.*/transactions t 
where  t.tx_id not in (select s.tx_id from /*terra.*/swaps s)
and    t.fee[0].amount.number()>0
FETCH FIRST 1000 ROWS ONLY;


TX_ID
D_0
A_0
D_1
A_1
D_2
A_2


1
ABC
.0001
null
null
GHI
.000001


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
AMOUNT


1
ABC
100

1
GHI
1