pyspark get element from array Column of struct based on condition

Question

I have a spark df with the following schema:

 |-- col1 : string
 |-- col2 : string
 |-- customer: struct
 |    |-- smt: string
 |    |-- attributes: array (nullable = true)
 |    |    |-- element: struct
 |    |    |     |-- key: string
 |    |    |     |-- value: string

df:

#+-------+-------+---------------------------------------------------------------------------+
#|col1   |col2   |customer                                                                   |
#+-------+-------+---------------------------------------------------------------------------+
#|col1_XX|col2_XX|"attributes":[[{"key": "A", "value": "123"},{"key": "B", "value": "456"}]  |
#+-------+-------+---------------------------------------------------------------------------+

and the json input for the array look like this:

...
          "attributes": [
            {
              "key": "A",
              "value": "123"
            },
            {
              "key": "B",
              "value": "456"
            }
          ],

I would like to loop attributes array and get the element with key="B" and then select the corresponding value. I don't want to use explode because I would like to avoid join dataframes. Is it possible to perform this kind of operation directly using spark 'Column' ?

Expected output will be:

#+-------+-------+-----+
#|col1   |col2   |B    |                                                               |
#+-------+-------+-----+
#|col1_XX|col2_XX|456  |
#+-------+-------+-----+

any help would be appreciated

blackbishop · Accepted Answer · 2022-01-28 14:03:21Z

10

You can use filter function to filter the array of structs then get value:

from pyspark.sql import functions as F

df2 = df.withColumn(
    "B", 
    F.expr("filter(customer.attributes, x -> x.key = 'B')")[0]["value"]
)

answered Jan 28, 2022 at 14:03

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

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

1 Comment

Salvatore Nedia Over a year ago

it works like a charm. That's what I was looking for, thanks a lot!

Collectives™ on Stack Overflow

pyspark get element from array Column of struct based on condition

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