Query large tables

Every large table in the API, from visits to record requests, is queried the same way: POST a JSON body to its /browse route to choose columns, filter, sort, aggregate, search and page, and get back { table, aggregate, total }.

{
  "select":    { },
  "where":     { },
  "orderBy":   [ ],
  "aggregate": { },
  "search":    "...",
  "take":      100,
  "skip":      0
}

Every field is optional. An empty body returns every row you can see, with every column, in the route’s default order.

Each route has a root entity, the key its own columns sit under, plus the entities joined to it. Use these keys in every part of the request:

The record-requests root key is request, not recordRequest.

To show clinical data to your own users, embed the read-only UI instead of rendering these rows yourself: see Embed clinical data. The browse routes are for server-to-server queries and exports.

Understand the row shape

Each row is keyed by entity: the root key and its joined entities, from the table above. A visit row, for example, holds a visit object beside patient, site, payor and the rest. Every part of the request (select, where, orderBy and aggregate) uses the same keys.

Choose columns

select maps each entity to the columns you want:

{
  "select": {
    "visit":   { "ref": true, "earliestServiceDate": true },
    "patient": { "firstName": true, "lastName": true }
  }
}

Inside an entity’s block, only the columns set to true are returned. Leave an entity out to get all its columns, or leave out select to get every column. Unselected columns are missing from the response, not null.

Filter rows

where maps each entity’s columns to a list of filters. Every filter must match: across columns, across entities, and within one column’s list.

{
  "where": {
    "visit": {
      "earliestServiceDate": [
        { "gte": "2025-01-01" },
        { "lt":  "2026-01-01" }
      ]
    },
    "patient": {
      "lastName": [ { "startsWith": "Smi" } ]
    },
    "site": {
      "state":      [ { "in": ["CA", "NY", "TX"] } ],
      "systemName": [ "isNotNull" ]
    }
  }
}

A filter is one of:

An operator that doesn’t fit the column, such as contains on a number, returns 400.

Filter related records through their entity: patient.id, not visit.patientId. A column name the route doesn’t recognise, inside where or select, is ignored rather than rejected, so a misspelled filter returns 200 with unfiltered rows. An unknown entity or top-level key is rejected with 400, and the error names it.

Sort rows

orderBy is a list. The first entry is the main sort and each later one breaks ties:

{
  "orderBy": [
    { "visit":   { "earliestServiceDate": "desc" } },
    { "patient": { "lastName": "asc" } }
  ]
}

Each entry names one entity and one column, with "asc" or "desc". Hermes adds a final tiebreak on the row’s ID, so paging is stable even when your sort has ties.

Aggregate rows

aggregate computes totals over every row that matches where and search. Paging does not affect it. Every entity supports count; entities with numeric columns also support sum, avg, min and max, keyed by column. Those four take numeric columns only, never a date:

{
  "aggregate": {
    "visit": { "count": true }
  }
}

The results come back in aggregate, in the same shape.

Search

search matches a case-insensitive substring against every text column of every joined entity, and combines with where. Use it for a single search box, and where for precise queries.

Page through results

take and skip count matching rows. The response’s total is the number of matching rows before paging, so you can show “page 2 of 9” without a second call. Leave out take to get every matching row.

Read the response

{
  "table":     [ ],
  "aggregate": { },
  "total":     4217
}

Export to CSV

Send Accept: text/csv to get the selected columns as CSV instead of JSON. Leave out take to export every matching row. CSV has no aggregate or total.

Find the columns a route accepts

Each route’s request and response types are in the API reference, named after the entity: VisitBrowseRequest and VisitBrowseResponse, for example, with VisitBrowseFilter, VisitBrowseSort and VisitBrowseSelect for each part. They are the full list of the entities, columns and operators the route accepts.

Next steps

• Get clinical history for a patient: the clinical tables these routes read.
• Request records from a facility: watch many requests at once with /v0/record-requests/browse.