主页 / dba / 问题 / 89971
Accepted
jgm
Asked: 2015-01-23 14:02:54 +0800 CST

PostgreSql JSONB SELECT 针对多个值

  • 772

我有一个非常简单的 JSON 表，其中填充了一些示例数据：

CREATE TABLE jsonthings(d JSONB NOT NULL);

INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}');
INSERT INTO jsonthings VALUES ('{"name":"Second","tags":["foo","bar"]}');
INSERT INTO jsonthings VALUES ('{"name":"Third","tags":["bar","baz"]}');
INSERT INTO jsonthings VALUES ('{"name":"Fourth","tags":["baz"]}');

CREATE INDEX ON jsonthings USING GIN(d);

并且在运行SELECT. 一个简单SELECT的获取值为单个项目的行就可以了：

SELECT d FROM jsonthings WHERE d @> '{"name":"First"}';

但是，当尝试运行与多个值匹配的查询时，name我不知道如何使用索引。我试过了：

SELECT d FROM jsonthings WHERE d->>'name' = ANY(ARRAY['First', 'Second']);
SELECT d FROM jsonthings WHERE d->'name' ?| ARRAY['First', 'Second'];
SELECT d FROM jsonthings WHERE d#>'{name}' ?| ARRAY['First','Second'];

并且它们都显示了对表的顺序扫描（如果可能的话，我enable_seqscan=false用来强制使用索引）。有什么方法可以重写查询以使其使用索引？我知道我可以这样做：

SELECT * FROM jsonthings WHERE d @> '{"name":"First"}' OR d @> '{"name":"Second"}';

但是后来我有一个可变长度的查询，并且我正在通过 JDBC，所以会失去查询作为 PreparedStatement 的好处。

我也有兴趣看到针对tags密钥中许多项目中的任何一个的类似查询，例如：

SELECT d FROM jsonthings WHERE d @> '{"tags":["foo"]}' OR d @> '{"tags":["bar"]}';

但是使用一个ARRAY而不是多个条件并使用一个索引。

这是在 PostgreSql 9.4 上。

postgresql json

2 个回答

  1. 这是对 Mladen 提供的答案的回应。我没有足够的声誉来发表评论，但我想回复，因为看起来查询可能不正确，并且让我感到困惑，并且将来可能会导致其他人感到困惑。

    你提到使用：

    SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';

    First但是，要检索具有或作为名称的任何条目Second，这似乎对我不起作用PostgreSQL 9.4.4：

    SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';
 d
---
(0 rows)

    似乎上面的查询正在尝试检索name属性包含数组的条目["First", "Second"]。

    如果我创建这样一个条目：

    INSERT INTO jsonthings VALUES ('{"name":["First", "Second"],"tags":["baz"]}');

    然后再次尝试查询，它返回一个结果：

    SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';
d
------------------------------------------------
{"name": ["First", "Second"], "tags": ["baz"]}
(1 row)

    name但是，这与原始发帖人提出的问题不同，即在查询属性为First or 的条目时如何使用索引Second：

    SELECT * FROM jsonthings WHERE d @> '{"name":"First"}' OR d @> '{"name":"Second"}';

    我想在这里提供这个，所以其他人认为不可能通过提供 JSON来执行OR"name": ["First", "Second"]查询，因为它具有误导性。

    • 6
  2. Best Answer

    从文档（http://www.postgresql.org/docs/9.4/static/datatype-json.html）尝试使用表达式索引：

    CREATE INDEX idx_jsonthings_names ON jsonthings USING gin ((d -> 'name'));
SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';
    • 3

相关问题