Postgres SQL to query array text[] in specific element

Question

This is the data that I'm trying to query: Table name: "test", column "data"

7;"{{Hello,50},{Wazaa,90}}"
8;"{{Hello,50},{"Dobar Den",15}}"

To query this data I'm using this SQL query:

SELECT *, pg_column_size(data) FROM test WHERE data[1][1] = 'Hello'

How I can search in all elements but in the first sub element and not in the second for example:

SELECT *, pg_column_size(data) FROM test WHERE data[][1] = 'Hello'

because if I search like this:

SELECT *, pg_column_size(data) FROM test WHERE data[1][1] = "Wazaa"

it won't return anything because I'm hardcoding to look at first sub element and I have to modify it like this:

SELECT *, pg_column_size(data) FROM test WHERE data[2][1] = 'Wazaa'

How to make it to check all parent elements and first sub element?

there is solution using "ANY" to query all elements but I don't want to touch second element in where statement because if I have numbers in first sub element it will query the second parameter which is also number.

SELECT * FROM test WHERE '90' = ANY (data);

As you are essentially storing key/value pairs, it would be much more efficient to use a hstore column for this. — user330315
– user330315, Commented May 18, 2015 at 9:01

Patrick · Accepted Answer · 2015-05-18 08:45:16Z

2

PostgreSQL's support for arrays is not particularly good. You can unnest a 1-dimensional array easy enough, but a n-dimensional array is completely flattened, rather than only the first dimension. Still, you can use this approach to find the desired set of records, but it is rather ugly:

SELECT test.*, pg_column_size(test.data) AS column_size
FROM test
JOIN (SELECT id, unnest(data) AS strings FROM test) AS id_strings USING (id)
WHERE id_strings.strings = 'Wazaa';

Alternatively, write this function to reduce a 2-dimensional array into records of 1-dimensional arrays and then you can basically use all of the SQL queries in your question.

edited May 18, 2015 at 8:45

answered May 18, 2015 at 8:34

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

4 Comments

Emrah Mehmedov Over a year ago

Which database is better for arrays?

Patrick Over a year ago

Most databases as we know them today are relational. That makes them inherently ill-fitted for sequential data structures like arrays. In general, you will be advised to change your data model in such a way that you do not need arrays to begin with.

user330315 Over a year ago

@Patrick: actually I think in the world of relational databases, Postgres' array support is extremely good - no other DBMS I know offers this kind of flexibility. As with any feature: you can always improve this. Whether or not an array is a good choice for a specific problem is a completely different topic though.

Patrick Over a year ago

@a_horse_with_no_name PostgreSQL may provide extremely better support than other DBMS'es, but that still does not make any RDBMS well-suited for manipulating arrays.

Collectives™ on Stack Overflow

Postgres SQL to query array text[] in specific element

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related