如何在R脚本中过滤缺失的JSON monitoringSystems元素

原代码核心问题 #

1. exists("monitoringSystems")是检查R全局环境中是否存在该变量，不是检查JSON数据内的字段，完全用错了场景。
2. 当目标字段不存在时，代码无返回值，导致map()结果混入NULL，后续unnest操作报错。
3. enter_object(monitoringSystems)缺少引号，会被当作R变量而非JSON字段名。

修正后的代码实现 #

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

get_monitor_data <- function(query) {
  # 发送请求并检查状态码
  response <- GET(query)
  warn_for_status(response)
  
  # 请求失败时返回结构一致的空数据框
  if (response$status_code != 200) {
    return(tibble(
      LOC_NAME = character(),
      SYS_ID = character(),
      SYS_TYPE_CODE = character(),
      COMPONENT_ID = character(),
      SERIAL_NUM = character(),
      MANUFACTURER = character(),
      MODEL_VERSION = character(),
      COMPONENT_TYPE_CODE = character(),
      ACQ_CODE = character(),
      BASIS_CODE = character(),
      SYS_BEGIN_DATE_HOUR = character(),
      SYS_END_DATE_HOUR = character(),
      COMPONENT_BEGIN_DATE_HOUR = character(),
      COMPONENT_END_DATE_HOUR = character(),
      SYSTEM_TYPE_DESCRIPTION = character(),
      SYSTEM_DESIGNATION_CODE_DESC = character(),
      ACQ_CODE_DESCRIPTION = character(),
      COMPONENT_TYPE_CODE_DESCRIPTION = character(),
      BASIS_CODE_DESC = character()
    ))
  }
  
  # 直接解析JSON为R列表，避免字符串处理的麻烦
  mon_json <- content(response, as = "parsed")
  locations <- mon_json$data$monitoringLocations
  
  # 遍历每个监测点，有数据就提取，无数据就返回带NA的空行
  map_dfr(locations, function(loc) {
    if (!is.null(loc$monitoringSystems) && length(loc$monitoringSystems) > 0) {
      loc$monitoringSystems %>%
        mutate(LOC_NAME = loc$locName) %>%
        select(
          LOC_NAME = LOC_NAME,
          SYS_ID = sysId,
          SYS_TYPE_CODE = sysTypeCode,
          COMPONENT_ID = componentId,
          SERIAL_NUM = serialNum,
          MANUFACTURER = manufacturer,
          MODEL_VERSION = modelVersion,
          COMPONENT_TYPE_CODE = componentTypeCode,
          ACQ_CODE = acqCode,
          BASIS_CODE = basisCode,
          SYS_BEGIN_DATE_HOUR = sysBeginDateHour,
          SYS_END_DATE_HOUR = sysEndDateHour,
          COMPONENT_BEGIN_DATE_HOUR = componentBeginDateHour,
          COMPONENT_END_DATE_HOUR = componentEndDateHour,
          SYSTEM_TYPE_DESCRIPTION = systemTypeDescription,
          SYSTEM_DESIGNATION_CODE_DESC = systemDesignationCodeDesc,
          ACQ_CODE_DESCRIPTION = acqCodeDescription,
          COMPONENT_TYPE_CODE_DESCRIPTION = componentTypeCodeDescription,
          BASIS_CODE_DESC = basisCodeDesc
        )
    } else {
      tibble(
        LOC_NAME = loc$locName,
        SYS_ID = NA_character_,
        SYS_TYPE_CODE = NA_character_,
        COMPONENT_ID = NA_character_,
        SERIAL_NUM = NA_character_,
        MANUFACTURER = NA_character_,
        MODEL_VERSION = NA_character_,
        COMPONENT_TYPE_CODE = NA_character_,
        ACQ_CODE = NA_character_,
        BASIS_CODE = NA_character_,
        SYS_BEGIN_DATE_HOUR = NA_character_,
        SYS_END_DATE_HOUR = NA_character_,
        COMPONENT_BEGIN_DATE_HOUR = NA_character_,
        COMPONENT_END_DATE_HOUR = NA_character_,
        SYSTEM_TYPE_DESCRIPTION = NA_character_,
        SYSTEM_DESIGNATION_CODE_DESC = NA_character_,
        ACQ_CODE_DESCRIPTION = NA_character_,
        COMPONENT_TYPE_CODE_DESCRIPTION = NA_character_,
        BASIS_CODE_DESC = NA_character_
      )
    }
  })
}

# 执行请求并整理数据
responses <- queries %>%
  mutate(response = map(query, get_monitor_data))

unit_monitor_data <- unnest(responses, cols = c(response))

unit_monitor_data

关键改进点 #

• 用is.null(loc$monitoringSystems)正确检查JSON字段是否存在，替代错误的exists函数。
• 确保所有分支都返回结构完全一致的data.frame（含空行或NA），避免map和unnest因数据类型不一致报错。
• 改用jsonlite直接解析JSON为R列表，比字符串处理更直观易维护。
• 将get_monitor_data改为处理单个请求，配合外部map循环，逻辑更清晰。

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