topic Re: Error while using Array_contains function in left join condition in Data Engineering

Error while using Array_contains function in left join condition

Rsa — Mon, 20 Mar 2023 08:48:46 GMT

'Item_id' is column in array format like ["ba1b-5fbe1547ddd5", "88f9-ac3b93334f69", "8bba-4075a47eb814"] in table1 and table2 has column Id with single value like ba1b-5fbe1547ddd5.

While join two table

select

table1.*,

table2.*

from table1

left join table2

on array_contains(table2.id, table1.item_id)

getting below error

Error in SQL statement: AnalysisException: cannot resolve 'array_contains(table2.id, table1.item_id)' due to data type mismatch: Input to function array_contains should have been array followed by a value with same element type, but it's [string, array<string>].

@Hubert Dudek @Werner Stinckens @Aviral Bhardwaj @HEMANT PATEL @Omkar G @yogu (Customer) @Gam Nguyen @Aman Sehgal @Ajay Pandey @Aayush Patel @Hau Nguyen

Re: Error while using Array_contains function in left join condition

pvignesh92 — Mon, 20 Mar 2023 15:32:25 GMT

@Rishabh Shanker Hi. The array_contains function requires the below syntax

array_contains(array, value)

The argument needs array as the first value and the element as the second value. So can you try

array_contains(table1.item_id, table2.id )

Re: Error while using Array_contains function in left join condition

Anonymous — Mon, 27 Mar 2023 05:17:28 GMT

Hi @Rishabh Shanker

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help.

We'd love to hear from you.

Thanks!