pysparksql从嵌套表达式中的json字段中提取值

{
   "name": "John",
   "age": 30,
   "address": {
      "city": "New York",
      "state": "NY",
      "country": "USA"
   }
}

get_json_object(json, path)

from pyspark.sql import SparkSession
from pyspark.sql.functions import expr

# 创建SparkSession
spark = SparkSession.builder.getOrCreate()

# 创建包含JSON数据的DataFrame
data = [
    ('{"name": "John", "age": 30, "address": {"city": "New York", "state": "NY", "country": "USA"}}',),
    ('{"name": "Jane", "age": 25, "address": {"city": "San Francisco", "state": "CA", "country": "USA"}}',),
]
df = spark.createDataFrame(data, ['json_col'])

# 使用get_json_object函数提取嵌套字段的值
df = df.withColumn('city', expr("get_json_object(json_col, '$.address.city')"))
df = df.withColumn('state', expr("get_json_object(json_col, '$.address.state')"))
df = df.withColumn('country', expr("get_json_object(json_col, '$.address.country')"))

# 显示提取后的结果
df.show(truncate=False)