How to access nested schema column?

user_9950894 0
Viewed 517

I have a Kafka streaming source with JSONs, e.g. {"type":"abc","1":"23.2"}.

The query gives the following exception:

org.apache.spark.sql.catalyst.parser.ParseException:  extraneous
input '.1' expecting {<EOF>, .......}
 == SQL == 
person.1

What is the correct syntax to access "person.1"?

I have even changed DoubleType to StringType, but that didn't work either. Example works fine with just by keeping person.type and removing person.1 in selectExpr:

val personJsonDf = inputDf.selectExpr("CAST(value AS STRING)")
val struct = new StructType()
  .add("type", DataTypes.StringType)
  .add("1", DataTypes.DoubleType)
val personNestedDf = personJsonDf
  .select(from_json($"value", struct).as("person"))
val personFlattenedDf = personNestedDf
  .selectExpr("person.type", "person.1")
val consoleOutput = personNestedDf.writeStream
  .outputMode("update")
  .format("console")
  .start()
spark-structured-streaming apache-spark-sql apache-spark
2 Answers

I have solved this problem by using person.*

+-----+--------+
|type | 1      |
+-----+--------+
|abc  |23.2    |
+-----+--------+
user_99508940

Interesting, since select($"person.1") should work fine (but you used selectExpr which could've confused Spark SQL).

StructField(1,DoubleType,true) won't work however since the type should actually be StringType.

Let's see...

$ cat input.json
{"type":"abc","1":"23.2"}

val input = spark.read.text("input.json")
scala> input.show(false)
+-------------------------+
|value                    |
+-------------------------+
|{"type":"abc","1":"23.2"}|
+-------------------------+

import org.apache.spark.sql.types._
val struct = new StructType()
  .add("type", DataTypes.StringType)
  .add("1", DataTypes.StringType)
val q = input.select(from_json($"value", struct).as("person"))
scala> q.show
+-----------+
|     person|
+-----------+
|[abc, 23.2]|
+-----------+

val q = input.select(from_json($"value", struct).as("person")).select($"person.1")
scala> q.show
+----+
|   1|
+----+
|23.2|
+----+
user_13053440
Related