主页 / dba / 问题 / 108447
Accepted
Alex Takitani
Asked: 2015-07-30 06:59:48 +0800 CST

jsonb 可以通过 *value* 甚至部分值进行快速搜索吗？其他类型？

  • 772

我有一个person必须插入任意数据的表。由于我使用的是 Postgres 9.4，jsonb因此似乎是正确的选择。

示例数据：

id: 1, name: "Joe Doe", preferences: { color: "red" , toy: "car"}
id: 2, name: "Jane Doe", preferences: { color: "blue", food: "hamburguer" }

问题是我需要通过变量值来查询它，例如：

所有偏好有“汉堡”的人，部分查询肯定也是可以的，所以搜索“汉堡”一定会给我带来记录2。

使用json函数，我可以搜索值，只要我知道key，对吧？还是有一种快速搜索值的方法？

我想做的是创建一个 tsvector 列并转储 json 以使用全文搜索来执行部分查询。

database-design postgresql

1 个回答

  1. Best Answer

    对于Postgres 12或更高版本，请考虑 SQL/JSON 路径语言。看：

    原始答案（对于旧版本）

    我建议MATERIALIZED VIEW使用未嵌套的值和三元索引作为搜索工具。

    hstore

    如果您不需要嵌套值，hstore则可能对您更好。使用该函数svals(hstore)取消嵌套hstore值。

    您需要为hstore每个数据库安装一次附加模块：

    CREATE EXTENSION hstore;

    桌子

    CREATE TABLE person (
  person_id int PRIMARY KEY
, name text
, preferences jsonb
);
INSERT INTO person VALUES
  (1, 'Joe Doe', jsonb  '{"toy": "car", "color": "red"}')
, (2, 'Jane Doe', '{"food": "hamburguer", "color": "blue"}')
;

    物化视图

    CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.preference        -- just pref
FROM   person p, svals(p.preferences) j(preference);

    CROSS JOIN LATERAL这是对 set-returning 函数的隐含svals()。

    三元索引

    您需要为pg_trgm每个数据库安装一次附加模块：

    CREATE EXTENSION pg_trgm;

    然后：

    CREATE INDEX person_pref_trgm_idx ON person_pref
USING gin (preference gin_trgm_ops);

    细节：

    询问

    SELECT *
FROM   person p
WHERE  EXISTS (
   SELECT FROM person_pref pp
   WHERE  pp.person_id = p.person_id
   AND    pp.preference ILIKE '%burg%'
   );

    这对于选择性搜索词来说很快。

    jsonb

    如果您有嵌套的值或numeric或boolean值，jsonb可能会更有效。您可以使用以下方法执行与上述相同的操作jsonb_each_text(jsonb)：

    CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.preference
FROM  person p, jsonb_each_text(p.preferences) AS j(key, preference);

    相同的索引，相同的查询。

    db<>在这里摆弄

    • 7

相关问题