使用JSONB数据查询Postgres表

Question

我是一名有用的助手，可以为您翻译文本。

我有一个表，其中以JSONB列存储数据。

现在，我想要做的是查询该表并获取记录，这些记录具有特定键的特定值。

以下内容可正常工作：

SELECT "documents".*
FROM "documents"
WHERE (data @> '{"type": "foo"}')

但是我想做的是获取表格中所有类型为foo或者bar的行。

我尝试了以下方法：

SELECT "documents".*
FROM "documents"
WHERE (data @> '{"type": ["foo", "bar"]}')

但是这似乎不起作用。

我还尝试了这个：

SELECT "documents".*
FROM "documents"
WHERE (data->'type' ?| array['foo', 'bar'])

这个方法是可以使用的，但如果我像这样指定一个键data->'type'，它会削弱查询的动态性。

顺便说一下，我正在使用Ruby on Rails和Postgres，因此所有的查询都通过ActiveRecord进行。如下：

Document.where("data @> ?", query)

- Gaurav Manchanda

Answer 1

如果我像这样指定一个键 data->'type'，它会削弱查询的动态性。

我了解您在列data上定义了一个gin索引，如下所示：

CREATE INDEX ON documents USING GIN (data);

这个查询可用于索引：

EXPLAIN ANALYSE
SELECT "documents".*
FROM "documents"
WHERE data @> '{"type": "foo"}';

                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on documents  (cost=30.32..857.00 rows=300 width=25) (actual time=0.639..0.640 rows=1 loops=1)
   Recheck Cond: (data @> '{"type": "foo"}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on documents_data_idx  (cost=0.00..30.25 rows=300 width=0) (actual time=0.581..0.581 rows=1 loops=1)
         Index Cond: (data @> '{"type": "foo"}'::jsonb)
 Planning time: 7.928 ms
 Execution time: 0.841 ms

但对于这个不行：

EXPLAIN ANALYSE
SELECT "documents".*
FROM "documents"
WHERE (data->'type' ?| array['foo', 'bar']);

                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Seq Scan on documents  (cost=0.00..6702.98 rows=300 width=25) (actual time=31.895..92.813 rows=2 loops=1)
   Filter: ((data -> 'type'::text) ?| '{foo,bar}'::text[])
   Rows Removed by Filter: 299997
 Planning time: 1.836 ms
 Execution time: 92.839 ms

解决方案1：使用操作符@>两次，索引将用于两个条件：

EXPLAIN ANALYSE
SELECT "documents".*
FROM "documents"
WHERE data @> '{"type": "foo"}'
OR data @> '{"type": "bar"}';

                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on documents  (cost=60.80..1408.13 rows=600 width=25) (actual time=0.222..0.233 rows=2 loops=1)
   Recheck Cond: ((data @> '{"type": "foo"}'::jsonb) OR (data @> '{"type": "bar"}'::jsonb))
   Heap Blocks: exact=2
   ->  BitmapOr  (cost=60.80..60.80 rows=600 width=0) (actual time=0.204..0.204 rows=0 loops=1)
         ->  Bitmap Index Scan on documents_data_idx  (cost=0.00..30.25 rows=300 width=0) (actual time=0.144..0.144 rows=1 loops=1)
               Index Cond: (data @> '{"type": "foo"}'::jsonb)
         ->  Bitmap Index Scan on documents_data_idx  (cost=0.00..30.25 rows=300 width=0) (actual time=0.059..0.059 rows=1 loops=1)
               Index Cond: (data @> '{"type": "bar"}'::jsonb)
 Planning time: 3.170 ms
 Execution time: 0.289 ms

解决方案2. 在(data->'type')上创建一个额外的索引:

CREATE INDEX ON documents USING GIN ((data->'type'));

EXPLAIN ANALYSE
SELECT "documents".*
FROM "documents"
WHERE (data->'type' ?| array['foo', 'bar']);

                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on documents  (cost=30.32..857.75 rows=300 width=25) (actual time=0.056..0.067 rows=2 loops=1)
   Recheck Cond: ((data -> 'type'::text) ?| '{foo,bar}'::text[])
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on documents_expr_idx  (cost=0.00..30.25 rows=300 width=0) (actual time=0.035..0.035 rows=2 loops=1)
         Index Cond: ((data -> 'type'::text) ?| '{foo,bar}'::text[])
 Planning time: 2.951 ms
 Execution time: 0.108 ms   

解决方案3。实际上这是解决方案1的一个变体，使用了不同格式的条件语句，这可能更方便客户程序使用：

EXPLAIN ANALYSE
SELECT "documents".*
FROM "documents"
WHERE data @> any(array['{"type": "foo"}', '{"type": "bar"}']::jsonb[]);

                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on documents  (cost=60.65..1544.20 rows=600 width=26) (actual time=0.803..0.819 rows=2 loops=1)
   Recheck Cond: (data @> ANY ('{"{\"type\": \"foo\"}","{\"type\": \"bar\"}"}'::jsonb[]))
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on documents_data_idx  (cost=0.00..60.50 rows=600 width=0) (actual time=0.778..0.778 rows=2 loops=1)
         Index Cond: (data @> ANY ('{"{\"type\": \"foo\"}","{\"type\": \"bar\"}"}'::jsonb[]))
 Planning time: 2.080 ms
 Execution time: 0.304 ms
(7 rows)

阅读文档以了解更多相关IT技术。

- klin

---

感谢您的回复，@klin。 解决方案 1 可以工作，但我考虑了其他一些方式，因为 Rails 是 API 部分，可能会有任何类型的请求，而我不想涉及解析参数。因此，我在考虑一些更清晰的方法，我觉得应该存在，因为这只是一个基本操作。你怎么看？ - Gaurav Manchanda

我知道你的意思，这就是为什么我写了第二个解决方案。没有更简洁的了。 - klin

是的，但属性可能会改变，它不总是类型，因此解析将再次成为问题。 - Gaurav Manchanda

谢谢@klin。方案3看起来很有前途。 - Gaurav Manchanda