How to combine two queries where one of them results in an array and the second is the element place-CodePudding

I have the following two queries:

Query #1

(SELECT ARRAY (SELECT (journeys.id) 
               FROM JOURNEYS 
               JOIN RESPONSES ON scenarios[1] = responses.id) AS arry);

This one returns an array.

Query #2:

SELECT (journeys_index.j_index) 
FROM journeys_index
WHERE environment = 'env1' 
  AND for_channel = 'ch1' 
  AND first_name = 'name1';

This second query returns the element index in the former array.

How do I combine the two to get only the element value?

CodePudding user response：

I recreated a simpler example with a table containing an array column (the result of your first query)

create table my_array_test (id int, tst_array varchar[]);
insert into my_array_test values (1,'{cat, mouse, frog}');
insert into my_array_test values (2,'{horse, crocodile, rabbit}');

And another table containing the element position for each row I want to extract.

create table my_array_pos_test (id int, pos int);
insert into my_array_pos_test values (1,1);
insert into my_array_pos_test values (2,3);

e.g. from the row in my_array_test with id=1 I want to extract the 1st item (pos=1) and from the row in my_array_test with id=2 I want to extract the 3rd item (pos=3)

defaultdb=> select * from my_array_pos_test;
 id | pos
---- -----
  1 |   1
  2 |   3
(2 rows)

Now the resulting statement is

select *, 
tst_array[my_array_pos_test.pos] 
from
my_array_test join
my_array_pos_test on my_array_test.id = my_array_pos_test.id

with the expected result

 id |        tst_array         | id | pos | tst_array
---- -------------------------- ---- ----- -----------
  1 | {cat,mouse,frog}         |  1 |   1 | cat
  2 | {horse,crocodile,rabbit} |  2 |   3 | rabbit
(2 rows)

Now, in your case I would probably do something similar to the below, assuming your 1st select statement returns one row only.

with array_sel as 
(SELECT ARRAY (SELECT (journeys.id) 
               FROM JOURNEYS 
               JOIN RESPONSES ON scenarios[1] = responses.id) AS arry)
SELECT arry[journeys_index.j_index]
FROM journeys_index cross join array_sel
WHERE environment = 'env1' 
  AND for_channel = 'ch1' 
  AND first_name = 'name1';

I can't validate fully the above sql statement since we can't replicate your tables, but should give you a hint on where to start from