Query table by a value in the second dimension of a two dimensional array column

Question

WHAT I HAVE

I have a table with the following definition:


CREATE TABLE "Highlights"
(
  id uuid,
  chunks numeric[][]
)

WHAT I NEED TO DO

I need to query the data in the table using the following predicate:

... WHERE id = 'some uuid' and chunks[????????][1] > 10 chunks[????????][3] < 20

What should I put instead of [????????] in order to scan all items in the first dimension of the array?

Notes

I'm not entirely sure that chunks[][1] even close to something I need.

All I need is to test a row, whether its chunks column contains a two dimensional array, that has in any of its tuples some specific values.

roman · Accepted Answer · 2019-08-25 14:48:49Z

1

May be there's better alternative, but this might do - you just go over first dimension of each array and testing your condition:

select *
from highlights as h
where
    exists (
        select
        from generate_series(1, array_length(h.chunks, 1)) as tt(i)
        where
            -- your condition goes here
            h.chunks[tt.i][1] > 10 and h.chunks[tt.i][3] < 20
    )

db<>fiddle demo

update as @arie-r pointed out, it'd be better to use generate_subscripts function:

select *
from highlights as h
where
    exists (
        select *
        from generate_subscripts(h.chunks, 1) as tt(i)
        where
            h.chunks[tt.i][3] = 6
    )

db<>fiddle demo

edited Aug 25, 2019 at 14:48

answered Aug 25, 2019 at 9:19

roman

118k30 gold badges205 silver badges209 bronze badges

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

2 Comments

Aryéh Radlé Over a year ago

I would use generate_subscripts(h.chunks, 1), but otherwise, it can be accepted as an answer. Can indexes improve the performance here? Any suggestions?

roman Over a year ago

@ArieR great suggestion. Honestly, I'm not sure if indexes can really improve performance

Collectives™ on Stack Overflow

Query table by a value in the second dimension of a two dimensional array column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related