Hstore search on values with wildcard

Question

Using PostgreSQL, I want to be able to find all of the key-value pairs in an HStore field where the value matches a given query. So, for example, given a table like the following:

Name        Parentage (Hstore)
_____       ___________________
Rover       Mother => Yellow Lab, Father => Black Lab
Fido        Mother => Black Lab, Father => Rottweiler
Rex         Mother => Labrador Retriever, Father => Springer Spaniel 
Lassie      Mother => Border Collie, Father => Collie

How could I do a query for any dog that has a '%lab%' in its family tree? I.e. the search would bring up Rover, Fido and Rex, but not Lassie. The only examples I've seen are of searches within a single key - I need a search within all values that allows wildcards.

I have looked at this similar question, but it seems to only do a search on a particular key, not on all of the values found in the Hstore field across all keys.

Note that this is a constructed example, I was trying to make it accessible. In my actual database, I have keys for language codes followed by values of translations of the same words in the different language. I need to be able to do a search that could hit any of the values, regardless of what language it is in.

Answer 1

Break out the hstore into rows of name/parent and then select distinct names of dogs where the parent column matches your search criteria.

Depending on the nature of your actual data, this may want additional indexes. I wouldn't use hstore for this, but your actual data may be different.

% psql -qe -f hstore.sql
begin;
create extension hstore;
create temp table dogs (
    "name" text,
    parentage hstore
);
insert into dogs values
('Rover', 'Mother => "Yellow Lab",Father => "Black Lab"')
,('Fido', 'Mother => "Black Lab",Father => "Rottweiler"')
,('Rex', 'Mother => "Labrador Retriever",Father => "Springer Spaniel"')
,('Lassie', 'Mother => "Border Collie",Father => "Collie"')
;
table dogs;
  name  |                          parentage                           
--------+--------------------------------------------------------------
 Rover  | "Father"=>"Black Lab", "Mother"=>"Yellow Lab"
 Fido   | "Father"=>"Rottweiler", "Mother"=>"Black Lab"
 Rex    | "Father"=>"Springer Spaniel", "Mother"=>"Labrador Retriever"
 Lassie | "Father"=>"Collie", "Mother"=>"Border Collie"
(4 rows)

select * from dogs where "name" in
(select distinct "name" from (
            select "name", unnest(avals(parentage)) as parent
        ) as plist
        where parent ilike '%lab%'
);
 name  |                          parentage                           
-------+--------------------------------------------------------------
 Rover | "Father"=>"Black Lab", "Mother"=>"Yellow Lab"
 Fido  | "Father"=>"Rottweiler", "Mother"=>"Black Lab"
 Rex   | "Father"=>"Springer Spaniel", "Mother"=>"Labrador Retriever"
(3 rows)

rollback;