How to join on array field?

Question

I have 2 dataset namely Distance and Customer, want to find out id in Customer dataset is present in id_5 of Distance dataset where the id_5 is Array of id's. Your help is greatly appreciated.

case class Distance(zip: String, id_5: Array[Int])
val dist = Seq(Distance("72712",Array(72713,72714,72715)))
val distDS=dist.toDS()

case class Customer (cust_id: Int, id: String)
val c = Seq(Customer(1,"72713"),Customer(2,"72714"),Customer(3,"72720"))
val custDS = c.toDS()

val res = distDS.joinWith(custDS,distDS.col("id_5"(??????)) === custDS.col("id"))`

user8962824 · Accepted Answer · 2017-11-18 14:59:52Z

1

Use array_contains:

import org.apache.spark.sql.functions.expr

distDS.joinWith(custDS, expr("array_contains(id_5, cust_id)"))

answered Nov 18, 2017 at 14:59

user8962824

111 bronze badge

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

1 Comment

Achilleus Over a year ago

Mark it as accepted or useful if you find it useful.

Collectives™ on Stack Overflow

How to join on array field?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related