33. JSON in a column: `from_json` and `to_json`

Often a column arrives as a raw JSON string — an API payload, a Kafka message, a log line. from_json parses it into a real struct you can dot into; to_json does the reverse.

Parsing with from_json

from_json(col, schema) needs a schema describing the JSON's shape. Parse into a struct j, then pull its fields out:

from pyspark.sql.types import StructType, StructField, StringType, DoubleType

raw = spark.createDataFrame(
    [
        ('{"user": "u101", "country": "IN", "amount": 9.99}',),
        ('{"user": "u102", "country": "DE", "amount": 37.0}',),
        ('{"user": "u103", "country": "UK", "amount": 74.5}',),
    ],
    ["payload"],
)

schema = StructType([
    StructField("user", StringType()),
    StructField("country", StringType()),
    StructField("amount", DoubleType()),
])

raw.select(F.from_json("payload", schema).alias("j")).select(
    "j.user", "j.country", "j.amount"
).show()

A field missing from the JSON, or a whole line that won't parse, becomes null — from_json won't crash your job, so check for nulls after parsing.

The schema is the hard part

You must tell from_json the shape. Two shortcuts:

• Nest StructType/ArrayType to match nested JSON.
• F.schema_of_json(F.lit(sample)) infers a schema string from a sample string — handy for exploration, though in production you pin the schema explicitly (same lesson as 0.3: explicit wins).

to_json — the other direction

Turn a struct (or the whole row) back into a JSON string — for writing to a message queue or a text sink:

events.select("event_id", F.to_json("user").alias("user_json")).show(6, truncate=False)

Your turn

Parse the payload JSON into columns and return user, country, and amount. Use the provided schema. Assign to result.

from pyspark.sql.types import StructType, StructField, StringType, DoubleType
raw = spark.createDataFrame(
    [
        ('{"user": "u101", "country": "IN", "amount": 9.99}',),
        ('{"user": "u102", "country": "DE", "amount": 37.0}',),
    ],
    ["payload"],
)
schema = StructType([
    StructField("user", StringType()),
    StructField("country", StringType()),
    StructField("amount", DoubleType()),
])

result = raw  # <- from_json(payload, schema) AS j, then select j.user, j.country, j.amount
result.show()

result = raw.select(F.from_json("payload", schema).alias("j")).select(
    "j.user", "j.country", "j.amount"
)