Hello All,

 

 

 

I am processing a nested complex Json and below is the schema for it.

root

|-- businessEntity: array (nullable = true)

|    |-- element: struct (containsNull = true)

|    |    |-- payGroup: array (nullable = true)

|    |    |    |-- element: struct (containsNull = true)

|    |    |    |    |-- reportingPeriod: struct (nullable = true)

|    |    |    |    |    |-- worker: array (nullable = true)

|    |    |    |    |    |    |-- element: struct (containsNull = true)

|    |    |    |    |    |    |    |-- category: string (nullable = true)

|    |    |    |    |    |    |    |-- person: struct (nullable = true)

|    |    |    |    |    |    |    |-- tax: array (nullable = true)

|    |    |    |    |    |    |    |    |-- element: struct (containsNull = true)

|    |    |    |    |    |    |    |    |    |-- code: string (nullable = true)

|    |    |    |    |    |    |    |    |    |-- qtdAmount: double (nullable = true)

|    |    |    |    |    |    |    |    |    |-- ytdAmount: double (nullable =

My requirement is to create a hashmap with code concatenated with qtdAmount as key and value of qtdAmount as value. Map.put(code + "qtdAmount" , qtdAmount). How can i do this with spark.

I tried with below shell commands.

import org.apache.spark.sql._

val sqlcontext = new SQLContext(sc)

val cdm = sqlcontext.read.json("/user/edureka/CDM/cdm.json")

val spark = SparkSession.builder().appName("SQL").config("spark.some.config.option","some-vale").getOrCreate()

cdm.createOrReplaceTempView("CDM")

val sqlDF = spark.sql("SELECT businessEntity[0].payGroup[0] from CDM").show()

val address = spark.sql("SELECT businessEntity[0].payGroup[0].reportingPeriod.worker[0].person.address from CDM as address")

val worker = spark.sql("SELECT businessEntity[0].payGroup[0].reportingPeriod.worker from CDM")

val tax = spark.sql("SELECT businessEntity[0].payGroup[0].reportingPeriod.worker[0].tax from CDM")

val tax = sqlcontext.sql("SELECT businessEntity[0].payGroup[0].reportingPeriod.worker[0].tax from CDM")

val codes = tax.select(expode(tax("code"))

scala> val codes = tax.withColumn("code",explode(tax("tax.code"))).withColumn("qtdAmount",explode(tax("tax.qtdAmount"))).withColumn("ytdAmount",explode(tax("tax.ytdAmount")))

 

 

i am trying to get all the codes and qtdAmount into a map. But i am not getting it. Using multiple explode statements for a single DF, is producing Cartesian product of the elements.

Could someone please help on how to parse the json of this much complex in spark.

 

 

Thanks,

Sivaram

 


This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, notify the sender immediately by return email and delete the message and any attachments from your system.