查询表的Postgres与JSONB数据

Question

我有在JSONB列存储的数据的表。

现在，我想要做的是，查询该表，并获取记录，其中有一个关键的具体数值。

这工作得很好：

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

但我想要做的是，取表中的所有行，其中有各类foo OR bar。

我尝试这样做：

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

但这似乎并没有工作。

我也试过这样：

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

其中一期工程，但如果我指定像这样data->'type'关键它带走查询的动态性。

顺便说一句，我使用在Postgres Rails的Ruby的，所以所有的查询都经历ActiveRecord。这就是：

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

Answer 1

如果我指定像这样的数据的关键 - >“类型”它带走查询的动态性。

我明白你有这样定义的列data杜松子酒指数：

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.使用OP爱适易@>两次，索引将被用于这两个条件：

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的一个变体，条件的不同的格式，其可以是由客户端程序，使用更方便：

​​

更多在the documentation。