PostgreSQL JSON fast search (search value from any key)
I am trying to find a solution to quickly search a PostgreSQL JSONB column. The requirement is that we can search for a value in any JSON key.
Table structure:
CREATE TABLE object (id bigint NOT NULL, jtype character variable (64) NOT NULL, jdata jsonb, CONSTRAINT entity_pk PRIMARY KEY (id))
The idea is that we store different jsons of types in one table, jtype defines json entity type, jdata-json data, for example:
jtype='person',jvalue = '{"personName":"John", "personSurname":"Smith", "company":"ABS Software", "position":"Programmer"}'
jtype='company', jvalue='{"name":"ABS Software", "address":"Somewhere in Alaska"}'
The goal is to do a quick search that the user can type in "ABS" and find both the company and the person who works for the company.
The analog for Oracle DB is the CONTAINS function:
SELECT jtype, jvalue FROM entity WHERE CONTAINS (jvalue, 'ABS')> 0;
GIN index only allows searches for key / value pairs
GIN indices can be used to efficiently find keys or key / value pairs found in a large number of jsonb documents (raw data). Two GIN "operator classes" are provided, offering different performance and flexible tradeoffs.
https://www.postgresql.org/docs/current/static/datatype-json.html#JSON-INDEXING
source to share
https://github.com/postgrespro/jsquery might be helpful for what you're looking for, although I haven't used it before.
source to share
As of Postgresql 10, you can create indexes on JSON / JSONB columns and then do full-text search within the values for that column as such:
libdata=# SELECT bookdata -> 'title'
FROM bookdata
WHERE to_tsvector('english',bookdata) @@ to_tsquery('duke');
------------------------------------------
"The Tattooed Duke"
"She Tempts the Duke"
"The Duke Is Mine"
"What I Did For a Duke"
Additional documentation can be found here .
source to share