Reading Json with Spark issue

user_9980649 0
Viewed 44

I am new to Spark I am trying to read json file with Spark. I am getting output as mentioned in "Current Output" where as I need in "Expected Output" format.

Json_file

{"Id":"665cfa8","Attributes":{"attr1":"0","attr2":"60","attr3":"","attr4":"p-queue"},"Channel":"CHAT","Method":"OUT","CTimestamp":"2019-12-31T02:38:11Z","PreviousId":null}

df = spark.read.format(json) \
      .option("sep", ",") \
      .load("Json_file.json")

Current Output

ID, Attributes, Channel, Method, CTimestamp, PreviousId
"665cfa8", {"attr1":"0","attr2":"60","attr3":"","attr4":"p-queue"}, "CHAT", "OUT", "2019-12-31T02:38:11Z", null

Q1- If I want to select the first value from the a Attributes column of the above df say attr1 how do I do that?

Q2 - Expected Output

ID, attr1, attr2, attr3, attr4, channel, method, ctimestamp, previousid
"665cfa8", "0", "60", "", "p-queue", "CHAT", "OUT", "2019-12-31T02:38:11Z", null

I tried to use Explode as stated below:

from pyspark.sql.functions import *
raw_df = df.withColumn("Attribute",explode(col("Attributes")))

I am getting "cannot resolve 'explode(Attributes)' due to data type mismatch: input to function explode should be array or map type, not struct;"

Can anyone please help?

Thanks.

pyspark
2 Answers

explode only works with array and map, while your Attibutes is a struct. You can do something like this

(df
    .select('Attributes.*', '*')
    .drop('Attributes')
    .show()
)

+-----+-----+-----+-------+--------------------+-------+-------+------+----------+
|attr1|attr2|attr3|  attr4|          CTimestamp|Channel|     Id|Method|PreviousId|
+-----+-----+-----+-------+--------------------+-------+-------+------+----------+
|    0|   60|     |p-queue|2019-12-31T02:38:11Z|   CHAT|665cfa8|   OUT|      null|
+-----+-----+-----+-------+--------------------+-------+-------+------+----------+
user_34415100 
from pyspark.sql.functions import *
df.select(col("Id"),
    col("Attributes.*"),
    col("Channel"),
    col("Method"),
    col("CTimestamp"),
    col("PreviousId")).show()
user_99806490
Related