PostgreSQL可以将更深层次的路径索引到JSON列中吗

Question

我正准备使用PostgreSQL的json存储和查询功能。我想我对插入和查询部分有所了解，但我找不到一个如何支持(通过索引(查询到一个不止一级深度的json路径的例子。我正在测试以下内容：

CREATE TABLE orders (
id serial NOT NULL PRIMARY KEY,
info json NOT NULL
);
INSERT INTO orders (info)
VALUES('{ "customer": "Mark Stevens", "items": {"product": { "name" : "Phone","qty": 3}}}'),
('{ "customer": "Josh William", "items": {"product": { "name" : "Toy Car","qty": 1}}}'),
('{ "customer": "Mary Clark", "items": {"product": { "name" : "Toy Train","qty": 2}}}');

这是一个构建的例子。本质是，数量字段嵌套的深度超过一个级别。

我可以对此执行查询，查找数量为1:的所有记录

SELECT * FROM orders
WHERE info::jsonb @@ '$.items.product.qty == 1';

这一切都很好。使用PgAdmin进行测试。因此，现在我想定义一个索引来支持该查询(或者可以由索引支持的索引版本，因为编写查询的方式通常很重要(。

我一直在这里和pg文档中寻找，但没有看到一个索引定义的例子，我可以将其转化为这个例子的工作定义。所有的例子似乎只涵盖一个层次的路径。例如，如果qty是json中的一个级别，那么支持索引将类似于

CREATE INDEX orders_index ON orders (((info ->> 'customer')::VARCHAR), ((info #>> '{items, qty}')::INTEGER));

所以，我的问题是：有可能想出一个索引来支持我对更深层次json的查询吗？如果是，有人能给我举个例子吗？

Answer 1

您应该定义列jsonb，在这里使用json没有优势。以下假设为jsonb列，而不是json列

您可以在列上定义GIN索引：

create index on orders using gin (info);

或者，对于一个稍小且更有效的索引：

create index on orders using gin (info jsonb_path_ops);

然后查询：

SELECT * 
FROM orders
WHERE info @@ '$.items.product.qty == 1';

可以在通常的限制下使用该索引。如果你只有几百行，这可能不会被使用。

备选查询：

SELECT * 
FROM orders
WHERE info @> '{"items": {"product" : {"qty": 1}}}'

也将使用该索引。

---

如果您知道您将始终查看例如products部分：，则可以创建较小的(GIN(索引

create index on orders using gin ( (info #> '{items,products}') );

但是你需要调整你的查询：

SELECT * 
FROM orders
WHERE info #> '{items,products}' @@ '$.qty == 1';

你可以在这里看到支持的JSONB操作符

---

如果您总是想查询数量，那么B树索引可能就足够了。B-Tree索引比GIN索引小，而且保持它们最新的开销也较小。

create index on orders ( ((info #>> '{items,product,qty}')::int) );

然后以下查询将使用该索引：

SELECT * 
FROM orders
WHERE (info #>> '{items,product,qty})::int = 1;