sql - 对未嵌套数组进行字符串操作后过滤行

Question

为什么这种情况会失败？解决我的问题的最佳方法是什么？

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

我的表有一个数组列，其中包含未解析的字符串。我在这里所做的是逐个元素地解析这些元素，然后再次重新分组到一个数组中，这样我就可以比较处理后的数组是否包含我预期的任何元素

但它返回:

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

我真的需要按所有列分组吗？ where 过滤器不应该是唯一虚拟的吗？是否有一种方法可以在不影响查询的其余部分的情况下虚拟地处理 unnest/nest 操作？

Answer 1

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

你可以使用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

---

另一种“更丑陋”的方法是使用子查询:

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

更多信息 PostgreSQL: using a calculated column in the same query