ilike search by resource content
With _ilike search parameter you search for terms inclusion as substring in text representation of FHIR resource. Interesting feature of this parameter, that it can provide quick feedback to user about matches without forcing to print the whole word (as with full text search). For example
jowill find Johns and Jolie or
aspwill match Aspirin.
With _ilike parameter you term separated with space combined with
ANDand separated by comma
OR. Example above is translated into SQL query like this:
SELECT * FROM patient
resource::text ilike '%joh%' AND ... ilike '%smit%'
resource::text ilike '%jes%' AND ... '%park%'
ILIKE search can be efficiently indexed with trigram PostgreSQL extension and GIN Index, providing response in tens of miliseconds responses on milions of records.
CREATE INDEX patient_trgm_idx on patient
USING gin (
(id || ' ' || resource::text) gin_trgm_ops
VACUUM ANALYZE patient;