PySpark DataFrame - Extract JSON Value using get_json_object Function

Code description

PySpark SQL functions get_json_object can be used to extract JSON values from a JSON string column in Spark DataFrame. This is equivalent as using Spark SQL directly: Spark SQL - Extract Value from JSON String.

Syntax of this function looks like the following:

pyspark.sql.functions.get_json_object(col, path)

The first parameter is the JSON string column name in the DataFrame and the second is the JSON path.

This code snippet shows you how to extract JSON values using JSON path. If you need to extract complex JSON documents like JSON arrays, you can follow this article - PySpark: Convert JSON String Column to Array of Object (StructType) in DataFrame.

Output

StructType([StructField('id', LongType(), True), StructField('json_col', StringType(), True), StructField('ATTR_INT_0', StringType(), True), StructField('ATTR_DATE_1', StringType(), True)])

+---+--------------------+----------+-----------+
| id|            json_col|ATTR_INT_0|ATTR_DATE_1|
+---+--------------------+----------+-----------+
|  1|[{"Attr_INT":1, "...|         1| 2022-01-01|
+---+--------------------+----------+-----------+

Code snippet

from pyspark.sql import SparkSession
from pyspark.sql.functions import get_json_object

app_name = "PySpark get_json_object sql functions"
master = "local"

spark = SparkSession.builder \
    .appName(app_name) \
    .master(master) \
    .getOrCreate()

spark.sparkContext.setLogLevel("WARN")

json_str = """[{"Attr_INT":1, "ATTR_DOUBLE":10.201, "ATTR_DATE": "2021-01-01"},
{"Attr_INT":2, "ATTR_DOUBLE":20.201, "ATTR_DATE": "2022-01-01"}]"""

# Create a DataFrame
df = spark.createDataFrame(
    [[1, json_str]], ['id', 'json_col'])

# Extract JSON values
df = df.withColumn('ATTR_INT_0',
                   get_json_object('json_col', '$[0].Attr_INT'))
df = df.withColumn('ATTR_DATE_1',
                   get_json_object('json_col', '$[1].ATTR_DATE'))
print(df.schema)
df.show()