Search code examples
sqlarrayssql-likepresto

How to search for text in an array of varchar field in Presto?

A field is defined to be array(varchar) in modeanlyatic.

I want to search for records with field contains a certain text pattern, says 'ABCD'.

If I run this SQL:

select * from data_table 
where top_results like '%ABCD%'

It throws this error:

Query failed (#20190730_021145_23663_dn9fj): line 2:7: Left side of LIKE expression must evaluate to a varchar (actual: array(varchar)

What is the correct syntax to detect the presence of a certain string?

Solution

  • Use filter(array(T), function(T, boolean)) -> array(T)

    it returns array containing elements for which function returns true. Use cardinality function to check if array is not empty:

    SELECT cardinality(filter(ARRAY ['123ABCD456', 'DQF', 'ABCD', 'ABC'], x -> x like '%ABCD%'))>0 ;

    Returns

    true

    Check another value:

    SELECT cardinality(filter(ARRAY ['123ABCD456', 'DQF', 'ABCD', 'ABC'], x -> x like '%XXX%'))>0 ;

    Returns

    false

    Hope you got the idea.