DataFrame to Array of Json(keys,values)

Question

I have a dataframe as below

+-------------+-------------+-------------+
| columnName1 | columnName2 | columnName3 |
+-------------+-------------+-------------+
| 001         | 002         | 003         |
+-------------+-------------+-------------+
| 004         | 005         | 006         |
+-------------+-------------+-------------+

I want to convert to JSON as expected Below Format.

EXPECTED FORMAT

[[{"key":"columnName1","value":"001"},{"key":"columnName2","value":"002"},{"key":"columnName1","value":"003"}],[{"key":"columnName1","value":"004"},{"key":"columnName2","value":"005"},{"key":"columnName1","value":"006"}]]

Thanks in Advance

I can do DF.toJSON.collect().This gives [{"columnName1":"001","columnName2":"002","columnName3":"003"},{"columnName1":"004","columnName2":"005","columnName3":"006"}]

But i need in expected format

koiralo · Accepted Answer · 2019-07-22 12:25:09Z

2

You can manually create a json string from given columns and collect as list as below

val json = df.columns.map(c => concat(
  lit("{\"key\": \""),
  lit(c + "\","),
  lit("\"value\": \""),
  concat(col(c), lit("\"}")))
)

df.select(array(json: _*))
  .collect()
  .map(_.getAs[Seq[String]](0).mkString("[", ", ", "]"))

edited Jul 22, 2019 at 12:25

answered Jul 22, 2019 at 9:45

koiralo

23.2k6 gold badges57 silver badges77 bronze badges

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

2 Comments

Learnis Over a year ago

I tried this.But Still it not formatted Response. Output is coming like

[List({"key":"columnName1","value":"001"},{"key":"columnName2","value":"002"},{"key":"columnName1","value":"003"}),List({"key":"columnName1","value":"004"},{"key":"columnName2","value":"005"},{"key":"columnName1","value":"006"})]

Is it possible to remove the List Key word .?

Learnis Over a year ago

Super @shankar-koirala.Thanks

Vihit Shah · Accepted Answer · 2019-07-22 17:11:50Z

0

The answer already posted seems perfectly fine. I had to perform a bit of a tweak to make it working. Please find below!

val json = df.columns.map(c => concat(
lit("{\"key\": \""),
lit(c + "\","),
lit("\"value\": \""),
concat(col(c), lit("\"}")))
)

val answer = df.select(array(json: _*))
.collect()
.map(_.getAs[Seq[String]](0).mkString("[", ", ", "]")).mkString("[", ", ", "]"))

Check if you find this helpful!

answered Jul 22, 2019 at 17:11

Vihit Shah

3141 silver badge5 bronze badges

Collectives™ on Stack Overflow

DataFrame to Array of Json(keys,values)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related