pyspark convert json to other types

How to convert JSON strings into Map, Array, or Struct Type in PySpark Azure Databricks?

by

Are you looking to find out how to parse a column containing a JSON string into a MapType of PySpark DataFrame in Azure Databricks cloud or maybe you are looking for a solution, to parse a column containing a multi line JSON string into an MapType in PySpark Databricks using the from_json() function? If you are looking for any of these problem solutions, you have landed on the correct page. I will also help you how to use the PySpark from_json() function with multiple examples in Azure Databricks. I will explain it by taking a practical example. So please don’t waste time let’s start with a step-by-step guide to understand how to use the from_json() function in PySpark.

In this blog, I will teach you the following with practical examples:

  • Syntax of from_json() functions
  • Converting JSON to MapType in simple way
  • Converting JSON to MapType using DDL schema
  • Converting JSON to MapType using struct()
  • Converting multiline JSON to MapType

The PySpark function from_json() is used to parses a column containing a JSON string into a MapType in Azure Databricks.

Syntax:

from_json()

What is the syntax of the from_json() function in PySpark Azure Databricks?

The syntax is as follows:

from_json(json_column, schema, options)
Parameter NameRequiredDescription
json_column (str, Column)YesIt represents the column containing JSON values.
schema (str, DataType)YesIt represents the schema of the JSON values.
options (dict)OptionalIt controls the parsing, you can the options by clicking here
Table 1: from_json() Method in PySpark Databricks Parameter list with Details

Apache Spark Official documentation link: from_json()

Create a simple DataFrame

Let’s understand the use of the from_json() function with a variety of examples. Let’s start by creating a DataFrame.

Gentle reminder:

In Databricks,

  • sparkSession made available as spark
  • sparkContext made available as sc

In case, you want to create it manually, use the below code.

from pyspark.sql.session import SparkSession

spark = SparkSession.builder 
    .master("local[*]") 
    .appName("azurelib.com") 
    .getOrCreate()

sc = spark.sparkContext

a) Create manual PySpark DataFrame

json_string = "{'Name':'chevrolet', 'Miles_per_Gallon':18, 'Cylinders':8, 'Displacement':307, 'Horsepower':130, 'Weight_in_lbs':3504, 'Acceleration':12, 'Year':'1970-01-01', 'Origin':'USA'}"

df = spark.createDataFrame([(1, json_string)], schema=["id", "value"])
df.printSchema()
df.show(truncate=False)

"""
root
 |-- id: long (nullable = true)
 |-- value: string (nullable = true)

+---+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|id |value                                                                                                                                                                         |
+---+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|1  |{'Name':'chevrolet', 'Miles_per_Gallon':18, 'Cylinders':8, 'Displacement':307, 'Horsepower':130, 'Weight_in_lbs':3504, 'Acceleration':12, 'Year':'1970-01-01', 'Origin':'USA'}|
+---+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
"""

b) Creating a DataFrame by reading files

Download and use the below source file.

Download Sample Json_Data.csv
# replace the file_path with the source file location which you have downloaded.

df_2 = spark.read.format("csv").option("header", True).load(file_path)
df_2.printSchema()

"""
root
 |-- id: long (nullable = true)
 |-- value: string (nullable = true)
"""

Note: Here, I will be using the manually created DataFrame.

How to convert JSON string column value into MapType of PySpark DataFrame using Azure Databricks?

Let’s see how to convert JSON string column value into MapType of PySpark DataFrame in Azure Databricks using various methods.

Example:

# Method 1:

from pyspark.sql.types import MapType, StringType
from pyspark.sql.functions import from_json

df1 = df.withColumn("value", from_json("value", MapType(StringType(),StringType())).alias("map_col"))

df1.printSchema()
df1.select("map_col.Name", "map_col.Origin", "map_col.Year").show()

"""
Output:

root
 |-- map_col: map (nullable = true)
 |    |-- key: string
 |    |-- value: string (valueContainsNull = true)

+---------+------+----------+
|     Name|Origin|      Year|
+---------+------+----------+
|chevrolet|   USA|1970-01-01|
+---------+------+----------+

"""

# Method 2:

from pyspark.sql.functions import from_json

df1 = df.select(from_json(df.value, "MAP<STRING,STRING>").alias("map_col"))

df1.printSchema()
df1.select("map_col.Name", "map_col.Origin", "map_col.Year").show()

"""
Output:

root
 |-- map_col: map (nullable = true)
 |    |-- key: string
 |    |-- value: string (valueContainsNull = true)

+---------+------+----------+
|     Name|Origin|      Year|
+---------+------+----------+
|chevrolet|   USA|1970-01-01|
+---------+------+----------+

"""

How to convert JSON string column value into StructType using the DDL format schema of PySpark DataFrame in Azure Databricks?

Let’s see how to convert JSON string column value into StructType using the DDL format schema of PySpark DataFrame in Azure Databricks using various methods.

Example:

from pyspark.sql.functions import from_json

ddl_schema = "Name String, Miles_per_Gallon INT, Cylinders INT, Displacement INT, Horsepower INT, Weight_in_lbs INT, Acceleration INT, Year Date, Oring String"

df2 = df.select(from_json("value", ddl_schema).alias("map_col"))
df2.printSchema()
df2.select("map_col.Name", "map_col.Cylinders", "map_col.Horsepower").show()

"""
Output:

root
 |-- map_col: struct (nullable = true)
 |    |-- Name: string (nullable = true)
 |    |-- Miles_per_Gallon: integer (nullable = true)
 |    |-- Cylinders: integer (nullable = true)
 |    |-- Displacement: integer (nullable = true)
 |    |-- Horsepower: integer (nullable = true)
 |    |-- Weight_in_lbs: integer (nullable = true)
 |    |-- Acceleration: integer (nullable = true)
 |    |-- Year: date (nullable = true)
 |    |-- Oring: string (nullable = true)

+---------+---------+----------+
|     Name|Cylinders|Horsepower|
+---------+---------+----------+
|chevrolet|        8|       130|
+---------+---------+----------+

"""

How to convert JSON string column value into StructType using struct() of PySpark DataFrame in Azure Databricks?

Let’s see how to convert JSON string column value into StructType using struct() of PySpark DataFrame in Azure Databricks using various methods.

Example:

from pyspark.sql.types import MapType, StringType, IntegerType, DateType
from pyspark.sql.functions import from_json

schema = StructType([
    StructField("Name", StringType()),
    StructField("Miles_per_Gallon", IntegerType()),
    StructField("Cylinders", IntegerType()),
    StructField("Displacement", IntegerType()),
    StructField("Horsepower", IntegerType()),
    StructField("Weight_in_lbs", IntegerType()),
    StructField("Acceleration", IntegerType()),
    StructField("Year", DateType()),
    StructField("Origin", StringType()),
])

df3 = df.select(from_json("value", schema).alias("map_col"))
df3.printSchema()
df3.select("map_col.Name", "map_col.Acceleration", "map_col.Year").show()

"""
Output:

root
 |-- map_col: struct (nullable = true)
 |    |-- Name: string (nullable = true)
 |    |-- Miles_per_Gallon: integer (nullable = true)
 |    |-- Cylinders: integer (nullable = true)
 |    |-- Displacement: integer (nullable = true)
 |    |-- Horsepower: integer (nullable = true)
 |    |-- Weight_in_lbs: integer (nullable = true)
 |    |-- Acceleration: integer (nullable = true)
 |    |-- Year: date (nullable = true)
 |    |-- Origin: string (nullable = true)

+---------+------------+----------+
|     Name|Acceleration|      Year|
+---------+------------+----------+
|chevrolet|          12|1970-01-01|
+---------+------------+----------+

"""

How to convert multiline JSON string column value into MapType of PySpark DataFrame using Azure Databricks?

Let’s see how to convert multiline JSON string column value into MapType of PySpark DataFrame in Azure Databricks using various methods.

Example:

from pyspark.sql.types import MapType, StringType, IntegerType, DateType
from pyspark.sql.functions import from_json, explode

data = [(1, "[{'Name':'chevrolet chevelle malibu', 'Miles_per_Gallon':18, 'Cylinders':8},{'Name':'buick skylark 320', 'Miles_per_Gallon':15, 'Cylinders':8},{'Name':'plymouth satellite', 'Miles_per_Gallon':18, 'Cylinders':8}]")]

schema = ArrayType(
    StructType([
        StructField("Name", StringType()),
        StructField("Miles_per_Gallon", IntegerType()),
        StructField("Cylinders", IntegerType()),
    ]))

df4 = spark.createDataFrame(data, ("key", "value"))
df4 = df4.select(from_json("value", schema).alias("map_col"))
df4.select("map_col.Name").show(truncate=False)

"""
Output:

+------------------------------------------------------------------+
|Name                                                              |
+------------------------------------------------------------------+
|[chevrolet chevelle malibu, buick skylark 320, plymouth satellite]|
+------------------------------------------------------------------+

"""

I have attached the complete code used in this blog in a notebook format to this GitHub link. You can download and import this notebook in databricks, jupyter notebook, etc.

GitHub Link

When should you use the PySpark from_json() in Azure Databricks?

These could be the possible reasons:

  1. For converting JSON string into StructType
  2. For converting JSON string into ArrayType
  3. For converting JSON string into MapType

Real World Use Case Scenarios for PySpark DataFrame from_json() in Azure Databricks?

Assume that you were given a requirement to convert JSON strings into ArrayType, MapType, and StructType columns. The PySpark from_json() function helps in converting those values into ArrayType, MapType, and StructType.

What are the alternatives to the from_json() function in PySpark Azure Databricks?

The PySpark function from_json() is the only one that helps in converting the JSON strings into ArrayType, MapType, and StructType, and this function is clearly explained with multiple examples in the above section.

Final Thoughts

In this article, we have learned about the PySpark from_json() method of DataFrame in Azure Databricks along with the examples explained clearly. I have also covered different scenarios with practical examples that could be possible. I hope the information that was provided helped in gaining knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Arud Seka Berne S
Arud Seka Berne S

As a big data engineer, I design and build scalable data processing systems and integrate them with various data sources and databases. I have a strong background in Python and am proficient in big data technologies such as Hadoop, Hive, Spark, Databricks, and Azure. My interest lies in working with large datasets and deriving actionable insights to support informed business decisions.