Preserving Array Member Order in Postgres Query

Question

I would like to know how to preserve/utilize the order of array elements when issuing a select query in Postgres. (In case it's relevant, the array is multidimensional.)

For example, given the following data:

 id |             points
----+---------------------------------
  1 | {{1,3},{7,11},{99,101},{0,1}}
  2 | {{99,101},{7,11},{0,1},{77,22}}

I'd like to know how to write a query which finds rows whose points:

contain the subarray {{7, 11}, {99, 101}}
but not {{99, 101},{7, 11}}.

I've tried using various array operators (@>, &&), adding an index using the intarray module, etc. but have not found a workable solution.

Vao Tsun · Accepted Answer · 2018-01-22 07:08:47Z

to be able to "unnest array by 1 dimention" and use the result set for incomarison, use Pavel Stěhule suggested function:

t=# with c(i,p) as (values(1,'{{1,3},{7,11},{99,101},{0,1}}'::int[][]),(2,'{{99,101},{7,11},{0,1},{77,22}}'))
, p as (select *,a,case when e = '{7, 11}' and lead(e) over (partition by i order by o) = '{99, 101}' and o = lead(o) over (partition by i order by o) -1 then true end from c, reduce_dim(p) with ordinality as a (e,o))
select * from p;
 i |                p                |    e     | o |       a        | case
---+---------------------------------+----------+---+----------------+------
 1 | {{1,3},{7,11},{99,101},{0,1}}   | {1,3}    | 1 | ("{1,3}",1)    |
 1 | {{1,3},{7,11},{99,101},{0,1}}   | {7,11}   | 2 | ("{7,11}",2)   | t
 1 | {{1,3},{7,11},{99,101},{0,1}}   | {99,101} | 3 | ("{99,101}",3) |
 1 | {{1,3},{7,11},{99,101},{0,1}}   | {0,1}    | 4 | ("{0,1}",4)    |
 2 | {{99,101},{7,11},{0,1},{77,22}} | {99,101} | 1 | ("{99,101}",1) |
 2 | {{99,101},{7,11},{0,1},{77,22}} | {7,11}   | 2 | ("{7,11}",2)   |
 2 | {{99,101},{7,11},{0,1},{77,22}} | {0,1}    | 3 | ("{0,1}",3)    |
 2 | {{99,101},{7,11},{0,1},{77,22}} | {77,22}  | 4 | ("{77,22}",4)  |
(8 rows)

now, that you see the logic, complete where:

t=# with c(i,p) as (values(1,'{{1,3},{7,11},{99,101},{0,1}}'::int[][]),(2,'{{99,101},{7,11},{0,1},{77,22}}'))
, p as (select *,a,case when e = '{7, 11}' and lead(e) over (partition by i order by o) = '{99, 101}' and o = lead(o) over (partition by i order by o) -1 then true end from c, reduce_dim(p) with ordinality as a (e,o))
select i,p from p where "case";
 i |               p
---+-------------------------------
 1 | {{1,3},{7,11},{99,101},{0,1}}
(1 row)

not to mention that in case of sequential array pair, you can just cast it to text and use like operator:

t=# with c(i,p) as (values(1,'{{1,3},{7,11},{99,101},{0,1}}'::int[][]),(2,'{{99,101},{7,11},{0,1},{77,22}}'))
select * from c where p::text like '%{7,11},{99,101}%';
 i |               p
---+-------------------------------
 1 | {{1,3},{7,11},{99,101},{0,1}}
(1 row)

Thanks -- I will weigh these options and make one of them work. I'm surprised such a common use case (IMO) is impossible to accomplish using the provided array operators.

Collectives™ on Stack Overflow

Preserving Array Member Order in Postgres Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related