Registry records manager: Able to search record by keywords & records list

[t83714]

Registry Records Manager: Able to search record by keywords & records list

To make the registry record manager more useful for people, we will need offer the basic keyword based searching functionality.

It should come with a record list and quick preview feature as well.

Technical notes:

• Needs to find a way of doing full text searching in JSONB data with reasonable performance
• Proper index should be created based on test result of at least the following approaches:
  • pg_trgm extension with Gin index
  • drawback: search JSONB as text i.e. key would be covered
  • leveraging the new full text search feature after postgres 11
  • mainly jsonb_to_tsvector: can generate lexemes for values only (excluding keys)
  • if we use full text search feature, we should make text search config is configurable (default to english)

[t83714]

Based on tests, full text search feature seems provide reasonable result (at the same level as GIN with pg_trgm extension) but offer:

• more features: lexemes based true full text search & support extra query syntax (via websearch_to_tsquery)
• more accurate result: keys are excluded
• same level performance (compared with GIN with pg_trgm extension plus LIKE search)

Index creation:

CREATE INDEX idx_data_full_text ON recordaspects 
USING GIN ((
 to_tsvector('english', recordid) ||
 to_tsvector('english', aspectid) ||
 jsonb_to_tsvector('english', data, '["string"]')
))

Query would be similar to:

select * from recordaspects where (
 to_tsvector('english', recordid) ||
 to_tsvector('english', aspectid) ||
 jsonb_to_tsvector('english', data, '["string"]')
) @@ websearch_to_tsquery('english', 'my search keywords')

by default, we will use 'english' search config for indexing & searching but allow users to change via helm chart config. helm chart config changes won't impact existing index created. Users are required to re-created the index with SQL above (with config name changed)

[t83714]

To make sure recordId & aspectId can be "exactly" matched via the full text search query API interface, we need to alter the index creation as:

CREATE INDEX idx_data_full_text ON recordaspects 
USING GIN (jsonb_to_tsvector('english'::regconfig, data, '["string"]'))

And Search would be similar to :

select * from recordaspects where (
 jsonb_to_tsvector('english'::regconfig, data, '["string"]') @@ websearch_to_tsquery('english'::regconfig, 'my search keywords')
OR recordid = 'my search keywords'
OR aspectid = 'my search keywords'
)

[t83714]

closed via https://github.com/magda-io/magda/pull/3485