Postgres FROM query with one of the column name

Question

As follow-up to the previous question:

Count matches between multiple columns and words in a nested array

I have the following query:

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;

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;

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.

The added double-unnest for the simple array `{"test","test1"}` makes no sense. A single `unnest()` already unnests all there is to unnest ... — Erwin Brandstetter, Nov 07 '16 at 04:42
Also, the proxy CROSS JOIN between your two LEFT JOIN subqueries, is almost certainly wrong. Consider the fishmarket example: http://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result/12464135#12464135 — Erwin Brandstetter, Nov 07 '16 at 04:46
Sure, you are still messing up the count. (I am off to bed now ...) — Erwin Brandstetter, Nov 07 '16 at 04:56
Is it possible to reference a column name that is defined in select query into a LEFT JOIN as in the above question — Syed Asad Abbas Zaidi, Nov 07 '16 at 04:57
Your question is still unclear to me. Your example still does not make sense. In addition to what I already commented, you reference `i.name`, but there is no `i` in the query. Please fix your question to make it clear. — Erwin Brandstetter, Nov 07 '16 at 14:38

Erwin Brandstetter · Answer 1 · 2016-11-07T04:34:33.770

0

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

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;

edited Nov 07 '16 at 04:34

answered Nov 07 '16 at 04:30

Erwin Brandstetter

605,456
145
1,078
1,228

@SyedAsadAbbasZaidi: Oh, you also renamed the input column `array_content` - by mistake I presume? – Erwin Brandstetter Nov 07 '16 at 04:35

Postgres FROM query with one of the column name

1 Answers1

Linked