You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

PySpark读取JSON文件select字段返回null，求修正Schema

avatar
云上老周
2026-8-14
问题分析与解决

问题背景

使用Spark 3.1版本读取JSON文件时，自定义Schema后执行指定SELECT语句，返回的position和title字段为null，需修正Schema并获取正确查询结果。

原定义Schema 

from pyspark.sql.types import StructType, StructField, MapType, StringType, BinaryType

StructType([
        StructField('search_metadata', MapType(StringType(),StringType())),
        StructField('search_parameters', MapType(StringType(),StringType())),
        StructField('search_information', MapType(StringType(),StringType())),
        StructField('local_results',StructType([
                                    StructField('position', StringType(), True),
                                    StructField('title', StringType(), True),
                                    StructField('place_id', StringType(), True),
                                    StructField('data_id', StringType(), True),
                                    StructField('data_cid', StringType(), True),
                                    StructField('reviews_link', StringType(), True),
                                    StructField('photos_link', StringType(), True),
                                    StructField('gps_coordinates', MapType(StringType(),StringType()), True),
                                    StructField('place_id_search', StringType(), True),
                                    StructField('unclaimed_listing', BinaryType(), True),
                                    StructField('type', StringType(), True),
                                    StructField('address', StringType(), True),
                                    StructField('open_state', StringType(), True),
                                    StructField('hours', StringType(), True),
                                    StructField('phone', MapType(StringType(),StringType()), True),
                                    StructField('thumbnail', StringType(), True),
                                        ]), True),
        StructField('serpapi_pagination',MapType(StringType(),StringType())),
        StructField('search_query', StringType(), True),
        ])

对应JSON结构 

[{
    "search_metadata": {
        "id": "63560cab66440a949ade5d72",
        "status": "Success",
        "json_endpoint": "https://serpapi.com/searches/b6986ff9ff715b13/63560cab66440a949ade5d72.json",
        "created_at": "2022-10-24 03:55:23 UTC",
        "processed_at": "2022-10-24 03:55:23 UTC",
        "google_maps_url": "https://www.google.com/maps/search/WH?hl=en",
        "raw_html_file": "https://serpapi.com/searches/b6986ff9ff715b13/63560cab66440a949ade5d72.html",
        "total_time_taken": 1.91
    },
    "search_parameters": {
        "engine": "google_maps",
        "type": "search",
        "q": "WH",
        "google_domain": "google.com",
        "hl": "en"
    },
    "search_information": {
        "local_results_state": "Results for exact spelling",
        "query_displayed": "WH"
    },
    "local_results": [{
            "position": 1,
            "title": "WH International Casting, LLC",
            "place_id": "ChIJh0wvXcu_a4gRWuH-O1ltlPg",
            "data_id": "0x886bbfcb5d2f4c87:0xf8946d593bfee15a",
            "data_cid": "17912061847985381722",
            "reviews_link": "https://serpapi.com/search.json?data_id=0x886bbfcb5d2f4c87%3A0xf8946d593bfee15a&engine=google_maps_reviews&hl=en",
            "photos_link": "https://serpapi.com/search.json?data_id=0x886bbfcb5d2f4c87%3A0xf8946d593bfee15a&engine=google_maps_photos&hl=en",
            "gps_coordinates": {
                "latitude": 38.295865,
                "longitude": -85.73001099999999
            },
            "place_id_search": "https://serpapi.com/search.json?data=%214m5%213m4%211s0x886bbfcb5d2f4c87%3A0xf8946d593bfee15a%218m2%213d38.295865%214d-85.73001099999999&engine=google_maps&google_domain=google.com&hl=en&type=place",
            "unclaimed_listing": true,
            "type": "Warehouse",
            "address": "260 America Pl Dr, Jeffersonville, IN 47130",
            "open_state": "Closed ⋅ Opens 8AM Mon",
            "hours": "Closed ⋅ Opens 8AM Mon",
            "operating_hours": {
                "sunday": "Closed",
                "monday": "8AM–4:30PM",
                "tuesday": "8AM–4:30PM",
                "wednesday": "8AM–4:30PM",
                "thursday": "8AM–4:30PM",
                "friday": "8AM–4:30PM",
                "saturday": "Closed"
            },
            "phone": "(812) 725-8029",
            "thumbnail": "https://lh5.googleusercontent.com/p/AF1QipPWDyyzxp1MG27vv3WVZbzy5WVI-Qh2u2jEDb-C=w122-h92-k-no"
        },
        {
            "position": 2,
            "title": "W.H. Smith Manor",
            "place_id": "ChIJ9584e22DXIgR5w2f2saKBOU",
            "data_id": "0x885c836d7b389ff7:0xe5048ac6da9f0de7",
            "data_cid": "16502467521268354535",
            "reviews_link": "https://serpapi.com/search.json?data_id=0x885c836d7b389ff7%3A0xe5048ac6da9f0de7&engine=google_maps_reviews&hl=en",
            "photos_link": "https://serpapi.com/search.json?data_id=0x885c836d7b389ff7%3A0xe5048ac6da9f0de7&engine=google_maps_photos&hl=en",
            "gps_coordinates": {
                "latitude": 36.581589799999996,
                "longitude": -83.6581731
            },
            "place_id_search": "https://serpapi.com/search.json?data=%214m5%213m4%211s0x885c836d7b389ff7%3A0xe5048ac6da9f0de7%218m2%213d36.581589799999996%214d-83.6581731&engine=google_maps&google_domain=google.com&hl=en&type=place",
            "unclaimed_listing": true,
            "type": "University department",
            "address": "184 Robertson Ave, Harrogate, TN 37752",
            "open_state": "Closed ⋅ Opens 8AM Mon",
            "hours": "Closed ⋅ Opens 8AM Mon",
            "operating_hours": {
                "sunday": "Closed",
                "monday": "8AM–4:30PM",
                "tuesday": "8AM–4:30PM",
                "wednesday": "8AM–4:30PM",
                "thursday": "8AM–4:30PM",
                "friday": "8AM–4:30PM",
                "saturday": "Closed"
            },
            "phone": "(423) 869-3611",
            "website": "http://lmunet.edu/",
            "thumbnail": "https://streetviewpixels-pa.googleapis.com/v1/thumbnail?panoid=mJwpOER-2yIbmD3xSwQ2pQ&cb_client=search.gws-prod.gps&w=80&h=92&yaw=307.97266&pitch=0&thumbfov=100"
        }
    ],
    "serpapi_pagination": {
        "next": "https://serpapi.com/search.json?engine=google_maps&google_domain=google.com&hl=en&q=WH&start=20&type=search"
    },
    "search_query": "WH.json"
}]

问题原因

  1. 核心错误：JSON中的local_results是数组类型，但原Schema将其定义为单个StructType，导致Spark无法正确解析数组结构，直接查询子字段会返回null。
  2. 字段类型不匹配：
    • unclaimed_listing在JSON中是布尔值，原Schema用BinaryType，类型不匹配
    • phone在JSON中是字符串，原Schema定义为MapType，类型不匹配
    • position在JSON中是整数，原Schema用StringType，虽能兼容但不符合数据类型规范
  3. 遗漏字段：JSON中local_results包含operating_hours字段，原Schema未定义，会导致该字段丢失

修正后的Schema 

from pyspark.sql.types import StructType, StructField, MapType, StringType, BooleanType, IntegerType, ArrayType

corrected_schema = StructType([
    StructField('search_metadata', MapType(StringType(), StringType())),
    StructField('search_parameters', MapType(StringType(), StringType())),
    StructField('search_information', MapType(StringType(), StringType())),
    StructField('local_results', ArrayType(StructType([
        StructField('position', IntegerType(), True),
        StructField('title', StringType(), True),
        StructField('place_id', StringType(), True),
        StructField('data_id', StringType(), True),
        StructField('data_cid', StringType(), True),
        StructField('reviews_link', StringType(), True),
        StructField('photos_link', StringType(), True),
        StructField('gps_coordinates', MapType(StringType(), StringType()), True),
        StructField('place_id_search', StringType(), True),
        StructField('unclaimed_listing', BooleanType(), True),
        StructField('type', StringType(), True),
        StructField('address', StringType(), True),
        StructField('open_state', StringType(), True),
        StructField('hours', StringType(), True),
        StructField('operating_hours', MapType(StringType(), StringType()), True),
        StructField('phone', StringType(), True),
        StructField('website', StringType(), True),
        StructField('thumbnail', StringType(), True)
    ])), True),
    StructField('serpapi_pagination', MapType(StringType(), StringType())),
    StructField('search_query', StringType(), True)
])

正确查询代码与结果

由于local_results是数组，需要先使用explode函数展开数组，再查询子字段：

from pyspark.sql.functions import col, explode

# 读取JSON时传入修正后的Schema
df = spark.read.schema(corrected_schema).json("path/to/your/file.json")

# 展开数组并查询字段
df = df.select(explode(col('local_results')).alias('local_result')) \
       .select(
           col('local_result').alias('local_results'),
           col('local_result.position').alias('position'),
           col('local_result.title').alias('title')
       )

df.show(truncate=False)

查询结果 

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------------------------+
|local_results                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |position|title                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------------------------+
|{1, WH International Casting, LLC, ChIJh0wvXcu_a4gRWuH-O1ltlPg, 0x886bbfcb5d2f4c87:0xf8946d593bfee15a, 17912061847985381722, https://serpapi.com/search.json?data_id=0x886bbfcb5d2f4c87%3A0xf8946d593bfee15a&engine=google_maps_reviews&hl=en, https://serpapi.com/search.json?data_id=0x886bbfcb5d2f4c87%3A0xf8946d593bfee15a&engine=google_maps_photos&hl=en, {latitude -> 38.295865, longitude -> -85.73001099999999}, https://serpapi.com/search.json?data=%214m5%213m4%211s0x886bbfcb5d2f4c87%3A0xf8946d593bfee15a%218m2%213d38.295865%214d-85.73001099999999&engine=google_maps&google_domain=google.com&hl=en&type=place, true, Warehouse, 260 America Pl Dr, Jeffersonville, IN 47130, Closed ⋅ Opens 8AM Mon, Closed ⋅ Opens 8AM Mon, {sunday -> Closed, monday -> 8AM–4:30PM, tuesday -> 8AM–4:30PM, wednesday -> 8AM–4:30PM, thursday -> 8AM–4:30PM, friday -> 8AM–4:30PM, saturday -> Closed}, (812) 725-8029, null, https://lh5.googleusercontent.com/p/AF1QipPWDyyzxp1MG27vv3WVZbzy5WVI-Qh2u2jEDb-C=w122-h92-k-no}|1       |WH International Casting, LLC|
|{2, W.H. Smith Manor, ChIJ9584e22DXIgR5w2f2saKBOU, 0x885c836d7b389ff7:0xe5048ac6da9f0de7, 16502467521268354535, https://serpapi.com/search.json?data_id=0x885c836d7b389ff7%3A0xe5048ac6da9f0de7&engine=google_maps_reviews&hl=en, https://serpapi.com/search.json?data_id=0x885c836d7b389ff7%3A0xe5048ac6da9f0de7&engine=google_maps_photos&hl=en, {latitude -> 36.581589799999996, longitude -> -83.6581731}, https://serpapi.com/search.json?data=%214m5%213m4%211s0x885c836d7b389ff7%3A0xe5048ac6da9f0de7%218m2%213d36.581589799999996%214d-83.6581731&engine=google_maps&google_domain=google.com&hl=en&type=place, true, University department, 184 Robertson Ave, Harrogate, TN 37752, Closed ⋅ Opens 8AM Mon, Closed ⋅ Opens 8AM Mon, {sunday -> Closed, monday -> 8AM–4:30PM, tuesday -> 8AM–4:30PM, wednesday -> 8AM–4:30PM, thursday -> 8AM–4:30PM, friday -> 8AM–4:30PM, s
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.08.14 12:55:16