从jsonb构造tsvector时索引编号有间隔，无法使用followed-by运算符

问题重现 #

执行SQL语句：

select to_tsvector('simple', '["one","two","three"]'::jsonb)

实际返回结果：

'one':1 'three':5 'two':3

预期结果应为：

'one':1 'three':3 'two':2

疑问点：

• 结果中索引2和4对应的是什么内容？
• 这是bug吗？
• 索引间隔导致使用followed-by运算符（如'one' <-> 'two'）的搜索失败，该如何修复？
• 如何将jsonb_path_query()返回的一组jsonb值合并为单个tsvector？

原因解析 #

这不是bug，索引2和4对应的是原json数组中的逗号分隔符。to_tsvector处理jsonb类型时，会逐字符遍历整个json结构，包括数组的括号、逗号等符号，然后提取其中的文本词并记录它们在原始json字符串中的位置。那些结构符号（逗号、括号）不会被加入最终的tsvector，但它们占用了位置索引，所以导致最终的词索引出现间隔。

比如原json数组["one","two","three"]的字符序列位置对应关系是：1对应"one"、2对应逗号、3对应"two"、4对应逗号、5对应"three"，因此提取的词索引就是1、3、5。

解决方法 #

方法1：将jsonb数组转为文本数组后再生成tsvector #

先把jsonb数组拆分为文本数组，用空格拼接成字符串后再转tsvector，这样就能得到连续的索引：

select to_tsvector('simple', array_to_string(array(select jsonb_array_elements_text('["one","two","three"]'::jsonb)), ' '));

返回结果：

'one':1 'three':3 'two':2

方法2：合并jsonb_path_query()的结果生成tsvector #

如果数据来自jsonb_path_query()，可以用string_agg将查询到的文本值拼接成字符串，再转换为tsvector：

select to_tsvector('simple', string_agg(value::text, ' '))
from jsonb_path_query('["one","two","three"]'::jsonb, '$[*]');

方法3：封装为不可变函数（支持索引场景） #

如果需要在索引中使用该逻辑，需要封装成不可变函数（PostgreSQL要求索引使用的函数必须是immutable）：

create or replace function jsonb_array_to_tsvector(config regconfig, j jsonb) 
returns tsvector 
as $$
select to_tsvector(config, string_agg(value::text, ' '))
from jsonb_array_elements_text(j);
$$ language sql immutable;

调用方式：

select jsonb_array_to_tsvector('simple', '["one","two","three"]'::jsonb);

返回结果符合预期，且该函数可用于创建GIN/GIST索引。

内容的提问来源于stack exchange，提问作者springy76