Query

Filters and sortings

Several query endpoints accept filters and sortings in the request body. The format is the same everywhere.

Filters

"filters": [{
    "field": string,      // the name of the field to filter by
    "op?": string,        // operator, defaults to "eq" (or "in" when values is given)
    "value?": any,        // the value to compare with
    "values?": [any],     // a list of values, used with "in" and "not_in"
    "in?": string         // reference to another query in the same dataset request
}]

All filters in the list must match (they are combined with and).
op is one of the operators listed on the Supported $filter page. The operator names are the same whether they are used in a JSON op or in an OData $filter query string.
value must match the type of the field, e.g. a string for text fields and a number for numeric fields.
values is used with in and not_in. When values is given, op defaults to in.
isnull, isnotnull, isempty and isnotempty take no value.
template resolves a placeholder value on the server before the query runs, see Template filters below.
in and $-references are only available in dataset requests, where they join queries together. See the dataset examples below.

Example - all experiments of type “Fermentation” that are not named “TEST” and have an analyst set:

"filters": [
    { "field": "type", "value": "Fermentation" },
    { "field": "name", "op": "ne", "value": "TEST" },
    { "field": "analystID", "op": "isnotnull" }
]

Template filters

The template operator takes a placeholder value that the server resolves when the query runs, so a saved query keeps working as time passes and as a different user runs it. The following values are supported.

Value	Resolves to
`!me`	`field eq <ID of the current user>`
`!previousDays-<n>`	`field ge <the timestamp n days ago>`
`!today-gt`	`field gt <today>`
`!today-ge`	`field ge <today>`
`!today-lt`	`field lt <today>`
`!today-le`	`field le <today>`

Notes:

!me is used on user fields, e.g. createdBy, analystID or assignedToID.
!previousDays-<n> and the !today-* values are used on date and date/time fields. <n> is a whole number of days, so !previousDays-90 covers the last 90 days.
For a date/time field, !today-* resolves to the current timestamp. For a date field it resolves to midnight at the start of today.
Template values are case insensitive.

Example - fermentations created by the current user within the last 90 days:

"filters": [
    { "field": "type", "value": "Fermentation" },
    { "field": "createdBy", "op": "template", "value": "!me" },
    { "field": "createdUtc", "op": "template", "value": "!previousDays-90" }
]

The same filters as an OData query string:

?$filter=Type eq 'Fermentation' and CreatedBy template '!me' and CreatedUtc template '!previousDays-90'

Sortings

"sortings": [{
    "field": string,               // the name of the field to sort by
    "direction?": "asc" | "desc"   // defaults to ascending if not given
}]

Sortings are applied in the order given, so the first entry is the primary sort key.

GET endpoints

The GET endpoints that support filtering (database view and suggest field values) take the filters as an OData $filter query parameter instead of a JSON body, e.g. ?$filter=Type eq 'Fermentation' and Name ne 'TEST'. See the OData page for $filter, $orderby, $top and $skip.

Dataset

POST /api/query/dataset

Query one or several entities or views.

Request

Body parameters

The request is a list of queries that can be inter-related:

[{
    "collection or entity": string,    // collection returns all, entity returns first match
    "eClass or view": string,          // entity class or database view to query
    "sortings": [{
        "field": string,               // the name of the field to sort by
        "direction?": "asc" | "desc"   // defaults to ascending if not given
    }],
    "filters": [{
        "field": string,               // the name of the field to filter by
        "op": string,                  // OData $filter operator, defaults to 'eq'
        "value?": any,                 // value paired with operator
        "in?": string,                 // used for joining queries together
        "values?": [any],              // if given, operator defaults to 'in'
    }],
    "select": [string]                 // only return given fields, minimizes data sent back
},
...]

filters and sortings are described under Filters and sortings. value can also refer to other queries in the same request with $-syntax, and in joins queries together, see the examples below.

Response

The response is an object with the keys defined as collection or entity:

{
    "collection1": [entities],
    "entity1": entity,
    "collection2": [entities],
    "entity2": entity
}

Example 1

Get Step and Experiment related to a Fermentation named “FERM-001”:

[{
    "entity": "ferm",
    "eClass": "Fermentation",
    "filters": [{ "field": "name", "value": "FERM-001" }]
},{
    "entity": "step",
    "eClass": "Step",
    "filters": [{ "field": "id", "value": "$ferm.originID" }]
},{
    "entity": "exp",
    "eClass": "Experiment",
    "filters": [{ "field": "id", "value": "$step.experimentID" }]
}]

This will return:

{
    "ferm": { "id": "F001", "eClass": "Fermentation", "originID": "ST001", ...},
    "step": { "id": "ST001", "eClass": "Step", "experimentID": "EX001", ...},
    "exp": { "id": "EX001", "eClass": "Experiment", ...}
}

Example 2

Get all Experiments of type “AdvancedAnalysis”, sort them by last modified, and also get the related Steps:

[{
    "collection": "experiments",
    "eClass": "Experiment",
    "filters": [{ "field": "type", "value": "AdvancedAnalysis" }],
    "sortings": [{ "field": "modifiedUtc", "direction": "desc" }]
},{
    "collection": "steps",
    "eClass": "Step",
    "filters": [{ "field": "experimentID", "in": "experiments.id" }]
}]

This will return:

{
    "experiments": [
        { "id": "EX001", "eClass": "Experiment", ...},
        { "id": "EX005", "eClass": "Experiment", ...},
        ...
    ],
    "steps": [
        { "id": "ST001", "eClass": "Step", "experimentID": "EX001", ...},
        { "id": "ST009", "eClass": "Step", "experimentID": "EX005", ...},
        ...
    ]
}

Pivot query

POST /api/query/pivot

Execute a pivot query that transforms row data into columns.

Request

Body

A JSON object with the pivot query model.

Database view

GET /api/query/view/{name}

Query a database view with OData query parameters.

Request

Path parameters

name Name of the database view

Query parameters

OData query parameters are used, as documented here.

Hierarchy

GET /api/query/hierarchy/{eClass}/{id}

Get the full hierarchy tree (parents and children) for an entity.

Request

Path parameters

eClass Entity class
id Entity ID

Parents

GET /api/query/parents/{eClass}/{id}

Get all parent entities for an entity.

Request

Path parameters

eClass Entity class
id Entity ID

Children

GET /api/query/children/{eClass}/{id}

Get all child entities for an entity.

Request

Path parameters

eClass Entity class
id Entity ID

Valid statuses

GET /api/query/status/{eClass}/{id?}

Get all valid status transitions for an entity class, optionally scoped to a specific entity’s current status.

Request

Path parameters

eClass Entity class
id Optional entity ID. If provided, only statuses valid for the entity’s current status are returned.

Suggest entities

GET /api/query/suggest/{eClass}

GET /api/query/suggest/{eClass}/{query}

POST /api/query/suggest/{eClass}

POST /api/query/suggest/{eClass}/{query}

Search for entities of a given class by name, barcode, or ID. Returns the first matching results.

Request

Path parameters

eClass Entity class
query Optional search string

Body (POST only)

{
    "filters": [{ "field": string, "op": string, "value": any }],
    "sortings": [{ "field": string, "direction": "asc" | "desc" }],
    "select": [string],
    "offset": number
}

filters and sortings are described under Filters and sortings.

Query parameters

OData $filter is supported for additional filtering.

Response

{
    "total": number,
    "data": [entities]
}

Suggest field values

GET /api/query/suggest/field/{eClass}/{field}

GET /api/query/suggest/field/{eClass}/{field}/{query}

Get all available field values for an entity class, possibly filtered by a contains query. Only existing entities the current user has access to are taken into consideration.

Request

Path parameters

eClass The entity class.
field The field to get suggestions for.
query The contains query (optional).

Query parameters

$filter Additional filtering on other fields, using the OData $filter format.

Response

{
    "total": number,
    "data": [{
        "value": "Suggestion",
        "name": "Name from database"  // only set when the field is a foreign key
    }]
}

Examples

To get all possible types for the experiment entity:

GET /api/query/suggest/field/experiment/type

To get all possible types for an experiment where the scientist is USER1 and the type contains ‘FA’:

GET /api/query/suggest/field/experiment/type/FA?$filter=Scientist eq 'USER1'

To get all possible analyst IDs for an experiment:

GET /api/query/suggest/field/experiment/analystID

This result includes the name property as well, because the AnalystID field is a foreign key to the User table:

{
    "total": 2,
    "data": [{
        "value": "BOYSEN",
        "name": "Jakob Jakobsen Boysen"
    },
    {
        "value": "TPB",
        "name": "Thomas P. Boesen"
    }]
}

Suggest field range

GET /api/query/suggest/fieldRange/{eClass}/{field}

Get the minimum and maximum values for a numeric or date field.

Request

Path parameters

eClass Entity class
field Field name

Response

{
    "start": any,  // minimum value
    "end": any     // maximum value
}

Unique field value

GET /api/query/unique/field/{eClass}/{field}/{query}

Query if a field value of an entity class is unique.

Request

Path parameters

eClass Entity class
field Field name
query The value to check for uniqueness

Response

true | false

Example 1

Check if a sample with name “S-001” already exists:

GET /api/query/unique/field/Sample/Name/S-001