How to get array subscripts when unnesting multidimensional arrays?

Question

I have a table:

CREATE TABLE p (
    id text PRIMARY KEY,
    t  text[],
    r  int[]
);

Columns t and r are multi-dimensional, where the number of elements in each sub-array for a given row is different.

id	t	r
p1	{{"a",NULL,NULL},{"b","c","d"},{"b",NULL,NULL}}	{{1,NULL,NULL},{1,1,2},{1,NULL,NULL}}
p2	{{"a",NULL},{"b","c"},{"b",NULL}}	{{1,NULL},{1,1},{1,NULL}}

I want to unnest the data in the columns and have a subscript value describing the sub-array that the row came from (this is wrong):

select p.id, u.stage, u.t, u.r
from p,
unnest(t, r) with ordinality as u(t, r, stage);

This gives the row from the unnest, i.e.:

id	stage	r	t
"p1"	1	"a"	1
"p1"	2
"p1"	3
"p1"	4	"b"	1
"p1"	5	"c"	1
"p1"	6	"d"	2
"p1"	7	"b"	1
"p1"	8
"p1"	9
"p2"	1	"a"	1
"p2"	2
"p2"	3	"b"	1
"p2"	4	"c"	1
"p2"	5	"b"	1
"p2"	6

What I want is to get an subscript describing from which sub-array the data came from, like this:

id	stage	r	t
"p1"	1	"a"	1
"p1"	1
"p1"	1
"p1"	2	"b"	1
"p1"	2	"c"	1
"p1"	2	"d"	2
"p1"	3	"b"	1
"p1"	3
"p1"	3
"p2"	1	"a"	1
"p2"	1
"p2"	2	"b"	1
"p2"	2	"c"	1
"p2"	3	"b"	1
"p2"	3

How do I do this please? I don't know how many array elements will be in t and r, but both arrays will have the same dimensions in the same row.

If you know length then divide by it and round up stage column — Kadet, Jun 05 '22 at 21:05
I took the liberty to fix your last sentence. Correct me if I'm wrong ... — Erwin Brandstetter, Jun 09 '22 at 01:35

Erwin Brandstetter · Answer 1 · 2022-06-09T01:37:48.263

Assuming at least Postgres 10:

SELECT p.id, i AS stage
     , unnest(r[i:i]) AS r
     , unnest(t[i:i]) AS t
FROM   p, generate_subscripts(r,1) i;

db<>fiddle here

Produces your desired result exactly.

Requires that both arrays have the same dimensions in the same row - like you asserted.

If r can be NULL, you need to do more. You did not specify that case, but your columns are not marked NOT NULL, either ...

demo

use array_length(array,2) get the array length in 2nd dimension.
row_num to construct the global sorted row_number
use stage/array_length and check the yield result is whole integer or not.
after first few step, then the problem becomes: how to do backward fill also ignores nulls.
I tried many ways to backward fill, failed then I try to use lead() window function.
I included 2nd array have 4 elements.
maybe there is some other simple way to do it. I even tried first function that ignores null

WITH cte AS (
    SELECT
        p.id,
        u.t,
        u.r,
        u.stage,
        array_length(p.t, 2),
        --        round(u.stage::numeric/array_length(p.t,2),1),
        CASE WHEN round(u.stage::numeric / array_length(p.t, 2), 1) = floor(round(u.stage::numeric / array_length(p.t, 2), 1)) THEN
            round(u.stage::numeric / array_length(p.t, 2), 1)::integer
        ELSE
            NULL
        END AS case1,
        row_number() OVER (ORDER BY id,
            stage) AS row_num
    FROM
        p,
        unnest(t, r)
        WITH ORDINALITY AS u (t, r, stage)
),
cte2 AS (
SELECT
    *,
    lead(case1, 1) OVER (PARTITION BY id ORDER BY row_num) AS lead1,
    lead(case1, 2) OVER (PARTITION BY id ORDER BY row_num) AS lead2,
    lead(case1, 3) OVER (PARTITION BY id ORDER BY row_num) AS lead3
FROM
    cte
ORDER BY
    row_num
)
SELECT
    id,
    t,
    r,
    stage,
    row_num,
    CASE WHEN lead1 IS NULL
        AND lead2 IS NOT NULL
        AND case1 IS NULL THEN
        lead2
    WHEN lead2 IS NULL
        AND lead1 IS NOT NULL
        AND case1 IS NULL THEN
        lead1
    WHEN lead1 IS NULL
        AND lead2 IS NULL
        AND case1 IS NULL THEN
        lead3
    WHEN case1 IS NOT NULL THEN
        case1
    END
FROM
    cte2;

How to get array subscripts when unnesting multidimensional arrays?

2 Answers2

demo