Databricks - explode JSON from SQL column with PySpark

Question

New to Databricks. Have a SQL database table that I am creating a dataframe from. One of the columns is a JSON string. I need to explode the nested JSON into multiple columns. Have used this post and this post to get me to where I am at now.

Example JSON:

{ "Module": { "PCBA Serial Number": "G7456789", "Manufacturing Designator": "DISNEY", "Firmware Version": "0.0.0", "Hardware Revision": "46858", "Manufacturing Date": "10/17/2018 4:04:25 PM", "Test Result": "Fail", "Test Start Time": "10/22/2018 6:14:14 AM", "Test End Time": "10/22/2018 6:16:11 AM" }

Code so far:

    #define schema 
         schema = StructType(
          [
            StructField('Module',ArrayType(StructType(Seq
              StructField('PCBA Serial Number',StringType,True),
              StructField('Manufacturing Designator',StringType,True),
              StructField('Firmware Version',StringType,True),
              StructField('Hardware Revision',StringType,True),
              StructField('Test Result',StringType,True),
              StructField('Test Start Time',StringType,True),
              StructField('Test End Time',StringType,True))), True) ,True),
            StructField('Test Results',StringType(),True),
            StructField('HVM Code Errors',StringType(),True)
          ]

#use from_json to explode json by applying it to column
        df.withColumn("ActivityName", from_json("ActivityName", schema))\
            .select(col('ActivityName'))\
            .show()

Error:

    SyntaxError: invalid syntax
  File "<command-1632344621139040>", line 10
    StructField('PCBA Serial Number',StringType,True),
              ^
SyntaxError: invalid syntax

notNull · Accepted Answer · 2020-03-31 02:05:27Z

1

As you are using pyspark then types should be StringType() instead of StringType and remove Seq replace it with []

schema = StructType([StructField('Module',ArrayType(StructType([
    StructField('PCBA Serial Number',StringType(),True),
    StructField('Manufacturing Designator',StringType(),True),
    StructField('Firmware Version',StringType(),True),
    StructField('Hardware Revision',StringType(),True),
    StructField('Test Result',StringType(),True),
    StructField('Test Start Time',StringType(),True),
    StructField('Test End Time',StringType(),True)])), True),
StructField('Test Results',StringType(),True),
StructField('HVM Code Errors',StringType(),True)])

answered Mar 31, 2020 at 2:05

notNull

31.8k4 gold badges41 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

notNull Over a year ago

@N.Fisher, Did the answer helped?

N.Fisher Over a year ago

This did help! I confused my syntax switching between Scala and Python

Collectives™ on Stack Overflow

Databricks - explode JSON from SQL column with PySpark

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related