CODEWITHSUNDEEP
postgresql
Alan
Alan

Reputation: 241

Array of array in Postgres

In a Postgres DB I have a field field defined like this:

CREATE TABLE t (
    id SERIAL PRIMARY KEY,
    field character varying(255)[] DEFAULT ARRAY[]::character varying[],
);

There I store values like:

ID   FIELD   
1    {{lower,0},{greater,10}}
2    {{something_else,7},{lower,5}}

1 - How can I select the lower/greater value? I'd like a query response like this:

ID   LOWER   
1    0
2    5

2 - How can I filter by those lower/greater values?

Thanks!

Upvotes: 0

Views: 157

Answers (1)

404
404

Reputation: 8592

It's pretty awkward to do but this accomplishes it. I use PG 9.3 so I don't know if there are better ways to do this in later versions.

SELECT id, (SELECT field[ss][2] FROM generate_subscripts(field, 1) ss WHERE field[ss][1] = 'lower') AS lower
FROM t;

Basically, for each record, generate the subscripts to use as indexes into the main array to access the subarrays. For each, look for an array where the first item is 'lower'. If found, return the value of the second item.

Upvotes: 1

Related Questions