Query on value of array elements (PostgreSQL)

Question

The following query:

select unnest(Table2.L) as X, unnest(Table1.O) 
from Table1, Table2 
where Table1.code = Table2.code 
order by X ;

produces the desired results. I would, however, like to exclude (unnested) rows which match some value. Adding a condition to the query, such as the following:

and unnest(Table2.L) != '-'

obviously does not work. Is this possible? How?

Answer 1

If by unnest(Table2.L) != '-' you mean

throw out all the unnested elements that are '-'

then use a derived table and filter out the unnested values you don't want:

select *
from (
    select unnest(Table2.L) as X, unnest(Table1.O) 
    from Table1 join Table2 on Table1.code = Table2.code
) dt
where X != '-'
order by X ;

If you mean

ignore all rows from Table2 where L contains '-'

then you can use the @> operator to check if L contains a certain element:

select unnest(Table2.L) as X, unnest(Table1.O)
from Table1 join Table2 on Table1.code = Table2.code
where not Table1.L @> ARRAY['-']

or you could use ANY:

select unnest(Table2.L) as X, unnest(Table1.O)
from Table1 join Table2 on Table1.code = Table2.code
where not '-' = any(Table1.L)

And do yourself a favor by forgetting that implicit joins exist, always use explicit join conditions.

Answer 2

Another way of doing it:

SELECT x, y
FROM  (SELECT code, unnest(l) AS x FROM table1) t1
JOIN  (SELECT code, unnest(o) AS y FROM table2) t2 USING (code)
WHERE  x <> '-'
ORDER  BY x;

May of may not be faster. Depends on how selective the WHERE clause is. Run a quick EXPLAIN ANYLYZE.

Note that I untangled table1 and table2, which are reversed in the example. If you are confused by all the clarity, try substituting x -> y in the WHERE and ORDER BY clause.

If you actually want to eliminate occurrences of - on both sides, add AND y <> '-' to the WHERE clause - making it symmetric (no cuonfusion possible).
I would also ORDER BY x, y, if x is not guaranteed to be unique, to make the sort order stable.

Demo on sqlfiddle.