pg removing common part in array

Question

Suppose a postgresql query that output rows of 2 columns each being array of int[][2]

            track0                          track1
{{1,2},{5847,5848},{5845,5846}......}   {{1,2},{5847,5848},{10716,10715}........}

 {{13,14},{1,2},{5847,5848},{284,285}........}   {{13,14},{1,2},{5847,5848},{1284,1285}................}

How can we remove the leading arrays common to both columns except the last one? In the first row {1,2} should be removed from the two columns. In the second row {13,14},{1,2} should be removed from the two columns.

Can it be done with sql or is it necessary to use plpgsql?

I could manage the plpgsql but would like sql solution.

score 0 · Answer 1 · answered Oct 20 '17 at 08:44

given your sample with no gaps in sequence of equal pairs,

t=# with d(t0,t1) as (values
      ('{{1,2},{5847,5848},{5845,5846}}'::int[][2],'{{1,2},{5847,5848},{10716,10715}}'::int[][2])
    , ('{{13,14},{1,2},{5847,5848},{284,285}}'::int[][2],'{{13,14},{1,2},{5847,5848},{1284,1285}}'::int[][2])
    )
    , u as (
      select *
      from d
      join unnest(t0) with ordinality t(e0,o0) on true
      left outer join unnest(t1) with ordinality t1(e1,o1) on o0=o1
    )
    , p as (
    select *
    , case when (
        not lead(e0=e1) over w
        or not lead(e0=e1,2) over w
        or e0!=e1
      ) AND (o1%2) = 1
      then ARRAY[e0,lead(e0) over w] end r0
    , case when (
        not lead(e0=e1) over w
        or not lead(e0=e1,2) over w
        or e0!=e1
      ) AND (o1%2) = 1
      then ARRAY[e1,lead(e1) over w] end r1
    from u
    window w as (partition by t0,t1 order by o0)
    )
    select t0,t1,array_agg(r0),array_agg(r1)
    from p
    where r0 is not null or r1 is not null
    group by t0,t1
    ;
                  t0                   |                   t1                    |         array_agg         |          array_agg
---------------------------------------+-----------------------------------------+---------------------------+-----------------------------
 {{13,14},{1,2},{5847,5848},{284,285}} | {{13,14},{1,2},{5847,5848},{1284,1285}} | {{5847,5848},{284,285}}   | {{5847,5848},{1284,1285}}
 {{1,2},{5847,5848},{5845,5846}}       | {{1,2},{5847,5848},{10716,10715}}       | {{5847,5848},{5845,5846}} | {{5847,5848},{10716,10715}}
(2 rows)

you can skip part of complication if you add some trick for multidimentional arrays, look here and here

pg removing common part in array

1 Answers1