PostgreSQL JSON quick search (search value in from any key)

Question

I try to find solution for quick search functionality within PostgreSQL JSONB column. Requirements is that we can search for value in any JSON key.

Table structure:

CREATE TABLE entity ( id bigint NOT NULL, jtype character varying(64) NOT NULL, jdata jsonb, CONSTRAINT entity_pk PRIMARY KEY (id) )

Idea is that we store different type jsons in one table, jtype define 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"}'

Goal is to make quick search that user can type 'ABS' and find both records - company and person who works in company.

Analog for Oracle DB is function CONTAINS:

SELECT jtype, jvalue FROM entity WHERE CONTAINS (jvalue, 'ABS') > 0;

GIN index only allow for searching key/value pairs

GIN indexes can be used to efficiently search for keys or key/value pairs occurring within a large number of jsonb documents (datums). Two GIN "operator classes" are provided, offering different performance and flexibility trade-offs.

https://www.postgresql.org/docs/current/static/datatype-json.html#JSON-INDEXING

Answer 1

https://github.com/postgrespro/jsquery might be useful for what you are looking for although I haven't used it before.

Answer 2

As of Postgresql 10, you can create indexes on JSON/JSONB columns and then do full text searching 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"

More documentation can be found here.