Flattening hierarchical JSON APIs

[ABertoG]

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)
  Data<-fromJSON(rawToChar(RawData$content))

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

With the same method

ULR<-"https://protocol-api.aave.com/data/users/liquidations-all/"
RawData <- GET(ULR)
Data<-fromJSON(rawToChar(RawData$content))
str(Data)

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

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

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

Thanks for your help

[AlexisW]

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)
class(parsed[[1]])
#[1] "data.frame"

as_tibble(parsed[[1]])
# 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.