JSONB[]上的PostgreSQL create index

Question

考虑如下定义的表：

CREATE TABLE test (
    id int4 NOT NULL,
    tag_counts _jsonb NOT NULL DEFAULT ARRAY[]::jsonb[]
);

INSERT INTO test(id, tag_counts) values(1,array['{"type":1, "count":4}','{"type":2, "count":10}' ]::jsonb[])

如何在json key type上创建索引，如何查询？

Edit:之前json keys没有索引，select查询使用unnest操作，如下图所示：

select * from (SELECT unnest(tag_counts) as tc
FROM public.test) as t
where tc->'type' = '2';

问题是，如果表有大量行，上面的查询不仅包括全表扫描，还包括过滤每个jsonb数组。

Answer 1

有一种方法可以索引它，不确定它会有多快。

如果这是一个“常规”jsonb列，那么可以使用where tag_counts @> '[{"type": 2}]'这样的条件，该条件可以在列上使用GIN索引。

如果将数组转换为“普通”json值，则可以使用该运算符：

select *
from test
where to_jsonb(tag_counts) @> '[{"type": 2}]'

不幸的是，to_jsonb()没有被标记为不可变的(我猜是因为可能存在时间戳转换)，如果您想在索引中使用表达式，这是必需的。

但是对于您的数据，这确实是不可变的，所以我们可以创建一个小的包装器函数：

create function as_jsonb(p_input jsonb[])
returns  jsonb
as
$$
  select to_jsonb(p_input);
$$
language sql
immutable;

通过这个函数，我们可以创建一个索引：

create index on test using gin ( as_jsonb(tag_counts) jsonb_path_ops);

您将需要在查询中使用该函数：

select *
from test
where as_jsonb(tag_counts) @> '[{"type": 2}]'

在一个有一百万行的表上，我得到了以下执行计划：

Bitmap Heap Scan on stuff.test  (cost=1102.62..67028.01 rows=118531 width=252) (actual time=15.145..684.062 rows=147293 loops=1)
  Output: id, tag_counts
  Recheck Cond: (as_jsonb(test.tag_counts) @> '[{"type": 2}]'::jsonb)
  Heap Blocks: exact=25455
  Buffers: shared hit=25486
  ->  Bitmap Index Scan on ix_test  (cost=0.00..1072.99 rows=118531 width=0) (actual time=12.347..12.356 rows=147293 loops=1)
        Index Cond: (as_jsonb(test.tag_counts) @> '[{"type": 2}]'::jsonb)
        Buffers: shared hit=31
Planning:
  Buffers: shared hit=23
Planning Time: 0.444 ms
Execution Time: 690.160 ms