postgresql full text proximity (`<->`) search on json document
NickName:Haleemur Ali Ask DateTime:2019-09-03T09:20:03

postgresql full text proximity (`<->`) search on json document

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

More about “postgresql full text proximity (`<->`) search on json document” related questions

suggest like google with postgresql trigrams and full text search

I want to do a text search like google suggestions. I'm using PostgreSQL because of the magical Postgis. I was thinking on using FTS, but I saw that it could not search partial words, so I found ...

Show Detail

MySQL Innodb Full text Proximity Search Gives Horrible Performance

I have a mysql database with a simple table named item. Item contains the following fields and has 55,000 records: ID (PK) Description (INNODB FULL TEXT) DATE By design i am forced to index all ...

Show Detail

postgresql full text proximity (`<->`) search on json document

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 ...

Show Detail

PostgreSQL Full Text Search with substrings

I'm trying to create the fastest way to search millions (80+ mio) of records in a PostgreSQL (version 9.4), over multiple columns. I would like to try and use standard PostgreSQL, and not Solr etc...

Show Detail

Postgresql Misspelling in Full Text Search

I'm using postgresql to perform Full Text Search and I am finding that users will not receive results if there are misspellings. What is the best way to handle misspelt words in Postgres full text

Show Detail

Full text search on PostgreSQL

Now I'm learning about full text search in PostgreSQL 9.2.3. However, I have a problem. I run this example: CREATE TABLE messages (title text,body text,tsv tsvector); CREATE TRIGGER tsvectorupdate

Show Detail

PostgreSQL Full Text Search and Trigram Confusion

I'm a little bit confused with the whole concept of PostgreSQL, full text search and Trigram. In my full text search queries, I'm using tsvectors, like so: SELECT * FROM articles WHERE search_vect...

Show Detail

PostgreSQL Full Text search

I need to use Full Text Search with Postgresql but I don't find the way to look for a list of words from a table (using ts_query) against an indexed text field (ts_vector data type). Is ts_query just

Show Detail

PostgreSQL full text search abbreviations

I created a Postgresql full text search using 'german'. How can I configer, that when I search for "Bezirk", lines containing "Bez." are also a match? (And vice-versa)

Show Detail

Full Text Search in Postgresql

I'm looking for tutorials to implement a full text search method in postgresql. I have tried several examples but no one performing. I tried indexing GIN, GIST, and several research method, but se...

Show Detail