Query

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
},
...]

Filter

• field: the name of the field to filter by
• op: can be one of the OData $filter operators
• value: value type must match the field. Can also refer to other queries with $-syntax, see examples below
• in: refers to other queries, see examples below
• values: a list of filter values

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

POST /api/query/pivot

Pivot data from a table or view.

Request

Body parameters

The request is the parameters used to pivot data.

{
    "eClass": string,                  // entity class or database view to query
    "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
    "keys": [string],                  // column(s) used to identify rows
    "columns": [string],               // column(s) used to identify pivot columns
    "values:" [string],                // column(s) used to identify values for pivot columns
}

Response

The response is an object with data and columns:

{
    "data": [rows],
    "pivotColumns": [{
        "accessor": string,
        "type": string,
        "data": any,     // e.g. if columns in the request is "type","unit", data will be set to the value of these for each column
    }],
}

Example 1

Pivot

{
    "eClass": "BS_ResultList",
    "select": ["FermentationName", "ExperimentName"],
    "keys": ["FermentationName"],
    "columns": ["Type", "Unit"],
    "values": ["ValueFloat"],
}

This will return:

{
    "data": [{ "fermentationName": "F001", "experimentName": "EName", "rtype-un": 1, "rtype2-un1": 2, ...}],
    "pivotColumns": [{
        "accessor": "rtype-un",
        "type": "float",
        "data": {
            "type": "rType",
            "unit": "un"
        }
    }, {
        "accessor": "rType2-un1",
        "type": "float",
        "data": {
            "type": "rType2",
            "unit": "un1"
        }
    }]
}

Database view

GET /api/query/view/{name}

Request

Path parameters

• name

Freetext search

GET /api/query/freetext/{query}

Search across…

Request

Path parameters

• query

Hierarchy

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

Request

Path parameters

• eClass
• id

Valid statuses

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

Request

Path parameters

• eClass
• id

Suggest entities

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

Request

Path parameters

• eClass
• query

Suggest field values

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

A simple JSON array of suggestions:

[{
    "value": "Suggestion",
    "name": "Name from database"
}]

The name property is only set, when the requested field is a foreign key to another table and this table contains a name field. For more, see the example below.

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:

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

Unique field value

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

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

Request

Path parameters

• eClass
• field
• query

Returns

A simple JSON object:

{
    "unique": true | false
}