RedShift解析嵌套JSON时二级字段查询无结果问题求助

问题原因 #

你的现有写法默认将deviceinfo字段当作数组类型做UNNEST展开，但实际deviceinfo是单个JSON对象，而非多元素数组，交叉连接操作不会返回有效匹配数据，因此查询无结果。

解决方案 #

Redshift 解析嵌套JSON有两种常用方案，可根据你的集群版本选择：

方案1：SUPER类型链式访问（推荐，适合Redshift 最新版本）

直接通过点操作符访问嵌套层级的属性即可，无需额外嵌套子查询做交叉连接：

WITH parsed_json AS (
  -- 仅做一次JSON解析，避免重复计算
  SELECT JSON_PARSE(file_attr) AS json_data
  FROM public.dc_ac_files
)
SELECT
  -- 一级字段提取
  json_data.promptnum,
  json_data.corpuscode,
  json_data.prompttype,
  json_data.skipped,
  json_data.transcription,
  -- deviceinfo二级字段提取
  json_data.deviceinfo.DEVICE_ID AS device_id,
  json_data.deviceinfo.DEVICE_MANUFACTURER AS device_manufacturer,
  json_data.deviceinfo.DEVICE_SERIAL AS device_serial,
  json_data.deviceinfo.DEVICE_DESIGN AS device_design,
  json_data.deviceinfo.DEVICE_MODEL AS device_model,
  json_data.deviceinfo.DEVICE_OS AS device_os,
  json_data.deviceinfo.DEVICE_OS_VERSION AS device_os_version,
  json_data.deviceinfo.DEVICE_CARRIER AS device_carrier,
  json_data.deviceinfo.DEVICE_BATTERY_LEVEL AS device_battery_level,
  json_data.deviceinfo.DEVICE_BATTERY_STATE AS device_battery_state,
  -- 含空格的键名需要用双引号包裹
  json_data.deviceinfo."Current App Version" AS current_app_version,
  json_data.deviceinfo."Current App Build" AS current_app_build
FROM parsed_json;

方案2：兼容旧版本的JSON路径提取

如果你的集群版本不支持SUPER类型的点操作符，可直接使用JSON_EXTRACT_PATH_TEXT函数，传入属性路径即可提取对应值：

SELECT
  JSON_EXTRACT_PATH_TEXT(file_attr, 'promptnum') AS promptnum,
  JSON_EXTRACT_PATH_TEXT(file_attr, 'corpuscode') AS corpuscode,
  JSON_EXTRACT_PATH_TEXT(file_attr, 'prompttype') AS prompttype,
  JSON_EXTRACT_PATH_TEXT(file_attr, 'skipped') AS skipped,
  JSON_EXTRACT_PATH_TEXT(file_attr, 'transcription') AS transcription,
  JSON_EXTRACT_PATH_TEXT(file_attr, 'deviceinfo', 'DEVICE_ID') AS device_id,
  JSON_EXTRACT_PATH_TEXT(file_attr, 'deviceinfo', 'Current App Version') AS current_app_version
  -- 其余字段按以上格式依次添加即可
FROM public.dc_ac_files;

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