I need to change my query from Postgres to Presto but I am getting an error which I don't understand - "mismatched input '&'. Expecting: expression"

My SQL query-

SELECT sub.custID, sum(coalesce(cast(devicecount as INT), 0)) as devicecount, data_attr3 FROM(
        SELECT  custID, data_attr1 as devicecount, split_part(data_attr3, '-', 2) data_attr3,
                RANK() OVER
                (
                    PARTITION by custID
                    ORDER BY collected_on desc
                ) AS rownum
        FROM tableA 
        ) sub
WHERE rownum = 1 and  string_to_array(data_attr3, ':') && '{0,1,2,4,5,16}'::text[]  group by 1, 3

I am getting an error at the last line where I am using "&&" but the error goes if I remove it. The error then is not recognizing string_to_array and it cant recognize ::text[] on the last line. Any help would be appreciated.

CodePudding user response：

As always - documentation is your friend. Presto has quite different syntax for handling arrays, so depending on Presto version you can try next:

select cardinality( -- count elements in array
        array_intersect( -- get arrays intersection
            split('1:2:3', ':'), -- split string on ':' as string_to_array
            array['0','1','2','4','5','16']) -- create varchar array
    ) > 0; 

or

select arrays_overlap(
        split('1:2:3', ':'),
        array['0','1','2','4','5','16']
    );

Output: