check if array is ordered subset postgres

Question

how to check if an array is an ordered subset of another array in PostgreSQL?

[1, 2] ordered_subset [1, 4, 2] -> true
[1, 2] ordered_subset [2, 3, 1, 5] -> false

Ajax1234 · Accepted Answer · 2022-10-02 16:48:09Z

1

You can first filter out elements from the second array that do not belong to the first, and then check if the first is a subarray of the second by using generate_series:

select exists (select 1 from generate_series(1, array_length(t1.a3, 1)) v2 
       where t1.a1 = t1.a3[v2:v2+array_length(t1.a1, 1)-1]) from 
   (select t.a1, (select array_agg(v) from unnest(t.a2) v 
         where exists (select 1 from unnest(t.a1) v1 where v1 = v)) a3
    from array_inp t
) t1

See fiddle.

edited Oct 2, 2022 at 16:48

answered Oct 2, 2022 at 16:39

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

check if array is ordered subset postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related