Is it possible that IN queries will work with arrays ?

[sayalit95]

I have a scenario where a subquery returns me an array of ids as below -

select string_to_array(string_agg(concat(t1.id,',',t2.id),','),',') :: array(bigint) as ids
from department t1 left JOIN department t2 on t1.employeeid=t2.employeeid
where (t1.certain_condition) and (t2.certain_condition)

Above query gives me expected result. But the problem is ,
I wish to apply another IN query on top of this query. But crate does not allow me to do the same. Throws an error like this -

_Error!
UnsupportedFeatureException[Unknown function: (department.id = ANY((SELECT ids FROM (t1, t2)))), no overload found for matching argument types: (bigint, bigint_array_array). Possible candidates: any=(E, array(E)):boolean]__

I am already done trying Array Expressions etc.
Is there any other workaround for this ?

[proddata]

Can you try with UNNEST?

department.id = ANY(SELECT UNNEST(ids) FROM (t1, t2))

---

 string_to_array(string_agg(concat(t1.id,',',t2.id),','),',') :: array(bigint)

could be rewritten as

array_agg(concat(t1.id,',',t2.id)) :: array(bigint)

or could it not?

[sayalit95]

@proddata Thanks for the quick response.
Your second suggestion, does not work for me as array_agg(concat(t1.id,',',t2.id)) :: array(bigint) throws error somewhat like this - SQLParseException[Cannot cast value [121,93,121,111,121,168] to type bigint_array]
array_agg(concat(t1.id,',',t2.id)) returns me the result as below -

1: 121,93
2: 121,111
3: 121,168
.
.
.
.
and so on............

I tried with UNNEST and it worked as expected.
But now I have experienced performance issue in terms of memory, CPU consumption and time as well for large data of more than 1.2 million records. It would be really helpful if you could give me any suggestion with that. Is there any limit with respect to performance for any one of these functions ? -
UNNEST
string_to_array
string_agg
concat

[proddata]

Ah ... right

array_agg(t1.id) || array_agg(t2.id)
-- or
array_cat(array_agg(t1.id),array_agg(t2.id))

would eliminate type casting
and

array_unique(array_agg(t1.id),array_agg(t2.id))

would also eliminate duplicate values.
Both solutions though would lead to a different ordering.

---

As of performance UNNEST or aggregates always lead to an intermediate results and it always would perform better, if you filter as much as possible on the original data set. For suggestions on this, it would be best to have a more complete example of table schemas and queries though.

[sayalit95]

Thanks it helped. :)