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%' )