PySpark DataFrame扁平化报错求助：嵌套JSON转扁平结构

问题分析 #

你遇到的AnalysisException通常源于数组展开时的字段引用错误，或是未正确处理多层嵌套结构。针对包含data数组，且数组内awayPlayers、homePlayers同为数组的结构，需分步骤逐层展开。

解决方案步骤 #

假设你的DataFrame结构如下（简化示例）：

root
 |-- data: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- awayPlayers: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- playerId: string (nullable = true)
 |    |    |    |    |-- name: string (nullable = true)
 |    |    |-- homePlayers: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- playerId: string (nullable = true)
 |    |    |    |    |-- name: string (nullable = true)
 |    |    |-- matchId: string (nullable = true)

1. 展开最外层data数组 #

用explode函数将data数组的元素转为单独行：

from pyspark.sql.functions import explode

df_exploded_data = df.withColumn("data_item", explode("data")).drop("data")

2. 分别处理主客队球员数组 #

直接同时展开两个数组会生成笛卡尔积，不符合常规业务需求，建议按主客队标识分开处理：

处理客队球员

from pyspark.sql.functions import lit

df_away = df_exploded_data.withColumn("player", explode("data_item.awayPlayers")) \
    .withColumn("team_type", lit("away")) \
    .select(
        "data_item.matchId",
        "team_type",
        "player.playerId",
        "player.name"
    )

处理主队球员

df_home = df_exploded_data.withColumn("player", explode("data_item.homePlayers")) \
    .withColumn("team_type", lit("home")) \
    .select(
        "data_item.matchId",
        "team_type",
        "player.playerId",
        "player.name"
    )

3. 合并主客队数据 #

将两个DataFrame合并，得到最终扁平化结果：

final_df = df_away.unionByName(df_home)
final_df.show()

常见错误排查 #

• 若出现AnalysisException: cannot resolve 'xxx' given input columns，检查字段名拼写，尤其嵌套结构的引用路径（比如展开data后需用data_item.awayPlayers而非data.awayPlayers）。
• 避免同时对两个数组使用explode，否则会生成awayPlayers长度 × homePlayers长度的冗余行。

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