How to search a string in an array of strings using Apache Spark SQL query?-CodePudding

I have an array of strings like this:

SELECT ARRAY('item_1', 'item_2', 'item_3') AS items

Result:

items
Type : ARRAY<STRING>
["item_1","item_2","item_3"]

I would like to search for an item inside of it, but if I try the regular way:

SELECT * FROM items WHERE items = 'item_1'

I'll get this error:

Cannot resolve '(items.items = 'item_1')' due to data type mismatch differing types in '(items.items = 'item_1')' (array and string). line 1 pos 26

So, what can I do to search a string value inside of an array of strings using a Spark SQL query?

Thanks in advance =)

CodePudding user response：

Use array_contains function:

SELECT * FROM items WHERE array_contains(items, 'item_1')