stsmurf
stsmurf

Reputation: 472

Query on multiple postgres hstores combined with or

This is a hardcoded example of what I'm trying to achieve:

SELECT id FROM places
    WHERE metadata->'route'='Route 23'
    OR  metadata->'route'='Route 22'
    OR  metadata->'region'='Northwest'
    OR  metadata->'territory'='Territory A';

Metadata column is an hstore column and I'm wanting to build up the WHERE clause dynamically based on another query from a different table. The table could either be:

    id   |        metadata           
---------+----------------------------
    1647 | "region"=>"Northwest"
    1648 | "route"=>"Route 23"
    1649 | "route"=>"Route 22"
    1650 | "territory"=>"Territory A"

or

    id |    key     |    value    
----+-------------+-------+---
    1  |   route     | Route 23
    2  |   route     | Route 22
    3  |   region    | Northwest
    4  |   territory | Territory A

Doesnt really matter, just whatever works to build up that where clause. It could potentially have 1 to n number of OR's in it based on the other query.

Upvotes: 2

Views: 56

Answers (1)

stsmurf
stsmurf

Reputation: 472

Ended up with a solution using the 2nd table (distribution table):

    id   |        metadata           
---------+----------------------------
    1647 | "region"=>"Northwest"
    1648 | "route"=>"Route 23"
    1649 | "route"=>"Route 22"
    1650 | "territory"=>"Territory A"

Used the following join, which the @> sees if the places.metadata contains the distributions.metadata

SELECT places.id, places.metadata
    FROM places INNER JOIN distributions
    ON places.metadata @> distributions.metadata
    WHERE distributions.some_other_column = something;

Upvotes: 1

Related Questions