Let us say that in our query we wanted all my zips returned but for ones that had keys where the value is in a certain range, we want those keys returned. we'd do this. The fact we need all even if they have no such keys necessitates us putting the condition in the ON rather than the WHERE.
SELECT zip, (h).key, (h).value As val FROM zcta5 AS foo LEFT JOIN LATERAL each(hstore(foo) - 'zip'::text) As h ON ((h).value BETWEEN '12345' and '14567') ORDER BY zip limit 5;
Output would be
zip | key | val ------+--------+---------- 00601 | | 00602 | | 00603 | | 00606 | awater | 12487.00 00610 | hu10 | 12618