Flattening hierarchical JSON APIs

Hi all,

I have been recently started using data imported by API which is usually in the JSON format.

For basic JSON this method has been working well

  ULR <- paste("https://api.coingecko.com/api/v3/coins/ethereum/market_chart?vs_currency=usd&days=max",sep="")
  RawData <- GET(ULR)

I'm now trying to extract more complex API without success

With the same method

RawData <- GET(ULR)

This Data is an unreadable large character but there is the text in there. Read the tibble package but it returns an empty table

FlatData <- flatten(as_tibble(Data))

Also found this alternative method but doesn't manage to do the last operation

jsonResponse <-GET("https://protocol-api.aave.com/data/users/liquidations-all/")
jsonResponseParsed <- content(jsonResponse, as="parsed")

What am I missing? Is it possible to read multi level JSON with R?

Thanks for your help :smiling_face_with_three_hearts:

I think the problem here is that there was two rounds of escaping characters. If you have a quotation mark " it would get escaped so that it can be used in a string enclosed with quotation marks: \". But I think there was a second escaping, making it into \\\".

Anyway, I can't totally explain why that works, but it does seem to work:

jsonResponseParsed <- content(RawData, as="parsed")

parsed <- jsonlite::fromJSON(jsonResponseParsed)
#[1] "data.frame"

# A tibble: 4,534 x 7
   principalBorrows currentBorrows currentBorrowsE~ currentBorrowsU~ reserve$id
   <chr>            <chr>          <chr>            <chr>            <chr>     
 1 131576.533018    131614.546163  390.500358465621 131861.34582415~ 0xa0b8699~
 2 10000.372989348~ 10001.7315681~ 29.818975204302~ 10069.0565739454 0x0000000~
 3 0.00019724       0.00019727     0.0059529091959~ 2.0101354611     0x2260fac~
 4 1.0004040365183~ 1.00090281723~ 0.0348833526366~ 11.7791590348    0x5149107~
 5 0.0785061166229~ 0.07980286849~ 0.0009983338848~ 0.3371101889     0xc011a73~
 6 72675.032514     72680.072674   215.569095551084 72791.8180898765 0xdac17f9~
 7 2143.746664      2143.8454      6.3586454564     2147.1415565252  0xdac17f9~
 8 7.8285168306033~ 7.82900595075~ 7.8290059507575~ 2643.642288034   0xeeeeeee~
 9 43.190035521271~ 43.4626428891~ 0.0095870328109~ 3.2372801241     0x0f5d2fb~
10 1.1322448086271~ 1.13437965714~ 1.1343796571435~ 383.0491445749   0xeeeeeee~
# ... with 4,524 more rows, and 22 more variables: $underlyingAsset <chr>,
#   $symbol <chr>, $decimals <int>, $`__typename` <chr>, user$id <chr>,
#   $reservesData <list>, $totalLiquidityETH <chr>, $totalLiquidityUSD <chr>,
#   $totalCollateralETH <chr>, $totalCollateralUSD <chr>, $totalFeesETH <chr>,
#   $totalFeesUSD <chr>, $totalBorrowsETH <chr>, $totalBorrowsUSD <chr>,
#   $totalBorrowsWithFeesETH <chr>, $totalBorrowsWithFeesUSD <chr>,
#   $availableBorrowsETH <chr>, $currentLoanToValue <chr>,
#   $currentLiquidationThreshold <chr>, $maxAmountToWithdrawInEth <chr>,
#   $healthFactor <chr>, id <chr>

Just note that's not a usual problem, normally fromJSON works directly (and multilevel is not a problem), here I think something went wrong with the preprocessing before you got the data.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.