$lookup
Efficient lookup for resources by key attributes
Overview
There are scenarios when you want to quickly lookup patients or practitioners with the prefix search by multiple key elements like a family name, date of birth, and identifier. Prefix search means you want to say in query string
jo do 79 and find John Doe with 1979 birthdate. Sometimes there are millions of patients in your database and you want to do it efficiently to show type-ahead dropdown choices in your UI.$lookup operations is especially designed to be efficient implementation for this case.
Demo. US NPI by Aidbox​https://search-resource.netlify.com/ Here is demo of US NPI ~10M practitioners in Aidbox Cloud using $lookup operation. You can source code of this app on github.
There is no way to implement the efficient multidimensional prefix search with ranking and sorting in the relational database. $lookup based on specific assumptions to find the right trade-off: if the search returns more than
count (by default 50) results, we consider that the search failed and result can have some anomalies, for example, not complete sorting.Here is how it works.
First of all, you have to describe priority groups of attributes with by parameter. Groups are separated by
; and inside group you specify the list of paths separated by ,. Each path expression consists of dot separated elements and indexes (filters support is coming soon) and should end with primitive type (examples: name.given or identifier.value).Result will be sorted with order of priority groups. For example, if you want to rate first matches of name, identifier and birth of data, and second matches in address and telecom you will use following expression:
name.family,name.given,identifier.value,birthDate;address.state,address.city,address.line,telecom.valueLet's say you are searching
joh 1979 ny Aidbox will initially search in first priority group by expression like this: 1
expr = extract_space_separated(resource, paths)
2
where expr ilike ' % joh' AND expr ilike '% 1979'
3
limit 50
Copied!
If this query returns 50 records, aidbox will respond with this records.
1
GET /Patient/$lookup?\
2
by=name.family,name.given,birthDate,identifier.value;address.city,address.line&\
3
sort=name.family,name.given&\
4
q=Joh+Do+1980&\
5
count=50&\
6
limit=200
Copied!
Parameters
Each path expression should point to primitive element!
- by:
;-separatedlist of priority groups. Each group is,-separatedlist of path expressions. - sort:
,-separatedlist of path expressions to sort by - q: is
+or space separated term (prefixes) to search - limit: is internal search limit (default 200)
- count: number results to return (default 50)
- mode: if mode=
indexAidbox returns index DDL for specific search
Create Indexes
To create indexes you have to make request with mode=index and execute DDL returned by Aidbox:
1
GET /Patient/$lookup?\
2
by=name.family,name.given,birthDate,identifier.value;address.city,address.line&\
3
sort=name.family,name.given&\
4
q=Joh+Do+1980&\
5
count=50&\
6
limit=200
Copied!
response
1
DROP INDEX IF EXISTS lookup_patient_g_1;
2
CREATE INDEX lookup_patient_g_1 ON "patient" USING GIN
3
( regexp_replace ( /*text*/ aidbox_text_search ( /*texts*/ knife_extract_text ( /*doc*/ resource , /*expr*/ $[["name","family"],["name","given"],["birthDate"],["identifier","value"]]$ ) ) , /*regexp*/ '[ -.,";:'']+' , /*repl*/ ' ' , /*flag*/ 'g' )
4
gin_trgm_ops );
5
​
6
DROP INDEX IF EXISTS lookup_patient_g_2;
7
CREATE INDEX lookup_patient_g_2 ON "patient" USING GIN
8
( regexp_replace ( /*text*/ aidbox_text_search ( /*texts*/ knife_extract_text ( /*doc*/ resource , /*expr*/ $[["name","family"],["name","given"],["birthDate"],["identifier","value"],["address","city"],["address","line"]]$ ) ) , /*regexp*/ '[ -.,";:'']+' , /*repl*/ ' ' , /*flag*/ 'g' )
9
gin_trgm_ops );
10
VACUUM ANALYZE "patient";
Copied!
​
Last modified 1yr ago
Copy link