How to convert a Spark Dataframe to JSONObject

user_3022900 0
Viewed 9821

My goal is to convert a DataFrame to a valid JSONArray of JSONObject.

I'm currently using:

val res = df.toJSON.collect()

But I'm getting an Array[String] - array of JSON-escaped strings, i.e:

["{\"url\":\"http://www.w3schools.com/html/html_form_action.asp?user=123\",\"subnet\":\"32.2.208.1\",\"country\":\"\",\"status_code\":\"200\"}"]

I'm looking for a way to convert those strings into actual JSONObjects, I found a few solution which suggested to find and replace characters, but I'm looking for something cleaner.

I tried to convert each string to a JSONObject using org.json library, but obviously it's not a Serializable Object.

Any suggestion? any fast Scala JSON library that can work?

Or how in general is it suggested to work with the toJSON method.

Update

This is a bit wasteful, but this option works for me:

 val res = df.toJSON.map(new JSONObject(_).toString).collect()

Since JSONObject is not serializable - I can use its toString to get a valid JSON format.

If you still have any suggestion on how I can improve it - please let me know.

apache-spark scala
4 Answers

You can:

  1. collect data frame - you will get Array[Row]
  2. map every row folding it to Map[String,Any] - the result will by Array[Map[String,Any]]
  3. serialize to JSON 
implicit val formats = DefaultFormats

val dataFrame = (1 to 10)
  .map(i => ("value" + i, i))
  .toDF("name", "value")

val maps = dataFrame
  .collect
  .map(
    row => dataFrame
      .columns
      .foldLeft(Map.empty[String, Any])
      (
        (acc, item) => acc + (item -> row.getAs[Any](item))
      )
  )

val json = Serialization.write(maps)

println(json)
user_14694450 
I will show how dataframe converted into Json object list in spark.
I/P: Dataframe
O/P Json : [{ "id":"111","Loc":"Pune"},{"id":"2222","Loc":"Mumbai"}]
Sol:-> 
1] Create  Person POJO having id and loc fields.
2] Suppose dataframe named 'myDF'
3] myDF.collect.foreach { record =>
 val recMap = record.getValuesMap(myDF.columns).toMap[Any, Any]
 val person =new Person
 person.setLoc(recMap("LOC"))
 jsonList.append(person) //List of Person obj
}
val gson = new Gson //GSON lib
jsonStr = gson.toJson(jsonList.asJava)
user_85937520
Related