Filter rows after string operation on unnested array

Question

Why is this situation failing? What's the neatest way to solve my problem?

SELECT * FROM myTable foo
WHERE ARRAY_AGG(SUBSTRING(UNNEST(foo.array), 'abc(.*?)xyz')) 
    && ARRAY['ElementA','ElementZ']

My table has an array column, which contains unparsed Strings. What I am doing here is parsing those, element by element, and then reaggrouping again into an array, so I can compare if the processed array contains any of my expected elements

But it returns:

ERROR: column "foo.id" must appear in the GROUP BY clause or be used in an aggregate function

Do I really need to group by all the columns? Shouldn't the where filter be virtual uniquely? IS there maybe a way to deal virtually with the unnest/nest operations without affecting the rest of the query?

Sample data and desired results would help others figure out what you are *trying* to do. — Gordon Linoff, Jan 15 '18 at 14:08
The thing is that all of this is a simplification, I am not sure it is valuable to focus too much on a mock scenario, which will be simpler — Whimusical, Jan 15 '18 at 14:15
@Whimusical Preparing sample data and desired output is **always** valuable and helps to provide accurate solution. — Lukasz Szozda, Jan 15 '18 at 15:42

Lukasz Szozda · Accepted Answer · 2018-01-15T15:49:43.590

Do I really need to group by all the columns? Shouldn't the where filter be virtual uniquely? Is there maybe a way to deal virtually with the unnest/nest operations without affecting the rest of the query

You could use JOIN LATERAL:

SELECT foo.*
FROM myTable foo
JOIN LATERAL (SELECT ARRAY_AGG(SUBSTRING(s.t, 'abc(.*?)xyz')) AS r
              FROM UNNEST(foo."array") s(t)
             ) sub 
   ON sub.r && ARRAY['ElementA','ElementZ'];

DBFiddle Demo

Another "uglier" way is to use subquery:

SELECT * 
FROM (
  SELECT *, (SELECT ARRAY_AGG(SUBSTRING(s.t, 'abc(.*?)xyz')) AS r
              FROM UNNEST(foo."array") s(t)
             ) r
  FROM myTable foo
) x
WHERE r && ARRAY['ElementA','ElementZ'];

DBFiddle Demo2

More info PostgreSQL: using a calculated column in the same query

Filter rows after string operation on unnested array

1 Answers1