I'm experiencing some unexpected behaviour for certain word combinations trying to use full text search on a json(b) document. Perhaps my understanding of text search is is incorrect or maybe this is a known bug.
Here's a simple example:
-- searching on text, expected true, query returned true
select to_tsvector('the quick brown fox jumped over the lazy dog')
@@ to_tsquery('fox <-> jump');
?column?
----------
t
(1 row)
-- searching json, expected true, but query returns false
select to_tsvector('{"example": ["the quick brown fox", "jumped over the lazy dog"]}'::json)
@@ to_tsquery('fox <-> jump');
?column?
----------
f
(1 row)
The unexpected behaviour seems to only happen across text elements for certain words, for instance, searching for fox <-> over
works in the following example.
-- expected this to be true, and I get true back
select to_tsvector('["jumped the quick brown fox", "over the lazy dog"]'::json)
@@ to_tsquery('fox <-> over');
?column?
----------
t
(1 row)
and similarly, if fox jumped
were in the same text element, then the query returns true, as expected.
select to_tsvector('["the quick brown fox jumped", "over the lazy dog"]'::json)
@@ to_tsquery('fox <-> jump');
?column?
----------
t
(1 row)
postgresql / system version tested on:
select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 10.1 on x86_64-apple-darwin15.6.0, compiled by Apple LLVM version 8.0.0 (clang-800.0.42.1), 64-bit
(1 row)
Copyright Notice:Content Author:「Haleemur Ali」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/57763985/postgresql-full-text-proximity-search-on-json-document