Postgres FROM查询的列名称之一 [英] Postgres FROM query with one of the column name

问题描述

作为上一个问题的后续内容：

As follow-up to the previous 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 必须是动态的，并且也是列值之一。

which gives me matches correctly, but now the value for array_content needs to be dynamic and that too one of the column value.

让我们说我正在使用聚合器函数来获取查询中的数组内容：

Lets say I am using a aggregator function to get the array content within the query as:

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 列代替在嵌套部分中定义的array_content。可能吗？
现在，它给出了一个例外，即未定义列名。

Now what I need is to get the array_values column in place of array_content that is defined in the unnest portion. Is it possible? For now it is giving an exception that column name not defined.

推荐答案

现在，它给出了一个未定义列名的例外。

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

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

That's because you are using a different column name a.obj_element. In the subquery, we named the column elem. (Or did you really mean to use txt?) So:

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;

这篇关于Postgres FROM查询的列名称之一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！