How do I POST a JSON formatted request to GET JSON data from a URL in R into the data.frame in a less verbose manner?

Question

I have written the following code in R to start using a data request API. It's a normal web service JSON API.

library(RJSONIO)
library(RCurl)
library(httr)

r <- POST("http://api.scb.se/OV0104/v1/doris/sv/ssd/START/PR/PR0101/PR0101A/KPIFastM2", 
          body = '{ "query": [], "response": { "format": "json" } }')
stop_for_status(r)
a<-content(r, "text", "application/json", encoding="UTF-8")
cat(a, file = "test.json")
x<-fromJSON(file("test.json", "r"))
mydf<-do.call(rbind, lapply(x$data, data.frame))
colnames(mydf)<-c("YearMonth", "CPI")

Basically it initialized a get reuest for the URL using httr and then convert the resulting JSON data to an R structure via fromJSON. The JSON request looks like this:

{ "query": [], "response": { "format": "json" } }

Indeed my code gets the data into a data.frame like I wanted it to, but it is painfully verbose and I refuse to believe that all of these lines are necessary to achieve the wanted result. The wanted result is the mydf data.frame of course.

So to my question: What is the shortest and most correct way to get the data from the web service into the data.frame?

Cheers, Michael

The JSON seems like it's not formatted correctly, e.g., `jsonlite::validate(content(r, "text"))` throws an error. If it was valid JSON it would parse easily with just `content(r, "text")`, then `jsonlite::fromJSON()` may parse to a `data.frame` if possible for you — sckott, Nov 01 '14 at 23:14

score 7 · Accepted Answer · edited Oct 07 '21 at 05:51

There are two problems. One is that you are not using jsonlite :-) The other one is that your JSON source seems to prefix the blob with a U+FEFF Byte order Mark character that makes the JSON invalid. RFC7159 says:

Implementations MUST NOT add a byte order mark to the beginning of a JSON text. In the interests of interoperability, implementations that parse JSON texts MAY ignore the presence of a byte order mark rather than treating it as an error.

So scb.se is not formatting their JSON correctly. Either way, try this:

library(jsonlite)
library(httr)

req <- POST("http://api.scb.se/OV0104/v1/doris/sv/ssd/START/PR/PR0101/PR0101A/KPIFastM2", 
  body = '{ "query": [], "response": { "format": "json" } }')
stop_for_status(req)
json <- content(req, "text")


# JSON starts with an invalid character:
validate(json)
json <- substring(json, 2)
validate(json)

# Now we can parse
object <- jsonlite::fromJSON(json)
print(objects)

Indeed you're right. There was a lurking sucker in the beginning. Strange for such a big data provider to f*ck something like that up.. — Dr. Mike, Nov 02 '14 at 21:48
FYI the new version of jsonlite has a fix to ignore the BOM. — Jeroen Ooms, Nov 05 '14 at 05:48

How do I POST a JSON formatted request to GET JSON data from a URL in R into the data.frame in a less verbose manner?

1 Answers1

Linked