Serialize table to nested JSON using Apache Spark SQL

Question

The question is the same as this one but can this:

df.withColumn("VEHICLE",struct("VEHICLENUMBER","CUSTOMERID")).
  select("VEHICLE","ACCOUNTNO"). //only select reqired columns
  groupBy("ACCOUNTNO"). 
  agg(collect_list("VEHICLE").as("VEHICLE")). //for the same group create a list of vehicles
  toJSON. //convert to json
  show(false)

be rewritten with pure SQL? I mean something like that:

val sqlDF = spark.sql("SELECT VEHICLE, ACCOUNTNO as collect_list(ACCOUNTNO) FROM VEHICLES group by ACCOUNTNO)
sqlDF.show()

Does it possible?

Charlie Flowers · Accepted Answer · 2020-02-27 18:55:24Z

The SQL equivalent of your dataframe example would be:

scala> val df = Seq((10003014,"MH43AJ411",20000000),
     |   (10003014,"MH43AJ411",20000001),
     |   (10003015,"MH12GZ3392",20000002)
     | ).toDF("ACCOUNTNO","VEHICLENUMBER","CUSTOMERID").withColumn("VEHICLE",struct("VEHICLENUMBER","CUSTOMERID"))
df: org.apache.spark.sql.DataFrame = [ACCOUNTNO: int, VEHICLENUMBER: string ... 2 more fields]

scala> df.registerTempTable("vehicles")

scala> val sqlDF = spark.sql("SELECT ACCOUNTNO, collect_list(VEHICLE) as ACCOUNT_LIST FROM VEHICLES group by ACCOUNTNO").toJSON
sqlDF: org.apache.spark.sql.Dataset[String] = [value: string]

scala> sqlDF.show(false)
+-----------------------------------------------------------------------------------------------------------------------------------------------+
|value                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
|{"ACCOUNTNO":10003014,"ACCOUNT_LIST":[{"VEHICLENUMBER":"MH43AJ411","CUSTOMERID":20000000},{"VEHICLENUMBER":"MH43AJ411","CUSTOMERID":20000001}]}|
|{"ACCOUNTNO":10003015,"ACCOUNT_LIST":[{"VEHICLENUMBER":"MH12GZ3392","CUSTOMERID":20000002}]}                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------+

Collectives™ on Stack Overflow

Serialize table to nested JSON using Apache Spark SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related