Reading Nested JSON via Spark SQL - [AnalysisException] cannot resolve Column

Question

I have a JSON data like this: ``` { "parent":[ { "prop1":1.0, "prop2":"C", "children":[ { "child_prop1":[ "3026" ] } ] } ] } ``` After reading data from Spark I get following schema: ``` val df = spark.read.json("test.json") ``` ``` df.printSchema root |-- parent: array (nullable = true) | |-- element: struct (containsNull = true) | | |-- children: array (nullable = true) | | | |-- element: struct (containsNull = true) | | | | |-- child_prop1: array (nullable = true) | | | | | |-- element: string (containsNull = true) | | |-- prop1: double (nullable = true) | | |-- prop2: string (nullable = true) ``` Now, I want to select `child_prop1` from `df`. But when I try to select it I get `org.apache.spark.sql.AnalysisException`. Something like this: ``` df.select("parent.children.child_prop1") ``` ``` org.apache.spark.sql.AnalysisException: cannot resolve '`parent`.`children`['child_prop1']' due to data type mismatch: argument 2 requires integral type, however, ''child_prop1'' is of string ty…

Accepted Answer

Your Json is a valid json which and I think you don't need to change your input data.

Use explode to get the data as

```
import org.apache.spark.sql.functions.explode

val data = spark.read.json("src/test/java/data.json")
val child = data.select(explode(data("parent.children"))).toDF("children")

child.select(explode(child("children.child_prop1"))).toDF("child_prop1").show()
```

If you can change the input data you can follow @ramesh suggestions

Reading Nested JSON via Spark SQL - [AnalysisException] cannot resolve Column

Solution

Problem