title: Iterating over for an Array Column with dynamic size in Spark Scala Dataframe
body: <p>I am familiar with this approach - case in point an example from <a href="https://stackoverflow.com/questions/59532225/how-to-obtain-the-average-of-an-array-type-column-in-scala-spark-over-all-row-en/59532979#59532979">How to obtain the average of an array-type column in scala-spark over all row entries per entry?</a> </p>
  
  <pre><code>val array_size = 3
  val avgAgg = for (i &lt;- 0 to array_size -1) yield avg($"value".getItem(i))
  df.select(array(avgAgg: _*).alias("avg_value")).show(false)
  </code></pre>
  
  <p>However, the 3 is hard-coded in reality.</p>
  
  <p>No matter how hard I try not to use an UDF, I cannot do this type of thing dynamically based on the size of an array column already present in the data frame. E.g:</p>
  
  <pre><code>...
  val z =  for (i &lt;- 1 to size($"sortedCol")   ) yield array (element_at($"sortedCol._2", i), element_at($"sortedCol._3", i) )
  ...
  ...
  .withColumn("Z", array(z: _*)  )
  </code></pre>
  
  <p>I am looking as to how this can be done by applying to an existing array col which is variable in length. transform, expr? Not sure.</p>
  
  <p>Full code as per request:</p>
  
  <pre><code>import spark.implicits._
  import org.apache.spark.sql.functions._
  import org.apache.spark.sql.expressions.Window
  
  case class abc(year: Int, month: Int, item: String, quantity: Int)
  
  val df0 = Seq(abc(2019, 1, "TV", 8), 
                abc(2019, 7, "AC", 10),  
                abc(2018, 1, "TV", 2),  
                abc(2018, 2, "AC", 3), 
                abc(2019, 2, "CO", 10)).toDS()
  
  val df1 = df0.toDF()
  // Gen some data, can be done easier, but not the point.
  
  val itemsList= collect_list(struct("month", "item", "quantity"))
  
  // This nn works.
  val nn = 3
  val z =  for (i &lt;- 1 to nn) yield array (element_at($"sortedCol.item", i), element_at($"sortedCol.quantity", i) )
  // But want this.
  //val z =  for (i &lt;- 1 to size($"sortedCol")   ) yield array (element_at($"sortedCol.item", i), element_at($"sortedCol.quantity", i) )
  
  
  val df2 = df1.groupBy($"year")
     .agg(itemsList as "items")
     .withColumn("sortedCol", sort_array($"items", asc = true))  
     .withColumn("S", size($"sortedCol")) // cannot use this either
     .withColumn("Z", array(z: _*)  )
     .drop("items")
     .orderBy($"year".desc)
  df2.show(false)
  // Col Z is the output I want, but not the null value Array 
  </code></pre>
  
  <p><strong>UPD</strong></p>
  
  <p><a href="https://stackoverflow.com/questions/61318531/in-apache-spark-sql-how-to-remove-the-duplicate-rows-when-using-collect-list-in/61319420#61319420">In apache spark SQL, how to remove the duplicate rows when using collect_list in window function?</a> there I solve with a very simple UDF but I was looking for a way without UDF and in particular the dynamic setting of the <code>to value</code> in the <code>for loop</code>. The answer proves that certain constructs are not possible - which was the verification being sort. </p>