Find position(s) in array matching a given sub-array

Question

Given this table:

CREATE TABLE datasets.travel(path integer[], path_timediff double precision[]);

INSERT INTO datasets.travel
VALUES (array[50,49,49,49,49,50], array[NULL,438,12,496,17,435]);

I am looking for some kind of function or query in the PostgreSQL that for a given input array[49,50] will find the matching consecutive index values in path which is [5,6] and the corresponding element in path_timediff which is 435 in the example (array index 6).

My ultimate purpose is to find all such occurrences of [49,50] in path and all the corresponding elements in path_timediff. How can I do that?

`array1[50,49,49,49,49.50]` is invalid. Can you clarify your example, please? And show your version of Postgres and the indexes you have. — Erwin Brandstetter, Nov 30 '15 at 21:24
Now it looks like your example does not match the description - unless I am missing something: `the index values in array1 which is [4,5]` .. looks like this should read `[5,6]`? Or explain how you match exactly ... (Also, your `INSERT` syntax is invalid as well.) — Erwin Brandstetter, Nov 30 '15 at 21:28
(array1 [50,49,49,49,49,50,49] ,array2[NULL,438,12,496,17,435,121]) here this array1 is basically a path values which is like 50 to 49 to 49 to 49 to 49 to 50.. now if i give a function like for example input (50,49) so the function find the first pair in the array1 and it search specific index in array2 which is index 1 and its value is 438. in array1 another pair it find which is [50,49] and its crosspounding index is 6 which value is 121 .now it must to add these two values returen me for this path.. — zahid, Nov 30 '15 at 21:35
exactly you are right Erwin...the values in the array2 must to be one more bcz array1[50] is the start point and when object reach from 50 to 49 then it take a travel time which is define in array2 which is [438] bcz on rout 50 it does not have any travel time when it reach to next station which is for example 49 then it take a value like 438 and then it start again from 49 and reach to 49 so it take a time like 12 and so one... — zahid, Nov 30 '15 at 21:38

score 0 · Accepted Answer · edited May 23 '17 at 12:15

Assuming you have a primary key in your table you did not show:

CREATE TABLE datasets.travel (
  travel_id     serial PRIMARY KEY
, path          integer[]
, path_timediff float8[]
);

Here is one way with generate_subscripts() in a LATERAL join:

SELECT t.travel_id, i+1 AS position, path_timediff[i+1] AS timediff
FROM  (SELECT * FROM datasets.travel WHERE path @> ARRAY[49,50]) t
     , generate_subscripts(t.path, 1) i
WHERE  path[i:i+1] = ARRAY[49,50];

This finds all matches, not just the first.

i+1 works for a sub-array of length 2. Generalize with i + array_length(sub_array, 1) - 1.

The subquery is not strictly necessary, but can use a GIN index on (path) for a fast pre-selection:

(SELECT * FROM datasets.travel WHERE path @> ARRAY[49,50])

Thanks Erwin.. Really appreciated for your sharing knowledge :) — zahid, Dec 01 '15 at 07:57

Find position(s) in array matching a given sub-array

1 Answers1