将 JSON 数据从 SQL 数据库导入 R 数据帧

Question

我想知道是否有办法将JSON数据从MySQL DB导入R数据帧。

我有一张这样的桌子：

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}

我想获取"id"和"json"列。 我正在使用 RMySQL 包将数据从数据库获取到 R 数据帧，但这只给了我"id"列，"json"列每行仅包含 NA。

有什么方法可以导入/加载数据并显示 json 列？并可能提取 json 值的"传感器"部分？

结果将是如下所示的数据帧 (df(：

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}

或者使用提取的值：

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

非常感谢您的任何建议。

Answer 1

使用tidyr中的unnest_wider

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)

结果：

# 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

如果只想检索过去 24 小时内的数据(根据 OP 请求(，请将tbl(con, 'table_name')语句更改为：

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

Answer 2

将 JSON响应转换为数据帧应该很简单，但是，由于 JSON 响应的结构本质上是任意的，并且您没有向我们提供有关如何获取它的详细信息或其内容的确切详细信息，因此不可能为您提供适用于您的特定情况的代码。 但是，这是我的一个应用程序中工作的基本过程，从对提供数据库访问的 API 的 post 调用开始。

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)

当然，您应该在整个过程中纳入错误和状态检查。

注意：您的数据包含拼写错误。 "威格特"应该是"重量"。