0

Input:

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

returns

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

Expected:

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

Which words have index 2 and 4? Is this a bug? How do I use it in a valid way?

With those gaps in the index numbering any search using a followed-by operator (ex: 'one' <-> 'zwo') is doomed to fail.

How I can repair that using immutable existing functions? The jsonb originates from a call to jsonb_path_query_array() which could be changed to jsonb_path_query() but I have no clue how to consolidate a set of jsonb values into a single tsvector.

springy76
  • 3,706
  • 2
  • 24
  • 46

1 Answers1

1

I'd say that a phrase query across multiple JSON array elements is, er, weird.

You could work around that by casting the JSON to text:

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

        to_tsvector        
═══════════════════════════
 'one':1 'three':3 'two':2
(1 row)

That type cast is IMMUTABLE.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I never had an interest in json elements, all I wanted was a space delimited text-blob out of an existing large jsonb document by applying `jsonb_path_query_array("BigJson", '$.Pages[*].TextBlocks[*].Lines[*].Words[*].Text')` - just to feed it into `to_tsvector`. – springy76 Jan 20 '23 at 18:20