How to perform a pattern matching query on the keys of a hstore/jsonb column?

Question

I'm trying to perform a pattern matching on an hstore column on a Postgresql database table.

Here's what I tried:

SELECT
  *
FROM
 products
WHERE
  'iphone8' LIKE ANY(AVALS(available_devices))

however, it seems that the ANY operator only supports <, <=, <>, etc.

I also tried this:

SELECT
  *
FROM
 products
WHERE
  ANY(AVALS(available_devices)) LIKE 'iphone8'

but then it raises a SyntaxError.

So, can I do a query with a WHERE clause in which I pass a parameter and the results of the query are the rows that contain any key in the informed hstore_column that match the given parameter?

eg: for rows

id | hstore_column
1       { country: 'brazil' }
2       { city: 'amsterdam' }
3       { state: 'new york' }
4       { count: 10 }
5       { counter: 'Irelia' }

I'd like to perform a WHERE with a parameter 'count' and I expect the results to be:

id  |  hstore_column
1       { country: 'brazil' }
4       { count: 10 }
5       { counter: 'Irelia' }

Answer 1

You can use jsonb_object_keys to turn the keys into a column. Then match against the key.

For example, here's my test data.

select * from test;

 id |                    stuff                    
----+---------------------------------------------
  1 | {"country": "brazil"}
  2 | {"city": "amsterdam"}
  3 | {"count": 10}
  4 | {"pearl": "jam", "counting": "crows"}
  5 | {"count": "chocula", "count down": "final"}

Then we can use jsonb_object_keys to turn each key into its own row.

select id, stuff, jsonb_object_keys(stuff) as key
from test;

 id |                    stuff                    |    key     
----+---------------------------------------------+------------
  1 | {"country": "brazil"}                       | country
  2 | {"city": "amsterdam"}                       | city
  3 | {"count": 10}                               | count
  4 | {"pearl": "jam", "counting": "crows"}       | pearl
  4 | {"pearl": "jam", "counting": "crows"}       | counting
  5 | {"count": "chocula", "count down": "final"} | count
  5 | {"count": "chocula", "count down": "final"} | count down

This can be used in a sub-select to get each matching key/value pair.

select id, stuff, key, stuff->key as value
from (
  select id, stuff, jsonb_object_keys(stuff) as key
  from test
) pairs
where key like 'count%';

 id |                    stuff                    |    key     |   value   
----+---------------------------------------------+------------+-----------
  1 | {"country": "brazil"}                       | country    | "brazil"
  3 | {"count": 10}                               | count      | 10
  4 | {"pearl": "jam", "counting": "crows"}       | counting   | "crows"
  5 | {"count": "chocula", "count down": "final"} | count      | "chocula"
  5 | {"count": "chocula", "count down": "final"} | count down | "final"

Or we can use distinct to get just the matching rows.

select distinct id, stuff
from (
  select id, stuff, jsonb_object_keys(stuff) as key
  from test
) pairs
where key like 'count%';

 id |                    stuff                    
----+---------------------------------------------
  1 | {"country": "brazil"}
  3 | {"count": 10}
  4 | {"pearl": "jam", "counting": "crows"}
  5 | {"count": "chocula", "count down": "final"}

dbfiddle

---

Note: having to search the keys indicates your data structure might need rethinking. A traditional key/value table might work better. The values can still be jsonb. There's a little more setup, but the queries are simpler and it is easier to index.

create table attribute_group (
  id bigserial primary key
);

create table test (
  id bigserial primary key,
  attribute_group_id bigint
    references attribute_group(id) 
    on delete cascade
);

create table attributes (
  attribute_group_id bigint
    references attribute_group(id) not null,
  key text not null,
  value jsonb not null
);

select test.id, attrs.key, attrs.value
from test
join attributes attrs on attrs.attribute_group_id = test.attribute_group_id
where attrs.key like 'count%';

dbfiddle