How to parse nested JSON objects in Spark SQL?

Question

I have a schema as shown below. How can I parse the nested objects?

root
 |-- apps: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- appName: string (nullable = true)
 |    |    |-- appPackage: string (nullable = true)
 |    |    |-- Ratings: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- date: string (nullable = true)
 |    |    |    |    |-- rating: long (nullable = true)
 |-- id: string (nullable = true)

I was trying to treat each json object as a String and parse it using JSONDecoder parser. — None
– None, Commented Apr 29, 2015 at 16:36

Vasilis Vagias · Accepted Answer · 2015-08-14 17:28:56Z

28

Assuming you read in a json file and print the schema you are showing us like this:

DataFrame df = sqlContext.read().json("/path/to/file").toDF();
    df.registerTempTable("df");
    df.printSchema();

Then you can select nested objects inside a struct type like so...

DataFrame app = df.select("app");
        app.registerTempTable("app");
        app.printSchema();
        app.show();
DataFrame appName = app.select("element.appName");
        appName.registerTempTable("appName");
        appName.printSchema();
        appName.show();

edited Aug 14, 2015 at 17:28

answered Aug 14, 2015 at 16:57

Vasilis Vagias

4164 silver badges6 bronze badges

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

3 Comments

Arjit Over a year ago

just to add, above code does not need registerTempTable to work. You need to registerTempTable only when you need to execute spark sql query. Also registerTempTable had been deprecated since Spark 2.0 and had been replaced by createOrReplaceTempView

M.Rez Over a year ago

This is assuming that you know the schema. What if you are not sure about the schema of the nested object? How do you even create the schema of the nested object at all? I kinda asked this question in here too: stackoverflow.com/questions/43438774/…

Paul Over a year ago

I am having the same problem, and this code does not work for me. When I try to select("app.element.appName") (or the analagous fields for my case, I get the error org.apache.spark.sql.AnalysisException: No such struct field element in.... The element field is not present in the original json but is created to represent a jsonarray. but for some reason it isn't finding it

Kristian · Accepted Answer · 2016-01-21 05:13:26Z

5

Try this:

val nameAndAddress = sqlContext.sql("""
    SELECT name, address.city, address.state
    FROM people
""")
nameAndAddress.collect.foreach(println)

Source: https://databricks.com/blog/2015/02/02/an-introduction-to-json-support-in-spark-sql.html

edited Jan 21, 2016 at 5:13

Kristian

22k19 gold badges108 silver badges184 bronze badges

answered May 18, 2015 at 22:27

ben jarman

1,14810 silver badges11 bronze badges

Comments

Michael Mior · Accepted Answer · 2024-11-11 13:05:59Z

3

I am using PySpark, but the logic should be similar. I found this way of parsing my nested JSON useful:

df.select(df.apps.appName.alias("apps_Name"), \
          df.apps.appPackage.alias("apps_Package"), \
          df.apps.Ratings.date.alias("apps_Ratings_date")) \
   .show()

The code could be obviously shortened with a f-string.

edited Nov 11, 2024 at 13:05

Michael Mior

28.9k10 gold badges95 silver badges118 bronze badges

answered Jan 30, 2019 at 9:46

filip stepniak

1091 silver badge6 bronze badges

Comments

Michael Mior · Accepted Answer · 2024-11-11 13:06:35Z

3

Have you tried doing it straight from the SQL query like

Select apps.element.Ratings from yourTableName

This will probably return an array and you can more easily access the elements inside. Also, I use this online JSON viewer when I have to deal with large JSON structures and the schema is too complex.

edited Nov 11, 2024 at 13:06

Michael Mior

28.9k10 gold badges95 silver badges118 bronze badges

answered Sep 4, 2015 at 10:20

Adelina Balasa

911 silver badge6 bronze badges

Comments

Michael Mior · Accepted Answer · 2024-11-11 13:05:18Z

1

var df = spark.read.format("json").load("/path/to/file")
df.createOrReplaceTempView("df");
spark.sql("select apps.element.Ratings from df where apps.element.appName like '%app_name%' ").show()

edited Nov 11, 2024 at 13:05

Michael Mior

28.9k10 gold badges95 silver badges118 bronze badges

answered Mar 19, 2020 at 16:49

Ganesh

7679 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to parse nested JSON objects in Spark SQL?

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related