How to use Spark SQL to parse the JSON array of objects

Question

now has JSON data as follows

{"Id":11,"data":[{"package":"com.browser1","activetime":60000},{"package":"com.browser6","activetime":1205000},{"package":"com.browser7","activetime":1205000}]}
{"Id":12,"data":[{"package":"com.browser1","activetime":60000},{"package":"com.browser6","activetime":1205000}]} 
......

This JSON is the activation time of app, the purpose of which is to analyze the total activation time of each app

I use sparK SQL to parse JSON

scala

val sqlContext = sc.sqlContext
val behavior = sqlContext.read.json("behavior-json.log")
behavior.cache()
behavior.createOrReplaceTempView("behavior")
val appActiveTime = sqlContext.sql ("SELECT data FROM behavior") // SQL query
appActiveTime.show (100100) // print dataFrame
appActiveTime.rdd.foreach(println) // print RDD

But the printed dataFrame is like this

.

+----------------------------------------------------------------------+

| data|

+----------------------------------------------------------------------+

| [[60000, com.browser1], [12870000, com.browser]]|

| [[60000, com.browser1], [120000, com.browser]]|

| [[60000, com.browser1], [120000, com.browser]]|

| [[60000, com.browser1], [1207000, com.browser]]|

| [[120000, com.browser]]|

| [[60000, com.browser1], [1204000, com.browser5]]|

| [[60000, com.browser1], [12075000, com.browser]]|

| [[60000, com.browser1], [120000, com.browser]]|

| [[60000, com.browser1], [1204000, com.browser]]|

| [[60000, com.browser1], [120000, com.browser]]|

| [[60000, com.browser1], [1201000, com.browser]]|

| [[1200400, com.browser5]]|

| [[60000, com.browser1], [1200400, com.browser]]|

|[[60000, com.browser1], [1205000, com.browser6], [1205000, com.browser7]]|

.

RDD is like this

.

[WrappedArray ([60000, com.browser1], [60000, com.browser1])]

[WrappedArray ([120000, com.browser])]

[WrappedArray ([60000, com.browser1], [1204000, com.browser5])]

[WrappedArray ([12075000, com.browser], [12075000, com.browser])]

.

And I want to turn the data into

.

Com.browser1 60000

Com.browser1 60000

Com.browser 12075000

Com.browser 12075000

...

.

I want to turn the array elements of each line in RDD into one row. Of course, it can be another structure that is easy to analyze.

Because I only learn spark and Scala a lot, I have try it for a long time but fail, so I hope you can guide me.

koiralo · Accepted Answer · 2018-03-06 15:47:03Z

19

From your given json data you can view the schema of your dataframe with printSchema and use it

appActiveTime.printSchema()
root
 |-- data: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- activetime: long (nullable = true)
 |    |    |-- package: string (nullable = true)

Since you have array you need to explode the data and select the struct field as below

import org.apache.spark.sql.functions._
appActiveTime.withColumn("data", explode($"data"))
       .select("data.*")
       .show(false)

Output:

+----------+------------+
|activetime|     package|
+----------+------------+
|     60000|com.browser1|
|   1205000|com.browser6|
|   1205000|com.browser7|
|     60000|com.browser1|
|   1205000|com.browser6|
+----------+------------+

Hope this helps!

edited Mar 6, 2018 at 15:47

answered Mar 6, 2018 at 15:11

koiralo

23.2k6 gold badges57 silver badges77 bronze badges

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

2 Comments

gonefuture Over a year ago

thank you very munch ! I got it. I will learn more about spark sql.

Anand Over a year ago

@koiralo this explode seems to take so much of time on large data.. any other way to do that

gonefuture · Accepted Answer · 2018-03-07 02:33:26Z

0

with @Shankar Koirala 's help , I learned how to use ' explode' to handle joson array.

  val df = sqlContext.sql("SELECT data FROM behavior")
appActiveTime.select(explode(df("data"))).toDF("data")
  .select("data.package","data.activetime")
  .show(false)

answered Mar 7, 2018 at 2:33

gonefuture

1131 gold badge2 silver badges5 bronze badges

Comments

Prashant Shubham · Accepted Answer · 2022-02-12 16:21:47Z

0

For Apache spark Java we will require to do something like below:

Dataset<Row> dataDF = spark.read()
                .option("header", "true")
                .json("/file_path");
dataDF.createOrReplaceTempView("behavior");
String sqlQuery = "SELECT data from behavior";
Dataset<Row> jsonData = spark.sql(sqlQuery);
snapshot.withColumn("data", explode(jsonData.col("data"))).select("data.*").show();

answered Feb 12, 2022 at 16:21

Prashant Shubham

52610 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to use Spark SQL to parse the JSON array of objects

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related