Dealing with collection parameters for array function such as PostgreSql overlap for a table column

Use case:

[toc on_off::hide=1]

A user has a multiselect parameter of type java.util.Collection and Nested type is java.lang.String. He has to pass the selected value in array field like below -

AND l."groupIds" && ARRAY['String1', 'String2] 

here ARRAY[selected_param_value].

Issue:

$X{} construction does not work. It appears, operator array_field && ARRAY[] works a bit differently. If there is atleast one element in ARRAY that exists in atleast one row of array_field, this field gets displayed. $X{} won't work here, as, it does not support array function such as PostgreSql overlap for a table column.

Resolution:

In attachments you can find a sample report i developed for the reference. You will also need a sample data. The sample table can be created using query below: 

Query for table:

CREATE TABLE posts ( 
title text NOT NULL PRIMARY KEY, 
tags text[] 
); 

INSERT INTO posts (title, tags) values 
('1', '{"postgres", "replication"}'), 
('2', '{"go", "queues", "goroutines"}'), 
('3', '{"postgres", "go", "triggers"}'), 
('4', '{"mysql", "monitoring", "opsdash"}'); 

My example is for Strings but it can be done for any type. So, basically the idea is next: 

1. Create parameter type of java.util.List, nested type String (in that case), as below. Providing default value expression is optional. I did it just to not waste time applying parameter values. 

   <parameter name="ValuesList" class="java.util.List" nestedType="java.lang.String"> 
   <defaultValueExpression><![CDATA[Arrays.asList(new String[] {"postgres", "go", "mysql"})]]></defaultValueExpression> 
   </parameter> 

2. Create parameter type of String, which parses the list we created in step 1 and makes it readable by psql syntax. Note, it should be String, also, DefaultValueExpression is essential, and prompting should be disabled. 

   <parameter name="FinalParam" class="java.lang.String" isForPrompting="false"> 
   <defaultValueExpression><![CDATA[$P{ValuesList}.toString().replace("[", "'{").replace("]", "}'")]]></defaultValueExpression> 
   </parameter> 

3. Use second parameter in query like below: 

   <queryString language="SQL"> 
   <![ 
   CDATA[ 
   select * from posts 
   where tags && $P!{FinalParam} 
   ] 
   ]> 
   </queryString> 
   

Please, note: query uses parameter with syntax $P! with "!" symbol. It is required for parameter to be passed to query as plain text, not being evaluated. If evaluated the report with throw the error regarding casting type.

paramarray.jrxml