I have data in a JSON file that I want to unnest into a data.table. The JSON is structured like this:
[
{
"version_id": "123456",
"data": [
{
"review_id": "1",
"rating": 5,
"review": "This app is great",
"date": "2024-09-01"
},
{
"review_id": "2",
"rating": 1,
"review": "This app is terrible",
"date": "2024-09-01"
}
]
},
{
"version_id": "789101",
"data": [
{
"review_id": "3",
"rating": 3,
"review": "This app is OK",
"date": "2024-09-01"
}
]
}
]
I can wrangle this into a data.table by doing a bunch of processing and then binding the results together, but I want to know if it’s possible to do this more simply and/or efficiently with the j in data.table.
What I’m doing currently:
reviews <- jsonlite::read_json("reviews.json")
version_ids <- purrr::map_chr(reviews, "version_id")
review_data <- purrr::map(reviews, "data")
cbind(
data.table::data.table(
version_id = rep(version_ids, lengths(review_data))
),
lapply(
review_data,
function(d) {
data.table::data.table(
review_id = purrr::map_chr(d, "review_id"),
rating = purrr::map_int(d, "rating"),
review = purrr::map_chr(d, "review"),
date = purrr::map_chr(d, "date")
)
}
) |>
data.table::rbindlist()
)
#> version_id review_id rating review date
#> 1: 123456 1 5 This app is great 2024-09-01
#> 2: 123456 2 1 This app is terrible 2024-09-01
#> 3: 789101 3 3 This app is OK 2024-09-01
This is the right result, but what I’m hoping to do is something like:
data.table::data.table(
version_id = version_ids,
review_data = review_data
)[
rep(version_id, lengths(review_data)),
.(
review_id = purrr::map_chr(.SD["review_data"], "review_id"),
rating = purrr::map_int(.SD["review_data"], "rating"),
review = purrr::map_chr(.SD["review_data"], "review"),
date = purrr::map_chr(.SD["review_data"], "date")
),
by = version_id
]
But that gives me the error:
Error in `[.data.table`(data.table::data.table(version_id = version_ids, :
When i is a data.table (or character vector), the columns to join by must be specified using 'on=' argument (see ?data.table), by keying x (i.e. sorted, and, marked as sorted, see ?setkey), or by sharing column names between x and i (i.e., a natural join). Keyed joins might have further speed benefits on very large data due to x being sorted in RAM.
I get the same error if I omit the i entirely. Can anyone help with getting this to work?
EDIT using data.table: Rather than trying to get the data table to recurse through itself, set up data table to be long initially, then you can use the rbindlist() function to explode the columns:
data.table::data.table(
version_id = rep(version_ids, map(review_data, length)),
data.table::rbindlist(unlist(review_data, recursive = FALSE))
)
version_id review_id rating review date
1: 123456 1 5 This app is great 2024-09-01
2: 123456 2 1 This app is terrible 2024-09-01
3: 789101 3 3 This app is OK 2024-09-01
Original answer using tidyr in case it’s helpful to others:
If one was using tidyr rather than data.table, the following should work by unnesting the list in stages, first to obtain the version_id index using unnest_wider(), then to create a row for each review (using unnest()) and then by creating columns for the characteristics of each review (using unnest_wider()):
library(tidyverse)
reviews <- jsonlite::read_json("reviews.json")
tibble(reviews) %>%
unnest_wider(col = reviews) %>%
unnest(col = data) %>%
unnest_wider(col = data)
# A tibble: 3 × 5
version_id review_id rating review date
<chr> <chr> <int> <chr> <chr>
1 123456 1 5 This app is great 2024-09-01
2 123456 2 1 This app is terrible 2024-09-01
3 789101 3 3 This app is OK 2024-09-01
The tibble could be converted to a data.table if necessary.
2
The rjsoncons CRAN package can be used to extract individual JSON elements using, e.g., JMESPath, including simple calculations like the length of each ‘data’ element. So
json_file <- "/tmp/reviews.json"
version_id <- j_query(json_file, "[].version_id", as = "R")
lengths <- j_query(json_file, "[].data.length(@)", as = "R")
review_data <- j_query(json_file, "[].data[]", as = "R")
data.table::data.table(
version_id = rep(version_id, lengths),
data.table::rbindlist(review_data)
)
Depending on file size, it might be more efficient to read the data in to R first and then query,
json <- j_query(json_file) # entire file as character(1)
version_id <- j_query(json, "[].version_id, as = "R")
## ...
A less tricky way might be to simply read each record element
data.table::data.table(
version_id = rep(version_id, lengths),
review_id = j_query(json, "[].data[].review_id", as = "R",
rating = j_query(json, "[].data[].rating", as = "R"),
review = j_query(json, "[].data[].review", as = "R"),
date = j_query(json, "[].data[].date", as = "R")
)
As a final example, restructure the data via the JMESPath query, and return an R list for rbindlist()
records <- j_query(
json_file,
"[].{
version_id: version_id,
review_id: data[].review_id,
rating: data[].rating,
review: data[].review,
date: data[].date
}",
as = "R"
)
data.table::rbindlist(records)
Quick and dirty data.table solution:
reviews <- jsonlite::read_json("reviews.json")
n <- sapply(reviews, (x) length(x$data))
DT <- data.table(
version_id = rep(sapply(reviews, (x) x$version_id), n),
lapply(reviews, (x) rbindlist(lapply(x$data, as.data.table))) |>
rbindlist()
)
# version_id review_id rating review date
# <char> <char> <int> <char> <char>
# 1: 123456 1 5 This app is great 2024-09-01
# 2: 123456 2 1 This app is terrible 2024-09-01
# 3: 789101 3 3 This app is OK 2024-09-01
1