create a spark dataframe from a nested json file in scala [duplicate]

Question

I have a json file that looks like this

{
"group" : {},
"lang" : [ 
    [ 1, "scala", "functional" ], 
    [ 2, "java","object" ], 
    [ 3, "py","interpreted" ]
]
}

I tried to create a dataframe using

val path = "some/path/to/jsonFile.json"
val df = sqlContext.read.json(path)
df.show()

when I run this I get

df: org.apache.spark.sql.DataFrame = [_corrupt_record: string]

How do we create a df based on contents of "lang" key? I do not care about group{} all I need is, pull data out of "lang" and apply case class like this

case class ProgLang (id: Int, lang: String, type: String )

I have read this post Reading JSON with Apache Spark - `corrupt_record` and understand that each record needs to be on a newline but in my case I cannot change the file structure

Anahcolus · Accepted Answer · 2017-07-19 02:30:23Z

7

The json format is wrong. The the json api of sqlContext is reading it as corrupt record. Correct form is

{"group":{},"lang":[[1,"scala","functional"],[2,"java","object"],[3,"py","interpreted"]]}

and supposing you have it in a file ("/home/test.json"), then you can use following method to get the dataframe you want

import org.apache.spark.sql.functions._
import sqlContext.implicits._

val df = sqlContext.read.json("/home/test.json")

val df2 = df.withColumn("lang", explode($"lang"))
    .withColumn("id", $"lang"(0))
    .withColumn("langs", $"lang"(1))
    .withColumn("type", $"lang"(2))
    .drop("lang")
    .withColumnRenamed("langs", "lang")
    .show(false)

You should have

+---+-----+-----------+
|id |lang |type       |
+---+-----+-----------+
|1  |scala|functional |
|2  |java |object     |
|3  |py   |interpreted|
+---+-----+-----------+

Updated

If you don't want to change your input json format as mentioned in your comment below, you can use wholeTextFiles to read the json file and parse it as below

import sqlContext.implicits._
import org.apache.spark.sql.functions._

val readJSON = sc.wholeTextFiles("/home/test.json")
  .map(x => x._2)
  .map(data => data.replaceAll("\n", ""))

val df = sqlContext.read.json(readJSON)

val df2 = df.withColumn("lang", explode($"lang"))
  .withColumn("id", $"lang"(0).cast(IntegerType))
  .withColumn("langs", $"lang"(1))
  .withColumn("type", $"lang"(2))
  .drop("lang")
  .withColumnRenamed("langs", "lang")

df2.show(false)
df2.printSchema

It should give you dataframe as above and schema as

root
 |-- id: integer (nullable = true)
 |-- lang: string (nullable = true)
 |-- type: string (nullable = true)

edited Jul 19, 2017 at 2:30

answered Jul 19, 2017 at 0:19

Anahcolus

42.1k6 gold badges75 silver badges101 bronze badges

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

6 Comments

devtest13 Over a year ago

Ramesh, I should have been more clear, I cannot change my json file format and it contains just a single json doc i.e. data shared earlier and it has newlines whereas in your case its all in a single line.

Anahcolus Over a year ago

is it in the same format as you pasted in your question? how another record is added in that format then?

devtest13 Over a year ago

yes, format remains same, within .json file it is the "lang" : [ 4, "ruby","interpreted" ] that gets new data

devtest13 Over a year ago

I think I am getting closer, I now have a df with group and lang schema root |-- lang: array (nullable = true) | |-- element: array (containsNull = true) | | |-- element: string (containsNull = true) here's the code

val readJSON = sc.wholeTextFiles("'home/data.json").map(x =>    x._2).map(data => data.replaceAll("""[\n]+""", " "))   val df = sqlContext.read.json(readJSON)      df.printSchema

Anahcolus Over a year ago

yeah I was thinking to use wholeTextFiles as well. You got it right . After that you can just use my solution above. :) the part of creating df2 :)

|

Jacek Laskowski · Accepted Answer · 2017-07-19 08:43:04Z

2

As of Spark 2.2 you can use multiLine option to deal with the case of multi-line JSONs.

scala> spark.read.option("multiLine", true).json("jsonFile.json").printSchema
root
 |-- lang: array (nullable = true)
 |    |-- element: array (containsNull = true)
 |    |    |-- element: string (containsNull = true)

Before Spark 2.2 see How to access sub-entities in JSON file? or Read multiline JSON in Apache Spark.

answered Jul 19, 2017 at 8:43

Jacek Laskowski

75k28 gold badges253 silver badges440 bronze badges

Collectives™ on Stack Overflow

create a spark dataframe from a nested json file in scala [duplicate]

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related