How to access values in array column?

Question

I have a Dataframe with one column. Each row of that column has an Array of String values:

Values in my Spark 2.2 Dataframe

["123", "abc", "2017", "ABC"]
["456", "def", "2001", "ABC"]
["789", "ghi", "2017", "DEF"]

org.apache.spark.sql.DataFrame = [col: array]

root
|-- col: array (nullable = true)
|    |-- element: string (containsNull = true)

What is the best way to access elements in the array? For example, I would like extract distinct values in the fourth element for the year 2017 (answer "ABC", "DEF").

Chris · Accepted Answer · 2020-07-08 14:21:55Z

43

Since Spark 2.4.0, there is a new function element_at($array_column, $index).

See Spark docs

edited Jul 8, 2020 at 14:21

Chris

1,46912 silver badges21 bronze badges

answered Feb 4, 2019 at 16:28

pathikrit

33.7k39 gold badges154 silver badges230 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Suraj Nayak Over a year ago

This is very helpful. Saved a lot of time. I was planning to write a UDF to do this which was erroring out. This answer is great.

Alex Moore-Niemi Over a year ago

I wish this existed in Spark 2.3.0. Is there an equivalent?

Shaido Over a year ago

@AlexMoore-Niemi: You can use getItem, see the other answers here.

Juh_ Over a year ago

note: index start at 1

Fermat's Little Student · Accepted Answer · 2017-12-01 01:28:52Z

19

 df.where($"col".getItem(2) === lit("2017")).select($"col".getItem(3))

see getItem from https://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.Column

answered Dec 1, 2017 at 1:28

Fermat's Little Student

6,3697 gold badges53 silver badges70 bronze badges

2 Comments

Ted Over a year ago

Interesting to note is that getItem will simply return NULL if the index is out of range rather than thrown an exception.

Juh_ Over a year ago

and contrary to element_at, index start at 0

Jacek Laskowski · Accepted Answer · 2017-12-01 09:56:18Z

What is the best way to access elements in the array?

Accessing elements in an array column is by getItem operator.

getItem(key: Any): Column An expression that gets an item at position ordinal out of an array, or gets a value by key key in a MapType.

You could also use (ordinal) to access an element at ordinal position.

val ds = Seq(
  Array("123", "abc", "2017", "ABC"),
  Array("456", "def", "2001", "ABC"),
  Array("789", "ghi", "2017", "DEF")).toDF("col")
scala> ds.printSchema
root
 |-- col: array (nullable = true)
 |    |-- element: string (containsNull = true)
scala> ds.select($"col"(2)).show
+------+
|col[2]|
+------+
|  2017|
|  2001|
|  2017|
+------+

It's just a matter of personal choice and taste which approach suits you better, i.e. getItem or simply (ordinal).

And in your case where / filter followed by select with distinct give the proper answer (as @Will did).

parthasarathi0317 · Accepted Answer · 2020-02-19 10:06:41Z

2

you can do something like below

import org.apache.spark.sql.functions._

val ds = Seq(
 Array("123", "abc", "2017", "ABC"),
 Array("456", "def", "2001", "ABC"),
 Array("789", "ghi", "2017", "DEF")).toDF("col")

ds.withColumn("col1",element_at('col,1))
.withColumn("col2",element_at('col,2))
.withColumn("col3",element_at('col,3))
.withColumn("col4",element_at('col,4))
.drop('col)
.show()

+----+----+----+----+
|col1|col2|col3|col4|
+----+----+----+----+
| 123| abc|2017| ABC|
| 456| def|2001| ABC|
| 789| ghi|2017| DEF|
+----+----+----+----+

answered Feb 19, 2020 at 10:06

parthasarathi0317

1778 bronze badges

Collectives™ on Stack Overflow

How to access values in array column?

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related