Postgresql: Filter array by series

Question

Is there any way to filter and array column by a series? It's simpler to explain with an example. Imagine I have this data:

table: data
id   tag
1    {a,b,c,d}
2    {a,c,d,b}
3    {c,d,a,b}
4    {d,c,b,a}
5    {d,a,b,c}
6    {d,a,c,b}

Now I want to get all rows, which have ["a", "b"] in that order and no items in between:

SELECT id from data where tags ???? ["a", "b"]

That query should return: 1,3,5

UPDATE 1: After taking a look to array_position and array_positionS: https://www.postgresql.org/docs/10/functions-array.html

I wrote this query:

select id
from data 
where 'a' = ANY(tags)
    and 'b' = ANY(tags) 
    and (array_position(tags, 'a') + 1) = any(array_positions(tags, 'b' ))

Which works as expected

UPDATE 2:

As @klin comment, this would produce wrong result if 'a' can appear multiple times, for example {a,a,b,c,d}. So this is a more generic answer

select *
from data
where 'a' = any(tags)
and 'b' = any(tags) 
and (
    array_position(tags, 'a') + 1 = any(array_positions(tags, 'b' ))
    or array_position(tags, 'b') - 1 = any(array_positions(tags, 'a' )))

klin · Accepted Answer · 2020-02-06 10:53:27Z

1

You can use a regular expression on the text representation of the arrays.

select *
from my_table
where tag::text ~ '[\{,]a,b[,\}]'

Db<>Fiddle.

answered Feb 6, 2020 at 10:53

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

Rumpelstinsk Over a year ago

thanks for the help. Take a look to the update, What query do you think would have better performance?

klin Over a year ago

I came back to the question because your solution seemed too simple to me. Indeed, the query may give unexpected results in some cases: Db<>fiddle.

Rumpelstinsk Over a year ago

Hi @klin thanks for the extra effort. In deed you are right, I came to the same conclusion after some test's. However, the data I showed in the question is just an example. On the real data a can not be duplicate, while b can. I shoulded comment that on the original question.

Collectives™ on Stack Overflow

Postgresql: Filter array by series

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related