Reading in dataframe with json file in R

Question

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)

Please show sample of JSON as it depends on structure to migrate the nested, tree type into two dimensions of dataframe. Also, please mention your RDBMS (SQL Server, Postgres, etc.) as methods may be available to query JSON into tabular format. Since, SQL is a language, SQL Database is akin to saying English book. — Parfait
– Parfait, Commented Jul 7, 2019 at 14:03

dylanvanw · Accepted Answer · 2019-07-05 13:01:22Z

1

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

answered Jul 5, 2019 at 13:01

dylanvanw

3,3391 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reading in dataframe with json file in R

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related