How to filter a struct array in a spark dataframe?

Question

I have the following code and outputs.

import org.apache.spark.sql.functions.{collect_list, struct}
import sqlContext.implicits._

val df = Seq(
  ("john", "tomato", 1.99),
  ("john", "carrot", 0.45),
  ("bill", "apple", 0.99),
  ("john", "banana", 1.29),
  ("bill", "taco", 2.59)
).toDF("name", "food", "price")

df.groupBy($"name")
  .agg(collect_list(struct($"food", $"price")).as("foods"))
  .show(false)

df.printSchema

Output and Schema:

+----+---------------------------------------------+
|name|foods                                        |
+----+---------------------------------------------+
|john|[[tomato,1.99], [carrot,0.45], [banana,1.29]]|
|bill|[[apple,0.99], [taco,2.59]]                  |
+----+---------------------------------------------+

root
 |-- name: string (nullable = true)
 |-- foods: array (nullable = false)
 |    |-- element: struct (containsNull = false)
 |    |    |-- food: string (nullable = true)
 |    |    |-- price: double (nullable = false)

I want to filter based on df("foods.price") > 1.00. How do I filter this to get the output below?

+----+---------------------------------------------+
|name|foods                                        |
+----+---------------------------------------------+
|john|[[banana,1.29], [tomato,1.99]]               |
|bill|[[[taco,2.59]]                               |
+----+---------------------------------------------+

I have tried df.filter($"foods.food" > 1.00), but this does not work as I'm getting an error. Anything else I can try?

Should the filter be moved up on to the df itself.

Abhi
– Abhi

2022-02-17 22:57:17 +00:00
Commented Feb 17, 2022 at 22:57 — Abhi
– Abhi, Commented Feb 17, 2022 at 22:57
Not following. Could you give the code?

Illustrious Imp
– Illustrious Imp

2022-02-17 23:07:19 +00:00
Commented Feb 17, 2022 at 23:07 — Illustrious Imp
– Illustrious Imp, Commented Feb 17, 2022 at 23:07

Karthik · Accepted Answer · 2022-02-17 23:19:04Z

2

you are trying to apply filter on an array, hence it will throw an error as the syntax will be wrong. You can apply filter on price before and then do transformation as needed.

val cf = df.filter("price > 1.0").groupBy($"name").agg(collect_list(struct($"food", $"price")).as("foods")

answered Feb 17, 2022 at 23:19

Karthik

1,1719 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

How to filter a struct array in a spark dataframe?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related