Not sure what database or SQL flavor you're using but array_contains works for querying an array of strings:

SELECT *
FROM table
WHERE ARRAY_CONTAINS(column_name, "string") = True;

Doing it this way generate the actual rows of data, not just a Boolean as shown in the documentation.

https://docs.data.world/documentation/sql/reference/functions/array_contains.html