Unnest array of arrays

Question

In my parameterized query I have to unnest arrays with 1 dimension and 2 dimensions:

$1: ARRAY['id1', 'id2']
$2: ARRAY[ARRAY['tag1'], ARRAY['tag2']]

I tried this query:

INSERT INTO table (id, tags)
SELECT * FROM UNNEST ($1::text[], $2::text[][])

But got this error:

column "tags" is of type text[] but expression is of type text 

I want the result of SELECT * FROM UNNEST ... to be 2 rows:

'id1', ARRAY['tag1']
'id2', ARRAY['tag2']

Answer 1

To unnest 1-dimensional arrays from N-dimensional arrays, I suggest this custom function:

CREATE OR REPLACE FUNCTION unnest_nd_1d(a ANYARRAY, OUT a_1d ANYARRAY)
  RETURNS SETOF ANYARRAY
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
BEGIN
   FOREACH a_1d SLICE 1 IN ARRAY a_2d LOOP
      RETURN NEXT;
   END LOOP;
END
$func$;

See:

• Unnest array by one level

Then to achieve your desired result:

SELECT unnest(a1), unnest_nd_1d(a2)
FROM  (
   SELECT '{id1,id2}'::text[]
        , '{{tag1},{tag2}}'::text[]
   ) t(a1, a2);

db<>fiddle here

About those multiple set-returning functions in the SELECT list:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?
• Unnest multiple arrays in parallel

Your use of text[][] indicates a misunderstanding. Postgres does not enforce array dimensions. See:

• Use string[][] with ngpsql
• Mapping PostgreSQL text[][] type and Java type