SearchQuery

Managed SQL for Search API

With SearchQuery resource, you can define "managed" SQL for Search API with parameters, paging, sorting, and includes.

Parameter nameDescription

_count

A number of records returned per page

_page

Controls pagination

_total

The maximum number of results returned by a search result

_timeout

Defines query timeout

join

Allows you to join related resources for search

order-by

Defines the ordering of the search results

includes

Allows you to predefine included resources

reverse

Includes resources that refer resources from your query

_explain=analyze

Helps to inspect the execution plan of a search query

If you want to use arbitrary SQL (e.g. LEFT JOIN), consider AidboxQuery.

Prepare example data

We need some sample data to see the results of example queries. Let's create it. Copy the following snippet to the Aidbox REST Console.

POST /

type: transaction
entry:
- resource:
    id: pr-1
    name:
    - given: [Ted]
      family: 'Scott'
  request:
    method: POST
    url: "/Practitioner"

- resource:
    id: pr-2
    name:
    - given: [Tommy]
      family: 'Peterson'
  request:
    method: POST
    url: "/Practitioner"

- resource:
    id: org1
    name: 'Test hospital1'
  request:
    method: POST
    url: "/Organization"

- resource:
    id: org2
    name: 'Test hospital2'
  request:
    method: POST
    url: "/Organization"

- resource:
    id: patient1
    name:
    - given: [Max]
      family: Johnson
    gender: male
    managingOrganization: {resourceType: Organization, id: org1, display: 'Test hospital1'}
    birthDate: '1960-10-10'
  request:
    method: POST
    url: "/Patient"

- resource:
    id: patient2
    name:
    - given: [Alex]
      family: Smith
    gender: male
    managingOrganization: {resourceType: Organization, id: org2, display: 'Test hospital2'}
    birthDate: '1990-01-01'
  request:
    method: POST
    url: "/Patient"

- resource:
    id: enc1
    status: planned
    subject:
      resourceType: Patient
      id: patient1
    class:
      code: abc
  request:
    method: POST
    url: "/Encounter"

- resource:
    id: enc2
    status: finished
    subject:
      resourceType: Patient
      id: patient1
    class:
      code: abc
  request:
    method: POST
    url: "/Encounter"

- resource:
    id: enc3
    status: planned
    subject:
      resourceType: Patient
      id: patient2
    class:
      code: abc
  request:
    method: POST
    url: "/Encounter"

- resource:
    id: apt1
    description: "Test appointment 1"
    start: '2020-12-10T09:00:00Z'
    end: '2020-12-10T11:00:00Z'
    status: booked
    participant: [{ actor: { resourceType: Patient, id: patient1}, status: accepted},{ actor: { resourceType: Practitioner, id: pr-1}, status: accepted}]
  request:
    method: POST
    url: "/Appointment"

- resource:
    id: apt2
    description: "Test appointment 2"
    start: '2021-04-10T09:00:00Z'
    end: '2021-04-10T11:00:00Z'
    status: booked
    participant: [{ actor: { resourceType: Patient, id: patient2}, status: accepted}, { actor: { resourceType: Practitioner, id: pr-2}, status: accepted}]
  request:
    method: POST
    url: "/Appointment"

We created 2 patients, 2 practitioners, 3 encounters, 2 appointments, 2 Managing organizations that are linked to each other.

Define search query with filtering

Let's define the search query to search old patients by the partial match of the family name with the filtering by gender:

PUT /SearchQuery/q-1

# attach this query to Patient resource type
resource: {id: 'Patient', resourceType: 'Entity'}
# give alias to patient table
as: pt
# enable total query
total: true 
# basic query
query:
  where: "(pt.resource->>'birthDate')::date < '1980-01-01'"
  order-by: pt.id desc
params:
   gender:
     type: string
     where: "pt.resource->>'gender' = {{params.gender}}"
   family:
     type: string
     format: '% ?%'
     where: |
       aidbox_text_search(knife_extract_text(pt.resource, $$[["name","family"]]$$)) 
       ilike {{params.family}}

Now we can call this query with /alpha/<resourceType>?query=<query-name>&params....:

GET /alpha/Patient?query=q-1&_page=1&_count=3&_total=none

# 200
resourceType: Bundle
type: searchset
entry: [...]
query-sql: |
  SELECT *
  FROM "patient" pt
  WHERE /* query */ (pt.resource->>'birthDate')::date < '1980-01-01'
  ORDER BY pt.id desc
  LIMIT 100
query-timeout: 60000

You can use count and page parameters for paging and control total query (if enabled) with total parameter. Use _timeout parameter to set query timeout.

If the parameter is provided, another query will be generated on the fly:

GET /alpha/Patient?query=q-1&family=joh

# 200

resourceType: Bundle
type: searchset
entry: [...]
query-sql:
- | 
  SELECT *
  FROM \"patient\" pt
  WHERE /* query */ (pt.resource->>'birthDate')::date < '1980-01-01'
    AND /* family */ aidbox_text_search(knife_extract_text(pt.resource, $$[[\"name\",\"family\"]]$$)) 
    ilike ?\nORDER BY pt.id desc
    LIMIT 100"
- '% joh%'

Define search query with JOIN

Your parameters and basic query can use join attribute to join related resources for search:

PUT /SearchQuery/q-2

resource: {id: 'Encounter', resourceType: 'Entity'}
as: enc
query:
  order-by: pt.id desc
params:
   pt:
     type: string
     format: '% ?%'
     join:
       pt: 
         table: patient
         by: "enc.resource#>>'{subject,id}' = pt.id"
     where: |
        aidbox_text_search(knife_extract_text(pt.resource, $$[["name","family"]]$$)) 
        ilike {{params.pt}}
GET /alpha/Encounter?query=q-2&pt=joh

# 200
resourceType: Bundle
type: searchset
entry: [...]
query-sql:
-  |
  SELECT *
  FROM \"encounter\" enc
  JOIN \"patient\" pt
    ON enc.resource#>>'{subject,id}' = pt.id
  WHERE /* pt */ aidbox_text_search(knife_extract_text(pt.resource, $$[[\"name\",\"family\"]]$$)) 
   ilike ?
  ORDER BY pt.id desc\nLIMIT 100"
- '% joh%'

Add order-by into parameters

Both query and params support order-by. order-by in query has the least precedence. order-by in params are added in top-down order. e.g. order-by in first search parameter has the most precedence.

Example: create search query

PUT /SearchQuery/sq

as: ap
query:
  order-by: "ap.resource->>'start' ASC"
resource:
  id: 'Appointment'
  resourceType: 'Entity'
params:
  ord-dir:
    type: string
    format: '?'
    order-by: |
      CASE WHEN {{params.ord-dir}} = 'asc' THEN ap.resource->>'start' END ASC,
      CASE WHEN {{params.ord-dir}} = 'desc' THEN ap.resource->>'start' END DESC

Example: use this search query

GET /alpha/Appointment?query=sq&ord-dir=desc

#200

resourceType: Bundle
type: searchset
entry:
  - resource:
      start: '2021-04-02T16:02:50.996+03:00'
      # omitted
  - resource:
      start: '2021-02-02T16:02:50.997+03:00'
      # omitted
  - resource:
      start: '2020-02-02T16:02:50.997+03:00'
      # omitted
# omitted

You can predefine included resources for SearchQuery with includes property:

PUT /SearchQuery/inc

resourceType: SearchQuery
resource: {id: Encounter, resourceType: Entity}
as: enc
total: true
includes:
  # name for include
  subject:
    # path to reference
    path: [subject]
    # ref to resource
    resource: {id: Patient, resourceType: Entity}
    # nested includes
    includes:
      organization:
        path: [managingOrganization]
        resource: {id: Organization, resourceType: Entity}
query: {order-by: enc.id}
limit: 40

Use the created query:

GET /alpha/Encounter?query=inc

#200

resourceType: Bundle
type: searchset
entry:
  - resource:
      status: planned
      subject:
        id: patient1
        resourceType: Patient
      id: enc1
      resourceType: Encounter
      meta:
        lastUpdated: '2021-04-19T12:18:14.183626Z'
        createdAt: '2021-04-19T12:18:14.183626Z'
        versionId: '244'
  - resource:
      status: finished
      subject:
        id: patient1
        resourceType: Patient
      id: enc2
      resourceType: Encounter
      meta:
        lastUpdated: '2021-04-19T12:18:14.183626Z'
        createdAt: '2021-04-19T12:18:14.183626Z'
        versionId: '244'
  - resource:
      status: planned
      subject:
        id: patient2
        resourceType: Patient
      id: enc3
      resourceType: Encounter
      meta:
        lastUpdated: '2021-04-19T12:18:14.183626Z'
        createdAt: '2021-04-19T12:18:14.183626Z'
        versionId: '244'
  - resource:
      name:
        - given:
            - Max
          family: Johnson
      gender: male
      birthDate: '1960-10-10'
      managingOrganization:
        id: org1