How use postgresql's ANY to access multidimensional array in dynamic query-CodePudding

I call a postgres function with a multidimensional array like so:

SELECT get_listings(ARRAY[['name:John','grade:1'],['id:5','year:2020']]);

The postgresql function has the following signature:

CREATE FUNCTION get_listings(arr text[]) RETURNS TABLE 
(
    id text,
    tagz text
)

Inside the function I have constructed a statement in a loop:

select * from (select product_type_id, string_agg(tag, '','') as tags 
from all_tags group by product_type_id) as a 
where tags = any($1[0]) and tags = any($1[1])

which is assigned to variable stmt;

Now I call

RETURN QUERY EXECUTE stmt using arrArgs;

where arrArgs has earlier been assigned with arr;

I get this error:

ERROR: op ANY/ALL (array) requires array on right side

How can I access the inner array in the any function call?

CodePudding user response：

PostgreSQL has the best and easy function unnest() for expanding array elements to a set of rows. For example:

SELECT unnest(array[['tagA','tagB'],['tagA','tagB']]);

Result: 

 unnest 
----------
|  tagA  |
|  tagB  |
|  tagC  |
|  tagD  |

Then you can use any conditions on the where statement.

select main.* from 
(
    SELECT unnest(array[['tagA','tagB'],['tagA','tagB']]) as array_data 
) main 
where main.array_data in ('tagA', 'tagD');

Result: 

 array_data 
--------------
|  tagA      |
|  tagD      |