如何用R解析含多嵌套键的JSON列？新手技术求助

Hey there! Let's work through your JSON parsing problem step by step—your scenario has a couple of specific quirks with the factor type from ODBC and your two desired outputs, so we'll tackle each part clearly.

First: Fix the Factor to Character Issue #

First off, I noticed a small variable name mismatch in your code (orgtc vs orgjf)—let's get that sorted first. The reason your TCbigdata column is coming through as a factor is because older ODBC connections default to converting text columns to factors. You can fix this two ways:

1. Prevent it at the query stage (best practice):

   # Add stringsAsFactors = FALSE to sqlQuery to get character columns directly
   orgtc <- sqlQuery(channel1,
                     'SELECT idMemberInfo,memberid, refbizid, crttime, TCbigdata FROM tcbiz_fq_rcs_data.MemberInfo ',
                     stringsAsFactors = FALSE)
   

2. Convert after the fact if you already have the data:

   orgtc$TCbigdata <- as.character(orgtc$TCbigdata)
   

Solution 1: Flatten All Nested Fields into One Dataset #

You want to strip out the parent keys (hotelgroup, visa, etc.) and have columns like g_orders, v_orders directly. We'll use jsonlite for parsing and purrr/dplyr to flatten and combine everything, including your original non-JSON columns (like idMemberInfo):

library(jsonlite)
library(purrr)
library(dplyr)

# Function to flatten a single JSON object (remove parent keys)
flatten_single_json <- function(json_str) {
  json_obj <- fromJSON(json_str, simplifyDataFrame = FALSE)
  # Remove memberid since you already have it in your original table
  json_obj <- json_obj[names(json_obj) != "memberid"]
  # Flatten all nested lists into key-value pairs
  flatten(json_obj) %>% as.data.frame(stringsAsFactors = FALSE)
}

# Apply the function to every row's JSON string, then combine with original columns
flattened_full_df <- map_dfr(orgtc$TCbigdata, flatten_single_json)
final_flattened <- cbind(orgtc %>% select(-TCbigdata), flattened_full_df)

This will automatically handle cases where some rows might be missing certain fields by filling in NA values.

Solution 2: Split into Separate Datasets (hotelgroup, visa, etc.) #

For this, we'll extract each nested group into its own table, and keep the original identifiers (idMemberInfo, memberid) so you can link them back to your main data:

# Get the list of group keys from the first JSON entry (assumes all JSONs have the same structure)
sample_json <- fromJSON(orgtc$TCbigdata[1], simplifyDataFrame = FALSE)
group_keys <- names(sample_json)[names(sample_json) != "memberid"]

# Create a list to store each separate dataset
group_tables <- list()

# Loop through each group key to build individual tables
for (key in group_keys) {
  # Extract the nested group data from every row
  group_data <- map_dfr(orgtc$TCbigdata, function(json_str) {
    json_obj <- fromJSON(json_str, simplifyDataFrame = FALSE)
    as.data.frame(json_obj[[key]], stringsAsFactors = FALSE)
  })
  # Add the identifier columns from the original table
  group_data <- cbind(orgtc %>% select(idMemberInfo, memberid), group_data)
  # Store the table in the list using the group name as the key
  group_tables[[key]] <- group_data
}

# Access individual tables like this:
# group_tables$hotelgroup
# group_tables$visa
# group_tables$callcentertel

Quick Note on Your Previous Attempts #

When you used fromJSON(orgjf$JFMemberPortrait, simplifyDataFrame = FALSE), you got a list of lists—this is correct, but we needed to add the flattening and combining steps to get your desired outputs. jsonlite is actually the right tool here; the missing piece was handling the nested structure and merging with your original data.

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