Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

JSON_TABLE and JSON data

LemarApr 18 2024

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;
/
This post has been answered by Solomon Yakobson on Apr 18 2024
Jump to Answer
Comments
Post Details
Added on Apr 18 2024
7 comments
216 views