Join the Stack Overflow Community
Stack Overflow is a community of 6.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 7 down vote favorite
2

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);
share|improve this question
1  
As you are essentially storing key/value pairs, it would be much more efficient to use a hstore column for this. – a_horse_with_no_name May 18 '15 at 9:01
    
I think it's better with hstore. – Emrah Mehmedov May 18 '15 at 10:56
up vote 1 down vote

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.

share|improve this answer
    
Which database is better for arrays? – Emrah Mehmedov May 18 '15 at 8:42
2  
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. – Patrick May 18 '15 at 8:48
2  
@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. – a_horse_with_no_name May 18 '15 at 9:00
1  
@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. – Patrick May 18 '15 at 9:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.