Parsing JSON file and extracting keys and values using Spark

Question

I'm new to spark. I have tried to parse the below mentioned JSON file in spark using SparkSQL but it didn't work. Can someone please help me to resolve this.

InputJSON:

[{"num":"1234","Projections":[{"Transactions":[{"14:45":0,"15:00":0}]}]}]

Expected output:

1234 14:45 0\n
1234 15:00 0

I have tried with the below code but it did not work

val sqlContext = new SQLContext(sc)
val df = sqlContext.read.json("hdfs:/user/aswin/test.json").toDF();
val sql_output = sqlContext.sql("SELECT num, Projections.Transactions FROM df group by Projections.TotalTransactions ")
sql_output.collect.foreach(println)

Output:

[01532,WrappedArray(WrappedArray([0,0]))]

Can you share what have you already tried? We can help you finding bug in your code. — Mariusz
– Mariusz, Commented Feb 3, 2017 at 6:36
Why are you collecting? Try sql_output.show() for the Dataframe — OneCricketeer
– OneCricketeer, Commented Feb 3, 2017 at 6:55
I have tried with show and got the below response. +-----+--------------------+ | NUM| Transactions| +-----+--------------------+ |01532|[WrappedArray([0,...| +-----+--------------------+ I have no idea how to select the keys like 14:45 in sql query — user3792699
– user3792699, Commented Feb 3, 2017 at 7:08

Mariusz · Accepted Answer · 2017-02-03 07:11:34Z

2

Spark recognizes your {"14:45":0,"15:00":0} map as structure so probably the only way to read your data is to specify schema manually:

>>> from pyspark.sql.types import *
>>> schema = StructType([StructField('num', StringType()), StructField('Projections', ArrayType(StructType([StructField('Transactions', ArrayType(MapType(StringType(), IntegerType())))])))])

Then you can query this temporary table to get results using multiple exploding:

>>> sqlContext.read.json('sample.json', schema=schema).registerTempTable('df')
>>> sqlContext.sql("select num, explode(col) from (select explode(col.Transactions), num from (select explode(Projections), num from df))").show()
+----+-----+-----+
| num|  key|value|
+----+-----+-----+
|1234|14:45|    0|
|1234|15:00|    0|
+----+-----+-----+

answered Feb 3, 2017 at 7:11

Mariusz

14k3 gold badges66 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

Parsing JSON file and extracting keys and values using Spark

InputJSON:

Expected output:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

InputJSON:

Expected output:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related