如何在data.table中高效展开嵌套JSON数据？

我有一个JSON格式的数据文件，想要将其展开（unnest）为data.table格式。JSON结构如下：

[
  {
    "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"
      }
    ]
  }
]

我已经能通过分步处理得到正确结果，代码如下：

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

但我想尝试用data.table的j表达式实现更简洁的写法，比如：

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
]

运行后报错：

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参数，仍然会报相同错误，求解决方法。

解决方案 #

方法1：简洁的data.table链式写法 #

可以直接结合rbindlist和分组操作，一步完成展开：

library(data.table)
library(purrr)
library(jsonlite)

reviews <- read_json("reviews.json")
dt <- data.table(
  version_id = map_chr(reviews, "version_id"),
  review_data = map(reviews, "data")
)

# 按version_id分组，将每组的review_data转换为data.table后合并
result <- dt[, rbindlist(map(review_data, as.data.table)), by = version_id]

方法2：利用jsonlite直接转换后展开 #

jsonlite读取数据后直接转成data.table，再通过unnest（需data.table 1.14.0及以上版本）快速展开：

library(data.table)
library(jsonlite)

dt <- as.data.table(read_json("reviews.json"))
result <- dt[, unnest(data)]

方法3：修正你尝试的写法逻辑 #

你之前的写法错误在于i的使用逻辑和.SD的调用方式，调整为按行处理嵌套数据即可：

library(data.table)
library(purrr)

reviews <- read_json("reviews.json")
dt <- data.table(
  version_id = map_chr(reviews, "version_id"),
  review_data = map(reviews, "data")
)

result <- dt[, 
  .(
    review_id = map_chr(review_data[[1]], "review_id"),
    rating = map_int(review_data[[1]], "rating"),
    review = map_chr(review_data[[1]], "review"),
    date = map_chr(review_data[[1]], "date")
  ),
  by = version_id
]

以上三种方法都能得到你需要的展开后data.table结果，其中方法1和方法2的代码最简洁高效。

内容的提问来源于stack exchange，提问作者Hamed