Read JSON inside a text file using spark and Scala

user_4051090 0
Viewed 10785

I have a text file which has JSON data something like this:

{
  "element" : value,
  "id" : value,
  "total" : []
}
{
  "element" : value,
  "id" : value,
  "total: []
}

All the JSON are new line separated.

I am trying to load all the text files data into a temp view :

sqlContext.read.textFiles("/path").createOrReplaceTempView("result")

val data = sqlContext.sql("select * from result").collect()

Result:

[{"element" : value,"id" : value,"total" : [] }]
[{"element" : value,"id" : value, "total" : []}]

I need to extract the id and the total related to it.

Is there a way in spark to handle this?

apache-spark scala text json
3 Answers

With Spark SQL each line must contain a separate, self-contained valid JSON otherwise the computation fails.

However you can try this 

spark.read.json(spark.sparkContext.wholeTextFiles("path to json").values)

or

spark.read.option("wholeFile", true).option("mode", "PERMISSIVE").json("path to json")

This should convert the json to the dataframe.

user_7241090

Given input file with json data as 

{
  "element" : value,
  "id" : value,
  "total" : []
}
{
  "element" : value,
  "id" : value,
  "total: []
}

which is not a valid json to be converted to a dataframe, so you have to convert the data into valid spark readable json format.

val rdd = sc.wholeTextFiles("path to the json file")

val validJsonRdd = rdd.flatMap(_._2.replace(" ", "").replace("\n", "").replace(":value", ":\"value\"").replace("}{", "}\n{").split("\n"))

Above step would work only if you have value string without inverted commas in element and id fields. Otherwise you can modify it according to your needs.

Next step is to convert into dataframe using sqlcontext.

 val df = sqlContext.read.json(validJsonRdd)

which should result to 

+-------+-----+-----+
|element|id   |total|
+-------+-----+-----+
|value  |value|[]   |
|value  |value|[]   |
+-------+-----+-----+

now you should be able to select id and respective totals and play with them

I hope the answer is helpful

user_58807060

Adding to this, as it took some time to understand it, when querying for something nested inside totals you may have to use the "explode" method:

Dataset<Row> socials = sparkSession
            .read()
            .option("multiLine", true)
            .option("mode", "PERMISSIVE")
            .json(<path to file>).cache();

socials.select(org.apache.spark.sql.functions.explode(socials.col("total")).as("t")).where("t.<some nested column under total> = 'foo'").toJSON().collectAsList();

This is for Java Spark but hope the explode method be of some help.

user_63903680
Related