如何在DuckDB SQL中正确访问JSON深层嵌套的competitions字段？

核心问题分析 #

你遇到的空值问题，本质是未正确展开嵌套的competitions数组，或者JSON路径书写错误。DuckDB不会自动解析数组内的嵌套字段，直接访问数组字段会返回数组对象而非单个字段值，导致空值或不符合预期的结果。

假设你的JSON结构示例 #

先明确典型的嵌套赛事数据结构（方便对应解决方案）：

{
  "season": "2024-2025",
  "competitions": [
    {
      "neutralSite": true,
      "conferenceCompetition": false,
      "matchId": "M1001"
    },
    {
      "neutralSite": false,
      "conferenceCompetition": true,
      "matchId": "M1002"
    }
  ]
}

正确访问方法 #

方法1：用unnest展开数组（推荐，适合逐行处理数组元素）

通过unnest将数组拆分为独立行，直接访问嵌套字段：

SELECT
  season,
  comp.neutralSite,
  comp.conferenceCompetition,
  comp.matchId
FROM
  'your_event_data.json',
  unnest(competitions) AS comp;

如果数组可能为空，用LEFT JOIN避免丢失主表记录：

SELECT
  season,
  comp.neutralSite,
  comp.conferenceCompetition
FROM
  'your_event_data.json'
LEFT JOIN unnest(competitions) AS comp ON true;

方法2：用json_extract指定数组路径

如果需要保留数组结构，提取所有元素的字段值为数组：

SELECT
  season,
  -- 提取所有neutralSite值为数组
  json_extract(competitions, '$[*].neutralSite') AS all_neutral_sites,
  -- 提取指定索引的元素字段（比如第一个元素）
  json_extract(competitions, '$[0].conferenceCompetition') AS first_conference_comp
FROM 'your_event_data.json';

关键排查点 #

1. 字段大小写匹配：DuckDB默认大小写敏感，确保查询的字段名和JSON中的完全一致（比如neutralSite≠neutralsite）。
2. 确认字段类型：用DESCRIBE查看表结构，确保competitions是JSON或LIST[STRUCT]类型：

DESCRIBE FROM 'your_event_data.json';

3. 空数组处理：如果competitions字段本身为空数组，返回空值是正常的，可通过COALESCE设置默认值：

SELECT
  season,
  COALESCE(comp.neutralSite, false) AS neutralSite
FROM
  'your_event_data.json'
LEFT JOIN unnest(competitions) AS comp ON true;

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