cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

Error while using Array_contains function in left join condition

Rsa
New Contributor II

'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​ 

1 ACCEPTED SOLUTION

Accepted Solutions

pvignesh92
Honored Contributor

@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 )

View solution in original post

2 REPLIES 2

pvignesh92
Honored Contributor

@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 )

Anonymous
Not applicable

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!