sql - 使用列名之一的 Postgres FROM 查询

Question

作为上一个问题的后续:

• Count matches between multiple columns and words in a nested array

我有以下查询:

SELECT row_number() OVER (ORDER BY t.id) AS id
     , t.id AS "RID"
     , count(DISTINCT a.ord) AS "Matches"
FROM   tbl t
LEFT   JOIN (
   unnest(array_content) WITH ORDINALITY x(elem, ord)
   CROSS JOIN LATERAL
   unnest(string_to_array(elem, ',')) txt
   ) a ON t.description ~ a.txt
       OR t.additional_info ~ a.txt
GROUP  BY t.id;

这给了我正确的匹配，但现在 array_content 的值需要是动态的，并且也是列值之一。

假设我正在使用聚合器函数来获取查询中的数组内容:

SELECT row_number() OVER (ORDER BY t.id) AS id
     , t.id AS "RID"
     , array_agg(DISTINCT demo_a.element_demo) as array_values
     , count(DISTINCT a.ord) AS "Matches"
     , count(DISTINCT demo_a.ord) AS "Demo_Matches"
FROM   tbl t
LEFT   JOIN (
   unnest(array_values) WITH ORDINALITY x(elem, ord)
   CROSS JOIN LATERAL
   unnest(string_to_array(elem, ',')) txt
   ) a ON t.description ~ a.txt
       OR t.additional_info ~ a.txt
LEFT JOIN (
   unnest("test1","test2"::varchar[]) WITH ORDINALITY x(element_demo, ord)
   CROSS JOIN LATERAL
   unnest(string_to_array(element_demo, ',')) text
   ) demo_a ON i.name ~ demo_a.text
GROUP  BY t.id;

现在我需要的是获取 array_values 列来代替 unnest 部分中定义的 array_content。是否可以？现在它给出了一个异常(exception)，即未定义列名。

Answer 1

For now it is giving an exception that column name not defined.

那是因为您使用了不同的列名 a.obj_element 。在子查询中，我们将列命名为 elem。 (或者你真的想使用 txt 吗？)所以:

SELECT row_number() OVER (ORDER BY t.id) AS id
     , t.id AS "RID"
     , array_agg(DISTINCT a.elem) AS array_values  -- or a.txt?
     , count(DISTINCT a.ord) AS "Matches"
FROM   tbl t
LEFT   JOIN (
   unnest(array_content) WITH ORDINALITY x(elem, ord)
   CROSS JOIN LATERAL
   unnest(string_to_array(elem, ',')) txt
   ) a ON t.description ~ a.txt
       OR t.additional_info ~ a.txt
GROUP  BY t.id;