Hello,
I am struggling with function json_table/json data with nested arrays. Any ideas how to extract bid_price and bid_amount?
Thanks
DECLARE
v_json_data CLOB := '{
"timestamp": "1713424111",
"microtimestamp": "1713424111929287",
"bids": [
[
"57374",
"0.00000255"
],
[
"57369",
"0.00052083"
],
[
"99999999",
"0.00000078"
]
]
}';
BEGIN
FOR rec IN (
SELECT *
FROM JSON_TABLE(
v_json_data,
'$'
COLUMNS (
timestamp VARCHAR2(30) PATH '$.timestamp',
microtimestamp VARCHAR2(30) PATH '$.microtimestamp',
bid_price VARCHAR2(30) PATH '$.bids[*][1]',
bid_amount VARCHAR2(30) PATH '$.bids[*][2]'
)
)
) LOOP
DBMS_OUTPUT.PUT_LINE('Timestamp: ' || rec.timestamp || ', Microtimestamp: ' || rec.microtimestamp || ', Bid Price: ' || rec.bid_price || ', Bid Amount: ' || rec.bid_amount);
END LOOP;
END;
/