CODEWITHSUNDEEP
sqlr
handep
handep

Reputation: 21

Reading in dataframe with json file in R

I have an SQL database where I want to select a column of JSONfiles and convert it to a readable R dataframe. I succeed in creating a dataframe of the JSONfiles from my database, but when I try to read them in with the fromJSON function it doesn't read all the JSONfiles.

the data$products_json column is a column with in every row a JSON file.

rs = dbSendQuery(mydb, "SELECT products_json FROM orders")
data = fetch(rs, n=-1)

library(rjson)

jfile <- fromJSON(data$products_json)

Upvotes: 0

Views: 261

Answers (1)

dylanvanw
dylanvanw

Reputation: 3341

You can try to collapse all JSON docs into one big JSON array. This will result in a structure that jsonlite can handle. Jsonlite also has a fromJSON function that makes it very easy to transform a JSON array into an R dataframe.

In your case this would be:

library(jsonlite)

# This will collapse the products_json into one string that represents a json_array
partial_array <- paste(data$products_json,  collapse = ', ')
json_array <- paste('[', partial_array, ']')

jfile <- jsonlite::fromJSON(json_array)

Where products_json is a column of JSON docs in data

Upvotes: 1

Related Questions