Spark, adding new field in array of arrays

Question

I am new to Spark, having the following df schema (part of it).

root
|-- value: binary (nullable = false)
|-- event: struct (nullable = false)
|    |-- eventtime: struct (nullable = true)
|    |    |-- seconds: long (nullable = true)
|    |    |-- ms: float (nullable = true)
|    |-- fault: struct (nullable = true)
|    |    |-- collections: struct (nullable = true)
|    |    |    |-- snapshots: array (nullable = false) --> ** FIRST LEVEL ARRAY (or array of arrays) **
|    |    |    |    |-- element: struct (containsNull = false)
|    |    |    |    |    |-- ringbuffer: struct (nullable = true)
|    |    |    |    |    |    |-- columns: array (nullable = false) --> ** SECOND LEVEL ARRAY **
|    |    |    |    |    |    |    |-- element: struct (containsNull = false)
|    |    |    |    |    |    |    |    |-- doubles: struct (nullable = true)
|    |    |    |    |    |    |    |    |    |-- values: array (nullable = false)
|    |    |    |    |    |    |    |    |    |    |-- element: float (containsNull = false)
....................................
..........................

I am able to add a new field under fault with following code and the new field comp_id appears at the same level with collections.

df.withColumn("event", col("event").withField("fault.comp_id", lit(1234)))

How can I add a new field in array of arrays. For example, adding a new test_field under columns? I tried to get in arrays by defining the first index 0

df.withColumn("event",col("event").withField("fault.collections.snapshots.0.ringbuffer.columns.0.test_field", lit("test_value")))

But got this error

org.apache.spark.sql.catalyst.parser.ParseException: mismatched input '.0' expecting {, '.', '-'}(line 1, pos 25)

== SQL ==
fault.snapshots.snapshots.0.ringbuffer.columns.0.test_field
-------------------------^^^
  at org.apache.spark.sql.catalyst.parser.ParseException.withCommand(ParseDriver.scala:255)
  at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:124)
  at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parseMultipartIdentifier(ParseDriver.scala:61)
  at org.apache.spark.sql.catalyst.expressions.UpdateFields$.nameParts(complexTypeCreator.scala:693)
  at org.apache.spark.sql.catalyst.expressions.UpdateFields$.apply(complexTypeCreator.scala:701)
  at org.apache.spark.sql.Column.withField(Column.scala:927)
  at org.apache.spark.sql.Dataset.transform(Dataset.scala:2751)
  at org.apache.spark.sql.Dataset.transform(Dataset.scala:2751)

So desired schema would be like following.

root
|-- value: binary (nullable = false)
|-- event: struct (nullable = false)
|    |-- eventtime: struct (nullable = true)
|    |    |-- seconds: long (nullable = true)
|    |    |-- ms: float (nullable = true)
|    |-- fault: struct (nullable = true)
|    |    |-- collections: struct (nullable = true)
|    |    |    |-- snapshots: array (nullable = false) --> ** FIRST LEVEL ARRAY (or array of arrays) **
|    |    |    |    |-- element: struct (containsNull = false)
|    |    |    |    |    |-- ringbuffer: struct (nullable = true)
|    |    |    |    |    |    |-- columns: array (nullable = false) --> ** SECOND LEVEL ARRAY **
|    |    |    |    |    |    |    |-- element: struct (containsNull = false)
|    |    |    |    |    |    |    |    |-- doubles: struct (nullable = true)
|    |    |    |    |    |    |    |    |    |-- values: array (nullable = false)
|    |    |    |    |    |    |    |    |    |    |-- element: float (containsNull = false)
|    |    |    |    |    |    |    |    |-- test_field: string (nullable = true)

Spark, adding new field in array of arrays

0 Answers