Pyspark dataframe split json column values into top-level multiple columns

Question

I have a json column which can contain any no of key:value pairs. I want to create new top level columns for these key:value pairs. For Eg if I have this data

A                                       B
"{\"C\":\"c\" , \"D\":\"d\"...}"        b

This is the output that i want

B   C   D  ...
b   c   d

There are few questions similar to splitting the coulmns into multiple columns but none are working in this case. Can Anyone please help. Thanks in Advance!

Dose all the json column's value have same schema or contain array? — Zhang Tong
– Zhang Tong, Commented Mar 21, 2017 at 1:39
The json column's value have different schema, contains different key:value pairs. We can use json.loads to parse this column, the column value is in json format. But I want to know how can i create top level columns while parsing this value? — gashu
– gashu, Commented Mar 21, 2017 at 2:55

Garren S · Accepted Answer · 2017-03-21 05:31:34Z

2

You are looking for org.apache.spark.sql.functions.from_json: https://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.functions$@from_json(e:org.apache.spark.sql.Column,schema:String,options:java.util.Map[String,String]):org.apache.spark.sql.Column

Here's the python code commit related to SPARK-17699: https://github.com/apache/spark/commit/fe33121a53384811a8e094ab6c05dc85b7c7ca87

Sample Usage from commit:

    >>> from pyspark.sql.types import *
    >>> data = [(1, '''{"a": 1}''')]
    >>> schema = StructType([StructField("a", IntegerType())])
    >>> df = spark.createDataFrame(data, ("key", "value"))
    >>> df.select(from_json(df.value, schema).alias("json")).collect()
    [Row(json=Row(a=1))]

answered Mar 21, 2017 at 5:31

Garren S

5,8223 gold badges34 silver badges45 bronze badges

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

3 Comments

gashu Over a year ago

Thanks , how can I add multiple columns at the same time , as I have multiple key:value pairs?

Garren S Over a year ago

Try df.select(from_json(df.value, schema), from_json(df.value2, schema2)) ?

Garren S Over a year ago

This may be related to your problem: issues.apache.org/jira/browse/SPARK-19595 - unfortunately if this is a limitation for you, you will have to wait until Spark 2.2 or create a custom solution now.

Collectives™ on Stack Overflow

Pyspark dataframe split json column values into top-level multiple columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related