How to implement full text search on complex nested JSONB in Postgresql

user_2122783 0
Viewed 4771

I have pretty complex JSONB stored in one jsonb column.

DB table looks like:

 CREATE TABLE sites (
   id text NOT NULL,
   doc jsonb,
   PRIMARY KEY (id)
 )

Data we are storing in doc column is a complex nested JSONB data:

   {
      "_id": "123",
      "type": "Site",
      "identification": "Custom ID",
      "title": "SITE 1",
      "address": "UK, London, Mr Tom's street, 2",
      "buildings": [
          {
               "uuid": "12312",
               "identification": "Custom ID",
               "name": "BUILDING 1",
               "deposits": [
                   {
                      "uuid": "12312",
                      "identification": "Custom ID",             
                      "audits": [
                          {
                             "uuid": "12312",         
                              "sample_id": "SAMPLE ID"                
                          }
                       ]
                   }
               ]
          } 
       ]
    }

So structure of my JSONB looks like:

SITE 
  -> ARRAY OF BUILDINGS
     -> ARRAY OF DEPOSITS
       -> ARRAY OF AUDITS

We need to implement full text search by some values in each of type of entry:

SITE (identification, title, address)
BUILDING (identification, name)
DEPOSIT (identification)
AUDIT (sample_id)

SQL query should run a full text search in these field values only.

I guess need to use GIN indexes and something like tsvector, but do not have enough Postgresql background.

So, my question is it possible to index and then query such nested JSONB structures?

postgresql-9.6 postgresql-9.5 jsonb full-text-search postgresql
2 Answers

Things seem a little simpler in Postgres 10, as the to_tsvector function supports json. So for example this works nicely:

UPDATE dataset SET search_vector = to_tsvector('english',
'{
  "abstract":"Abstract goes here",
  "useConstraints":"None",
  "dataQuality":"Good",
  "Keyword":"historic",
  "topicCategory":"Environment",
  "responsibleOrganisation":"HES"
}'::json)
where dataset_id = 4;

Note I haven't tried this on a deeply nested structure, but don't see why it wouldn't work

user_6203620
Related