Postgres Full Text Search Jsonb Array column does not search for first row

[Dmytro Zhluktenko <d1mnewz@xxxxxxxxx>]

Hey,

Pgsql is unable to perform indexed full text search onto jsonb column containing an array when looking for the first row in the table.

Any ideas why this is happening?

Table is nothing more than just Id and Foo which is jsonb column.

The case is that I have an empty database with predefined gin index repro_fts_idx on make_tsvector function. make_tsvector creates tsvector from given jsonb column.

When I add a new item into the table, I expect it to appear in make_tsvector function in a form of tsvector. It's there. Also, I expect that if I run full text search query onto it, it would appear in search results. However, this is not the case because it returns empty specifically for the first row. It simply does not take it into account. If I add one more row which is completely the same, the system is able to find it with the same query.

here is a small repro case:

-- drop table cp."Repro" cascade

CREATE TABLE cp."Repro" (

    "Id" serial NOT NULL,

    "Foo" jsonb NULL

);

CREATE OR REPLACE FUNCTION cp.make_tsvector(in_t cp."Repro")

 RETURNS tsvector

 LANGUAGE plpgsql

 IMMUTABLE

AS $function$ begin    

                        return to_tsvector(jsonb_agg(x.prop))

                        from (SELECT CONCAT( jsonb_array_elements(in_t."Foo") ->> 'Name', ' ', jsonb_array_elements(in_t."Foo") ->> 'Address' ) as prop from cp."Repro" f) as x;

                        END;

                        $function$

;

CREATE INDEX repro_fts_idx ON cp."Repro" USING gin (cp.make_tsvector(cp."Repro".*)) WITH (fastupdate=off, gin_pending_list_limit='64');

INSERT INTO cp."Repro"

("Foo")

VALUES('[{"Name": "Sup", "Address": "Adress", "IsCurrent": true}]');

-- just in case it's the indexing issue

-- REINDEX INDEX cp.repro_fts_idx;

select * from cp."Repro"

select cp.make_tsvector(x) from cp."Repro" x

select * from ts_stat('select cp.make_tsvector(x) from cp."Repro" x')

-- explain analyze

SELECT *

 FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'sup:*'::tsquery

 INSERT INTO cp."Repro"

("Foo")

VALUES('[{"Name": "Sup", "Address": "Adress", "IsCurrent": true}]');

-- explain analyze

SELECT *

 FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'sup:*'::tsquery

 

 

BR, Dmytro.