CODEWITHSUNDEEP
jsonoracle-databaseoracle-apex
Sandy Hunt
Sandy Hunt

Reputation: 13

How to parse JSON array that has no element/property names using Oracle

I am new to JSON and am trying to parse the data returned by following URL

https://api.binance.com/api/v3/klines?symbol=LTCBTC&interval=5m

The data is public if you want to see the exact output

I am in an Oracle 18c database trying to use json_table but I am not sure how to format the query or reference the columns as the JSON has no names, just values.

If I just paste in one record from the array as follows then I can get a column with all the values, but I need to parse the entire array and get the output into a table

SELECT *
FROM json_table( '[1617210000000,"0.00325500","0.00326600","0.00325400","0.00326600","780.81000000",1617210299999,"2.54374363",210,"569.58000000","1.85545803","0"]' , '$[*]'
                COLUMNS (value PATH '$' ))

I have been searching google for days and not found an example of what I am trying to do, all the example use JSON with name:value pairs.

Thank you in advance.

Upvotes: 1

Views: 1381

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

The raw data is an array of arrays, so you can use $[*] to get the individual arrays, and then numbered positions to get the values from each of those arrays:

SELECT *
FROM json_table(
  '[[...], [...], ...]', -- use actual data, as CLOB?
  '$[*]'
  COLUMNS (
    open_time PATH '$[0]',
    open PATH '$[1]',
    high PATH '$[2]',
    low PATH '$[3]',
    close PATH '$[4]',
    volume PATH '$[5]',
    close_time PATH '$[6]',
    quote_av PATH '$[7]',
    number_of_trades PATH '$[8]',
    taker_buy_base_av PATH '$[9]',
    taker_buy_quote_av PATH '$[10]',
    ignore PATH '$[11]'
  )
)

I've taken the column names from the API documentation. Not sure why some are strings, presumably a precision thing; but you can obviously specify the data types. (And there are lots of examples of converted epoch timestamps to Oracle dates/timestamps if you want to do that.)

db<>fiddle with four entries, and an additional column for ordinality, which you might not want/need.

IDX | OPEN_TIME     | OPEN       | HIGH       | LOW        | CLOSE      | VOLUME       | CLOSE_TIME    | QUOTE_AV   | NUMBER_OF_TRADES | TAKER_BUY_BASE_AV | TAKER_BUY_QUOTE_AV | IGNORE
--: | :------------ | :--------- | :--------- | :--------- | :--------- | :----------- | :------------ | :--------- | :--------------- | :---------------- | :----------------- | :-----
  1 | 1617423900000 | 0.00356800 | 0.00357100 | 0.00356400 | 0.00356800 | 358.71000000 | 1617424199999 | 1.27964866 | 90               | 313.96000000      | 1.12008826         | 0     
  2 | 1617424200000 | 0.00356800 | 0.00357000 | 0.00356600 | 0.00356800 | 349.47000000 | 1617424499999 | 1.24704741 | 105              | 283.05000000      | 1.01005077         | 0     
  3 | 1617424500000 | 0.00357000 | 0.00357900 | 0.00357000 | 0.00357400 | 412.32000000 | 1617424799999 | 1.47359944 | 127              | 53.73000000       | 0.19203676         | 0     
  4 | 1617424800000 | 0.00357500 | 0.00357500 | 0.00356500 | 0.00356600 | 910.58000000 | 1617425099999 | 3.25045272 | 198              | 463.30000000      | 1.65400945         | 0

Upvotes: 5

Related Questions