如何索引jsonb整数值

Question

我试图使用"新式"的JSONB类型。

我有一个名为documents的表，其中有一个properties字段是jsonb类型，并且其中有一个名为publication_year的字段。我想找出所有在某个年份范围内的文档记录，例如2013-2015年。[编辑：在这里查询一系列值是主要挑战，即使我在下面使用了精确匹配的示例。所请求的方法也适用于比如价格范围（价格> $20并且价格< $40）或时间戳范围。]

我尝试过：

create index test1 on documents using gin ((cast(properties->'announced_on_year' as integer)));

ERROR:  cannot cast type jsonb to integer

以及：

create index test1 on documents using gin (cast(properties->>'publication_year' as integer));

ERROR:  data type integer has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.`

我从这篇帖子中看到 http://www.postgresql.org/message-id/[e-mail] ，它说这应该是可能的，但我无法找出正确的语法。

当我只是简单地建立索引时：

create index test1 on documents using gin ((properties->'publication_year'));

我创建了一个索引，但是无法使用整数值查询它以获取范围，出现以下错误：

select count(*) from documents where properties->>'publication_year' = 2015;
ERROR:  operator does not exist: text = integer
LINE 1: ...*) from documents where properties->>'publication_year' = 2015;
                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

任何提示和建议都非常感激。我相信其他人也会受益。TIA

- Will Kessler

Answer 1

我发现在使用GIN索引处理JSONB列时，并不能提高速度。你可以通过将其转换为整数来创建普通索引。

CREATE INDEX test1 ON documents ((properties->>'publication_year')::int);

此外，在创建GIN索引之前，应考虑到GIN存在一些限制。即使对整个JSONB列进行索引，也可能导致庞大的表大小索引。

这是基于我的经验和查阅Postgres文档得出的结论。

- qwerty

Answer 2

1) 对于整数类型，没有默认提供GIN索引，可以使用B树索引。

create index test1 on documents using btree (cast (properties->>'announced_on_year' as int));

2) 错误信息已经很明显了，将整数转换为文本或使用文本进行比较：

select count(*) from documents where properties->>'publication_year' = '2015';

- Jakub Kania

---

谢谢，这很有帮助，但它并没有解决我的主要问题：范围查询（也许我应该更明确）。最终目标是找到所有publication_year在2012年之后且在2016年之前的记录。 - Will Kessler

@WillKessler 你可以使用B树索引来实现。你试过了吗？ - Jakub Kania

我曾尝试过，但是查询语句一直不正确。但是现在有了你的帮助和murison的指导，我已经成功了，可以看上面对murison的回应。无论如何感谢你的帮助。 - Will Kessler

Answer 3

您可以将其转换为整数并使用contrib / btree_gin扩展。

创建扩展btree_gin;
在tt表中使用gin（cast（jb-> -'price' as int））创建索引tt_jb_int_idx;
解释分析选择* from tt where cast（jb-＆gt; - 'price' as int）> 3并且cast（jb-＆gt; - 'price' as int）>'price'：：text）:: integer > 3）AND（（（jb-＆gt; - 'price'：：text））：：int）Bitmap Index Scan on tt_jb_int_idx（cost = 0.00..28.06 rows = 6 width = 0）（实际时间= 0.016..0.016 rows = 1 loops = 1）
         索引条件：（（（（jb-＆gt; - 'price'：：text））：：int）> 3）AND（（（（jb-＆gt; - 'price'：：text））：：int）

- user1821441

Answer 4

为什么不像文档中描述的那样为整个 jsonb 字段定义索引呢？

create index test1 on documents using gin (properties);

- murison

---

谢谢 - 我认为这就是我正在寻找的答案。即使文档没有明确说明，您也可以使用此解决方案使用CAST获取范围结果，例如：`EXPLAIN ANALYZE SELECT COUNT(*) FROM documents WHERE cast(properties->>'publication_year' AS integer) > 2012 AND cast(properties->>'publication_year' AS integer) < 2016; - Will Kessler

我不确定这样使用会不会影响性能（我猜这是目标）。根据文档，"->>"运算符不支持此类型的索引。此外，您可以将x > A AND x < B组合成x BETWEEN A AND B。 - murison