spark sql nested JSON with filed name number ParseException

Question

I have nested JSON in my Kafka topic which I consume using spark streaming.

{"x" : { "y" : "CLASSIC", "z" : { "100" : { "user" : 0.0, "AA" : 0, "BB" : 0, "CC" : 2 } }

when I try to spark.sql(s"""select x.y.z.100.user from $tableInMemory """) Im geeting : org.apache.spark.sql.catalyst.parser.ParseException: extraneous input '.100' expecting {, '(', ',', '.',

however when I try without spark sql: myDataFrame.select("x.y.z.100.user").show(false) It's working. Any Idea???

Answer 1

It is not a valid SQL document:

• It is missing opening curly bracket ({).
• It has extraneous , after nested CC field.