How to explode StructType to rows from json dataframe in Spark rather than to columns

4
votes

I read a nested json with this schema :

 root
 |-- company: struct (nullable = true)
 |    |-- 0: string (nullable = true)
 |    |-- 1: string (nullable = true)
 |    |-- 10: string (nullable = true)
 |    |-- 100: string (nullable = true)
 |    |-- 101: string (nullable = true)
 |    |-- 102: string (nullable = true)
 |    |-- 103: string (nullable = true)
 |    |-- 104: string (nullable = true)
 |    |-- 105: string (nullable = true)
 |    |-- 106: string (nullable = true)
 |    |-- 107: string (nullable = true)
 |    |-- 108: string (nullable = true)
 |    |-- 109: string (nullable = true)

When I try to :

df.select(col("company.*"))

I get every fields of the struct "company" as columns. But I want them as rows. I would like to get a row with the id and the string in another column :

  0        1         10       100      101        102 
"hey"   "yooyo"    "yuyu"    "hey"   "yooyo"    "yuyu"

But rather get something like :

id    name
0     "hey"
1     "yoooyo"
10    "yuuy"
100   "hey"
101   "yooyo"
102    "yuyu"

Thanks in advance for your help,

Tricky

1
scalaapache-sparkapache-spark-sql

1 Answers

9
votes

Try this using union:

val dfExpl = df.select("company.*")

dfExpl.columns
.map(name => dfExpl.select(lit(name),col(name)))
  .reduce(_ union _)
  .show

Or alternatively using array/explode : 

val dfExpl = df.select("company.*")
val selectExpr = dfExpl
  .columns
  .map(name =>
    struct(
      lit(name).as("id"),
      col(name).as("value")
    ).as("col")
  )


dfExpl
  .select(
    explode(array(selectExpr: _*))
  )
  .select("col.*")
  .show()