1

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.

Improve this question

1 Answer 1

Reset to default
5

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This worked perfectly, thank you so much!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.