filter not supported for listagg

[danielyahn]

This is an example for the listagg function without filter. It works as expected:

SELECT
    id,
    listagg(value, ',') WITHIN GROUP (ORDER BY value) csv_value
FROM (VALUES
   ROW('a', 1, 'filter-me'),
   ROW('c', 1, 'not-me'),
   ROW('b', 2, 'not-me')
    ) t(value, id, attr)
group by id

However, I tried placing filter in couple places with listagg, but Trino return SQL grammar errors.

-- mismatched input 'FILTER'. Expecting: 'WITHIN'
SELECT id,
       listagg(value, ',')
           FILTER (WHERE attr <> 'filter-me')
           WITHIN GROUP
           (ORDER BY value)
           csv_value
FROM (VALUES
    ROW('a', 1, 'filter-me'),
    ROW('c', 1, 'not-me'),
    ROW('b', 2, 'not-me')
    ) t(value, id, attr)
group by id;

-- mismatched input 'FILTER'. Expecting: '('
SELECT id,
       listagg(value, ',')
           WITHIN GROUP
           FILTER (WHERE attr <> 'filter-me')
           (ORDER BY value)
           csv_value
FROM (VALUES
    ROW('a', 1, 'filter-me'),
    ROW('c', 1, 'not-me'),
    ROW('b', 2, 'not-me')
    ) t(value, id, attr)
group by id;

-- mismatched input 'FILTER'. Expecting: '%', '(', ')', '*', '+', ',', '-', '->', '.', '/', 'AND', 'ASC', 'AT', 'DESC', 'NULLS', 'OR', 'OVER', '[', '||', <predicate>, <string>
SELECT id,
       listagg(value, ',')
           WITHIN GROUP
           (ORDER BY value FILTER (WHERE attr <> 'filter-me'))
           csv_value
FROM (VALUES
    ROW('a', 1, 'filter-me'),
    ROW('c', 1, 'not-me'),
    ROW('b', 2, 'not-me')
    ) t(value, id, attr)
group by id;

-- mismatched input '('. Expecting: ',', 'EXCEPT', 'FETCH', 'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'ORDER', 'UNION', 'WHERE', 'WINDOW', <EOF>
SELECT id,
       listagg(value, ',')
           WITHIN GROUP
           (ORDER BY value)
           FILTER (WHERE attr <> 'filter-me')
           csv_value
FROM (VALUES
    ROW('a', 1, 'filter-me'),
    ROW('c', 1, 'not-me'),
    ROW('b', 2, 'not-me')
    ) t(value, id, attr)
group by id;

This brings back the expected result, but this is a workaround using array_join, array_sort, and array_agg.

SELECT
    id,
    array_join(array_sort(array_agg(value) FILTER (WHERE attr <> 'filter-me')), ',')
FROM (VALUES
          ROW('a', 1, 'filter-me'),
          ROW('c', 1, 'not-me'),
          ROW('b', 2, 'not-me')
     ) t(value, id, attr)
group by id