如何在R中实现与Snowflake等价的JSON解析操作？

我经常使用R与Snowflake，Snowflake里解析JSON很方便。比如对包含user、grouping、json三列的表，用以下SQL可以展平JSON并提取特定元素：

with json_data as (SELECT 'a' as user,  'z' as grouping, '{  "123": {    "info": {      "text": "Hello",    },    "id": "123"  },  "456": {    "info": {      "text": "Good bye",    },    "id": "456"  }}' as json)

select user
, grouping
, flat.key
, flat.value:info:text::string

from json_data
, lateral flatten(try_parse_json(json)) as flat

我已经在R里构造了相同的数据集：

data <- data.frame(user="A", 
grouping="Z", 
json='{
  "123": {
    "info": {
      "text": "Hello",
    },
    "id": "123"
  },
  "456": {
    "info": {
      "text": "Good bye",
    },
    "id": "456"
  }
}')

请问如何用jsonlite或类似包实现和Snowflake等价的表结构？

实现方案 #

可以用jsonlite结合基础R或dplyr完成，核心是解析JSON、展平顶层键值对，再和原表关联，对应Snowflake的lateral flatten逻辑：

方法1：jsonlite + dplyr #

library(jsonlite)
library(dplyr)

# 解析JSON并展平顶层键值对
parsed_flat <- fromJSON(data$json) %>%
  as.data.frame() %>%
  t() %>%
  as.data.frame() %>%
  mutate(key = rownames(.)) %>%
  rename(value = V1) %>%
  mutate(info_text = value$info$text) %>%
  select(key, info_text)

# 合并原表数据得到最终结果
result <- data %>%
  select(user, grouping) %>%
  bind_cols(parsed_flat)

print(result)

方法2：基础R写法 #

library(jsonlite)

# 解析JSON为列表
json_list <- fromJSON(data$json)

# 遍历顶层键，生成展平后的数据集
flat_df <- do.call(rbind, lapply(names(json_list), function(k) {
  data.frame(
    key = k,
    info_text = json_list[[k]]$info$text,
    stringsAsFactors = FALSE
  )
}))

# 合并原表字段
result <- cbind(data[, c("user", "grouping")], flat_df)

print(result)

最终输出 #

两种方法都会得到和Snowflake查询一致的结构：

user grouping key info_text
1    A        Z 123     Hello
2    A        Z 456  Good bye

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