如何在PostgreSQL含嵌套hstore列的表中搜索特定数据？

你的查询仅针对hstore中键为'0'的条目，所以只能匹配第一个电话。要遍历所有存储的电话条目，可以通过以下方式实现：

基础匹配查询 #

SELECT people.*
FROM people
WHERE organization_id = 1
AND EXISTS (
  SELECT 1
  FROM svals(people.phones) AS phone_json_str
  WHERE (phone_json_str::json ->> 'phone') ilike '%00000000000%'
);

语句说明： #

• svals(people.phones)：提取hstore字段中的所有值，返回一个包含所有电话JSON字符串的行集合。
• phone_json_str::json：将每个hstore值（JSON格式的字符串）转换为PostgreSQL的JSON类型，方便提取内部字段。
• ->> 'phone'：从JSON对象中提取phone字段的文本值。
• ilike '%00000000000%'：模糊匹配目标电话号码，若需精确匹配，直接替换为= '00000000000'即可。

带非数字字符过滤的查询 #

如果需要先去除电话号码中的非数字字符再匹配（和你原查询的逻辑一致），可以修改条件：

SELECT people.*
FROM people
WHERE organization_id = 1
AND EXISTS (
  SELECT 1
  FROM svals(people.phones) AS phone_json_str
  WHERE NULLIF(regexp_replace((phone_json_str::json ->> 'phone'), '[^0-9]*', '', 'g'), '') ilike '%00000000000%'
);

额外建议 #

如果你的PostgreSQL版本在10及以上，更推荐使用jsonb类型存储这类结构化的多条目数据（比如直接存储包含电话数组的jsonb对象），相比hstore，jsonb对嵌套结构的支持更原生，查询和维护都会更直观。

内容的提问来源于stack exchange，提问作者Vinícius Lisboa