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) 

Answer 1

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(); 

Answer 2

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