How to search in table using nested hstore column?

41 Views Asked by At

I'm new to PostgreSQL and recently I had contact to hstore, I'm looking for learn more about it.

This is my current situation:

I have a people table with a column phones, that is a hstore. One person can have multiple phones. The column seems like this:

"0"=>"{\"phone\":\"00000000000\",\"phone_type\":\"cell_phone\"}",
"1"=>"{\"phone\":\"11111111111\",\"phone_type\":\"cell_phone\"}", "__TYPE__"=>"__ARRAY__"

How could I make a query to find a person along the table with specific number?

I tried something like this, but it only allows me to search in the first item of hstore

SELECT
  "people".*
FROM
  "people"
WHERE
  "people"."organization_id" = 1
  AND ( NULLIF( regexp_replace( people.phones -> '0' , '[^0-9]*', '', 'g'), '') ilike '%00000000000%' )

0

There are 0 best solutions below