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);

Answer 1

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.