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!

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.