How to get the final element of a hierarchical array in scala and apply aggregate functions on it?

user_10323366 0
Viewed 57

I have a hierarchical array in a dataframe as :

customerId  accounts
IND0002 [["IND0002","ACC0155","323"],["IND0002","ACC0262","60"]]
IND0003 [["IND0003","ACC0235","631"],["IND0003","ACC0486","400"],["IND0003","ACC0540","53"]]
IND0004 [["IND0004","ACC0116","965"]]

I need to extract the last element lowest element from each list in the array Ex : from the 1st row I should get 323,60 and 2nd row should get 631,400,53

I tried using explode function, but its extracting just the first elemnts

customerId  accounts    col
IND0002 [["IND0002","ACC0155","323"],["IND0002","ACC0262","60"]]    ["IND0002","ACC0155","323"]
IND0002 [["IND0002","ACC0155","323"],["IND0002","ACC0262","60"]]    ["IND0002","ACC0262","60"]
IND0003 [["IND0003","ACC0235","631"],["IND0003","ACC0486","400"],["IND0003","ACC0540","53"]]    ["IND0003","ACC0235","631"]
IND0003 [["IND0003","ACC0235","631"],["IND0003","ACC0486","400"],["IND0003","ACC0540","53"]]    ["IND0003","ACC0486","400"]
IND0003 [["IND0003","ACC0235","631"],["IND0003","ACC0486","400"],["IND0003","ACC0540","53"]]    ["IND0003","ACC0540","53"]
IND0004 [["IND0004","ACC0116","965"]]   ["IND0004","ACC0116","965"]

val newDF1 = CustomerAccountOutput.withColumn("accounts", $"accounts"(size($"accounts")).minus(1))

CustomerAccountOutput.select($"customerID",explode($"accounts"))
apache-spark-sql apache-spark explode dataframe scala
2 Answers

Use transform function with a lambda function where for each sub-array you get the last element by using element_at with index -1:

val newDF1 = CustomerAccountOutput.withColumn(
  "new_col", 
  expr("transform(accounts, x -> element_at(x, -1))")
)

newDF1.show(false)

//+----------+--------------------+--------------+
//|customerId|            accounts|       new_col|
//+----------+--------------------+--------------+
//|   IND0002|[[IND0002, ACC015...|     [323, 60]|
//|   IND0003|[[IND0003, ACC023...|[631, 400, 53]|
//|   IND0004|[[IND0004, ACC011...|         [965]|
//+----------+--------------------+--------------+
user_13865510

got satisfactory result by using slice and withcolumn enter image description here

user_103233660
Related