Importing JSON data from SQL DB to an R dataframe

Question

I would like to know whether there is a way of importing JSON data from a MySQL DB to an R dataframe.

I have a table like this:

id  created_at   json
1   2020-07-01   {"name":"Dent, Arthur","group":"Green","age (y)":43,"height (cm)":187,"wieght (kg)":89,"sensor":34834834}
2   2020-07-01   {"name":"Doe, Jane","group":"Blue","age (y)":23,"height (cm)":172,"wieght (kg)":67,"sensor":12342439}
3   2020-07-01   {"name":"Curt, Travis","group":"Red","age (y)":13,"height (cm)":128,"wieght (kg)":47,"sensor":83287699}

I would like to get the columns 'id' and 'json'. I am using RMySQL package for getting the data from the db to an R dataframe but this gives me only the column 'id', the column 'json' contains only NAs in each row.

Is there any way how to import/load the data and get the json column displayed? And possibly to extract the "sensor" part of the json values?

The result would be a dataframe (df) like this:

id   json
1    {"name":"Dent, Arthur","group":"Green","age (y)":43,"height (cm)":187,"wieght (kg)":89,"sensor":34834834}
2    {"name":"Doe, Jane","group":"Blue","age (y)":23,"height (cm)":172,"wieght (kg)":67,"sensor":12342439}
3    {"name":"Curt, Travis","group":"Red","age (y)":13,"height (cm)":128,"wieght (kg)":47,"sensor":83287699}

Or with with the extracted value:

id   sensor
1    "sensor":34834834
2    "sensor":12342439
3    "sensor":83287699

Thank you very much for any suggestions.

kofm · Accepted Answer · 2020-07-03 08:25:30Z

2

Using unnest_wider from tidyr

library(dplyr)

con <- DBI::dbConnect(RMySQL::MySQL(), 'db_name', user = 'user', password = 'pass', host = 'hostname')

t <- tbl(con, 'table_name')
  
t %>% 
  as_tibble() %>% 
  transmute(j = purrr::map(json, jsonlite::fromJSON)) %>%
  tidyr::unnest_wider(j)


DBI::dbDisconnect(con)

Result:

# A tibble: 3 x 6
  name         group `age (y)` `height (cm)` `wieght (kg)`   sensor
  <chr>        <chr>     <int>         <int>         <int>    <int>
1 Dent, Arthur Green        43           187            89 34834834
2 Doe, Jane    Blue         23           172            67 12342439
3 Curt, Travis Red          13           128            47 83287699

If you want to only retrieve data from the last 24 hours (as the OP requested) change the tbl(con, 'table_name') statement to:

t <- DBI::dbGetQuery(con, 'SELECT * FROM `table_name` WHERE DATE(`created_at`) > NOW() - INTERVAL 1 DAY')

edited Jul 3, 2020 at 8:25

answered Jul 3, 2020 at 7:55

kofm

1451 silver badge12 bronze badges

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

9 Comments

dloudtrain Over a year ago

Thank you @kofm. And is there a way to transmute only the last 24hours of data in the table? The table is huge. Thank you very much.

kofm Over a year ago

Do you mean without even retrieving it from MySQL or by filtering the table with dbplyr?

dloudtrain Over a year ago

I mean subseting the data for last 24 hours before unnesting the jsons, preferably without retrieving from MySQL.

kofm Over a year ago

Updated the original answer

dloudtrain Over a year ago

Perfect, thank you, the subsetting works great but the unnesting part gives this error: Error: lexical error: invalid character inside string. ":"733","arg":"$u0027AT CMGF=1 u0027$u0027OK u0027$u0027 u0 (right here) ------^ Would you know what this means? Thank you very much!

|

Limey · Accepted Answer · 2020-07-03 07:29:58Z

Converting your JSON response to a data frame should be straightforward but, because the structure of a JSON response is essentially arbitrary and you haven't given us details of how you obtain it or exact details of its content, it's impossible to give you code that will work in your specific case. However, this is the basic process that works in one of my appliocations, starting with the post call to the API that provides access to the database.

library(httr)
library(jsonlite)

# Query the API
response <- POST(<your code here>)
# Extract the content of the response.  Amend the format an encoding if necessary.
content <- content(response, as="text", encoding="UTF-8")
#  Convert the content to an R object
content <- fromJSON(content, flatten=FALSE)
# Coerce to data.frame
df <- as.data.frame(content)

You should, of course, incorporate error and status checking throughout the process.

Note: your data contains a spelling mistake. "wieght" should be "weight".

Collectives™ on Stack Overflow

Importing JSON data from SQL DB to an R dataframe

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related