The search API uses the following filtering options to facilitate the search and allow to perform a wide range of consultations.

Several techniques solve the filtering issue when you’re querying over a RESTful interface. For example, you can use standard HTTP parameters to add filtering capabilities to your query. It’s pretty simple but doesn’t cover complex needs. We require a SQL-like approach, with typical operators like AND, OR, EQUAL, NOT EQUAL, etc. OpenGate allows you to filter your queries by sending a POST request to a specific URI. In the POST request, you must send a JSON document with a fashionable DSL structure. It is a command pattern approach in contrast with the entity/collection pattern used in the provisioning API.

Filtering operators

Filtering comparison operator list

• eq: Equals.
• neq: Not equals.
• like: Regex pattern like.
• gt: Greater than.
• lt: Lower than.
• gte: Greater than or equals.
• lte: Lower than or equals.
• in[]: Included in a concrete group.
• nin[]: Not included in a concrete group.
• exists: Exists.
• within: Included in an areas.geometry GeoJson (exclusive for Area search).

Filtering logical operators list

• and[]: And
• or[]: Or

How to use filters

Let’s suppose we want to filter devices with device.operationalStatus equals to NORMAL and with device.communicationModules[].mobile.imei starting with 351873000102290.

If we were dealing with a SQL database we’d write the following SQL sentence:

SELECT * FROM device
WHERE device.operationalStatus LIKE 'NORMAL'
AND device.communicationModules[].mobile.imei LIKE '351873000102290'

Translating the previous SQL sentence to OpenGate searching API we’ll have:

{
  "filter": {
    "and": [
      {
        "like": {
          "provision.device.administrativeState": "NORMAL"
        }
      },
      {
        // The result will contain all devices with collected operational Status that
        // contains NORMAL and are related with communications modules with collected
        // imei containing 351873000102290
        "like": {
          "provision.device.communicationModules[].mobile.imei": "351873000102290"
        }
      }
    ]
  }
}

The result will contain all devices with collected operational Status that contains NORMAL and are related to communications modules with collected imei containing 351873000102290.

Another example comparing SQL to JSON, searching all devices except the one with serialNumber equal to 82A75D494B0EBF7A95587285AE78E83F:

SELECT * FROM device WHERE serialNumber <> '82A75D494B0EBF7A95587285AE78E83F'
/north/v80/search/devices

{
  "filter": {
    "neq": { "device.serialNumber": "82A75D494B0EBF7A95587285AE78E83F" }
  },
  "limit": {
    "start": 2
  }
}

More filtering examples

Click on any of the following links to expand the contents.

{
  "filter": {
    "and": [
      { "in": { "device.name": ["device_1", "device_2"] } },
      { "nin": { "device.description": ["device default description"] } }
    ]
  },
  "limit": {
    "start": 26,
    "size": 50
  }
}

{
  "filter": {
    "eq": { "tag": "my_sticky_tag" }
  },
  "limit": {
    "start": 26,
    "size": 50
  }
}

{
  "filter": {
    "and": [
      { "like": { "device.model": "EF5" } },
      { "exists": { "device.identifier": true } }
    ]
  },
  "limit": {
    "start": 1,
    "size": 10
  }
}

{
  "filter": {
    "like": { "device.software": "1.0" }
  },
  "limit": {
    "start": 1,
    "size": 10
  }
}

{
  "filter": {
    "in": { "operationEntityType": ["SUBSCRIPTION", "ASSET"] }
  },
  "limit": {
    "start": 1,
    "size": 10
  }
}

{
  "filter": {
    "in": { "device.name": ["DEVICE22"] }
  },
  "limit": {
    "start": 1,
    "size": 10
  }
}

{
  "filter": {
    "like": {
      "provision.device.location._current.value.postal": "41015"
    }
  }
}

By default, the search response includes all the data streams of the searched entities. You can retrieve only the information you need using the select sub-document in the search JSON.

The select sub-document can be used only on entity searching and must not be empty.

You can also use this sub-document when you search for information in CSV format.

If the select clause isn’t in the filter, the behavior is the following:

• In JSON format, the response will contain all the data streams collected or provisioned in the devices you are searching.
• In CSV format, the search API raises an error in the response, asking for the select clause.

As described above, any data stream of the default data models or data models defined by the user can be used as select fields.

The order to apply the filters is securitization and next the following fields whenever there are resourceType, sort, filter, select (the data streams to show)

Select JSON object

• select[]: Array of parameters to be selected.

  • name: String. Data stream name in the default or user-defined data models.

  • fields[]: Array of strings with the name of the fields to be retrieved.

    The possible values are: (See current object attributes table for field description):

    • value
    • date
    • at
    • from
    • tags
    • feedId
    • scoring.performance
    • scoring.qrating
    • provType
    • value.simplexAttribute: where simplexAttribute is an attribute of the complex object. For example, the provision.device.location is a complex data stream. If you need only de postal code, the value would be value.postal

  • alias: String. Shortname replaces the parameter’s full name when a CSV format is required. Example:

    • Using “alias”=“imei”
    • The device.communicationModules[].mobile.imei becomes imei in the CSV header

    The complete data stream name in the CSV header will appear if this field doesn’t exist. The CSV format shows this field, but the JSON format ignores it.

Select examples

Here’s how to search devices with a filter with a select clause

The following snippet shows the request using curl:

curl --request POST \
     --data-binary @subscription.json \
     --header "X-ApiKey: YOUR_API_KEY_HERE" \
     --verbose \
     https://[your_opengate_address]/north/v80/search/devices \
     -H "Content-type: application/json"

You include the filter with the select clause in the request’s body.

Filter using predefined device fields

{
  "select": [
    {
      "name": "provision.device.identifier",
      "fields": [
        {
          "field": "value",
          "alias": "id"
        },
        {
          "field": "date",
          "alias": "date"
        }
      ]
    }
  ]
}

Filter obtaining a field of a complex value

{
  "select": [
    {
      "name": "provision.device.location",
      "fields": [
        {
          "field": "value.postal",
          "alias": "Postal code"
        }
      ]
    }
  ]
}

Depending on the parameter header, the response can be in two different formats, see HTTP Header Options:

• JSON Format (Default)
• CSV Format

Response to the filter in JSON format example

{
  "devices": [
    {
      "id": "bca8cbbb-b151-442b-8b0d-96ed77789c45",
      "device": {
        "operationalStatus": {
          "current": {
            "value": "NORMAL",
            "date": "2017-02-02T09:05:58Z",
            "performance": 75
          }
        },
        "communicationsModules": [
          {
            "identifier": "351873000102290",
            "mobile": {
              "imei": {
                "current": {
                  "value": "351873000102290",
                  "date": "2017-02-02T09:05:58Z"
                }
              }
            }
          }
        ]
      },
      "health": {
        "heart": {
          "rate": {
            "current": {
              "value": 60,
              "date": "2017-02-02T09:05:58Z",
              "performance": 78
            }
          }
        }
      }
    }
  ]
}

Response to the Filter in CSV format example

device.operationalStatus.current.value;device.operationalStatus.current.updated;device.operationalStatus.current.performance;imei.current.value;imei.current.updated;imei.current.performance;rate.current.value;rate.current.updated;rate.current.performance;
"NORMAL";"2017-02-02T09:05:58Z";75;"351873000102290";"2017-02-02T09:05:58Z";;60;"2017-02-02T09:05:58Z";78

The searching API allows ordering of the results. You can sort your search results, including an order object to the JSON.

Quick sort example

{
  "filter": {},
  "sort": {
    // Array of ordering parameters
    "parameters": [
      {
        // Name of parameter defined in the Default Datamodels
        "name": "provision.administration.identifier",
        // Enumeration string with ordering type. Valid values are:
        // - ASCENDING
        // - DESCENDING
        "type": "ASCENDING"
      }
    ]
  }
}

Thanks to the group keyword in the search JSON, it is possible to group by some parameters of the default data models. This keyword is optional.

Quick grouping example:

{
  "filter": {},
  // Group sub-document
  "group": {
    // List of data streams to group by
    "parameters": [
      {
        "name": "provision.device.model"
      }
    ]
  }
}

Group examples

Click on any of the following links to expand the contents.

{
  "filter": {
    "and": [
      {
        "like": {
          "provision.administration.organization": "battery_organization"
        }
      }
    ]
  },
  "group": {
    "parameters": [
      {
        "name": "provision.device.model"
      }
    ]
  }
}

{
  "summary": {
    "count": 6,
    "summaryGroup": [
      {
        "provision.administration.organization": {
          "count": 6,
          "list": [
            {
              "count": 6,
              "name": "battery_organization"
            }
          ]
        }
      },
      {
        "provision.administration.channel": {
          "count": 6,
          "list": [
            {
              "count": 6,
              "name": "battery_channel"
            }
          ]
        }
      },
      {
        "provision.device.model.name": {
          "count": 6,
          "list": [
            {
              "count": 1,
              "name": "model_name-7"
            },
            {
              "count": 2,
              "name": "model_name-4"
            },
            {
              "count": 1,
              "name": "model_name-10"
            },
            {
              "count": 1,
              "name": "model_name-5"
            },
            {
              "count": 1,
              "name": "model_name-11"
            }
          ]
        }
      }
    ]
  }
}

The API allows you obtaining the response to a search in blocks with predefined number of results.

• limit:
  • start: Page number you request. The count starts with the number 1
  • size: The number of entities that you can see on the page

Paginated examples

Paginated example response

{
    "page" : { "number" : 1 },
    "resources" : [
        { ... },
        { ... },
        { ... },
        { ... },
        ...
        { ... }
    ]
}

Warning about the previous example

The number attribute is the current number of pages based on the limit setup.

What can you do to get the following page? It’s easy. You only have to include a limit object in your query. See next example.

Pagination example request

{
  "limit": {
    "start": 2
  }
}

Paginated example generic response

{
   "page" : { "number" : 2 },
   "resources" : [
       { ... },
       { ... },
       { ... },
       { ... },
       ...
       { ... }
   ]
}

See previous warning about the resources word in the example.

You can change the page limit from the beginning. Supposing you want to retrieve 50 items per query, you must set up the limit object with a starting point and the page size you want.

Paginated example request

Changing the starting page and the limit

{
  "limit": {
    "start": 2,
    "size": 50
  }
}

The top margin for the page size in the limit object is 1000. You’ll receive a server error response if you set up a size attribute over this limit.

Another example of pagination response

With the starting page and the limit changed

{
    "page" : { "number" : 2},
    "resources" : [
         { ... },
         { ... },
         { ... },
         { ... },
         ...
         { ... }
    ]
}

See previous warning about the resources word in the example.

Responses to all search requests include a summary object with different counters regarding the results obtained. It is closely related to the grouping feature.

By default, the summary always shows the total count, the organization’s grouping counter, and the channel grouping counter.

• count (field): number of occurrences found in the whole search
• summaryGroup []: array of type of summarized specific object structure
  • SpecificObjectParameterDatamodel: object inside the Parameter of the data models
    • count: number of these specific elements found
    • list: array of each type of summarized element
      • count: number of these specific elements found
      • name: value of the parameter of the data model

Here’s how to search devices with a summary without a group clause

This is the request using curl:

curl --request POST \
     --data-binary @subscription.json \
     --header "X-ApiKey: YOUR_API_KEY_HERE" \
     --verbose \
     https://[your_opengate_address]/north/v80/search/devices/summary \
     -H "Content-type: application/json"

Some grouping examples

Response summary without grouping

{
  "summary": {
    "count": 6,
    "summaryGroup": [
      {
        "provision.administration.organization": {
          "count": 6,
          "list": [
            {
              "count": 6,
              "name": "organization1"
            }
          ]
        }
      },
      {
        "provision.administration.channel": {
          "count": 6,
          "list": [
            {
              "count": 6,
              "name": "channel1"
            }
          ]
        }
      }
    ]
  }
}

This is the request using curl:

curl --request POST \
     --data-binary @subscription.json \
     --header "X-ApiKey: YOUR_API_KEY_HERE" \
     --verbose \
     https://[your_opengate_address]/north/v80/search/devices/summary \
     -H "Content-type: application/json"

In the body of the request, you include the filter with the select clause.

Example Filter using group clause

{
  "filter": {
    "and": [
      {
        "like": {
          "provision.administration.organization": "battery_organization"
        }
      }
    ]
  },
  "group": {
    "parameters": [
      {
        "name": "provision.device.model"
      }
    ]
  }
}

Response summary with group clause

{
  "summary": {
    "count": 6,
    "summaryGroup": [
      {
        "provision.administration.organization": {
          "count": 6,
          "list": [
            {
              "count": 6,
              "name": "battery_organization"
            }
          ]
        }
      },
      {
        "provision.administration.channel": {
          "count": 6,
          "list": [
            {
              "count": 6,
              "name": "battery_channel"
            }
          ]
        }
      },
      {
        "provision.device.model.name": {
          "count": 6,
          "list": [
            {
              "count": 1,
              "name": "model_name-7"
            },
            {
              "count": 2,
              "name": "model_name-4"
            },
            {
              "count": 1,
              "name": "model_name-10"
            },
            {
              "count": 1,
              "name": "model_name-5"
            },
            {
              "count": 1,
              "name": "model_name-11"
            }
          ]
        }
      }
    ]
  }
}

Defining a data set means choosing which data streams become columns. This is administration work, done once per data set.

The identifier column

Every data set needs an identifierColumn. It maps to provision.administration.identifier._current.value, with filtering enabled and sorting available, and it identifies the device each row belongs to.

Column paths

A column’s path has three parts, and the third is only required when the data stream is not a primitive value.

1. The data stream identifier — the datastreamId you want. If it contains communicationModules[], include the index of the module you mean:

device.communicationModules[0].subscription.mobile.imsi._current.value

2. The data stream field — one of:

_current.value · _current.date · _current.at · _current.feedId · _current.source · _current.sourceInfo

3. The value path — when the data stream holds an object or an array, a path down to a primitive value.

What a column can be filtered by

Every column carries a filter value that decides how queries may use it:

RANGE is only allowed on numeric columns, integer and number. Columns of type date-time are always range-searchable whatever the value says.

The sorts section

Sorting is declared in the definition, not composed at query time. The sorts section holds a list of named sorts, each an ordered list of columns with a direction, and a query asks for one by its identifier.

"sorts": [
  {
    "identifier": "sortByDeviceAsc",
    "description": "Sort by device identifier ascending",
    "columns": [
      { "name": "Prov Identifier", "direction": "ASC" }
    ]
  }
]

At least one sort is mandatory, and the order inside columns is the sort precedence.

Limits

There is no fixed maximum number of filterable columns or declared sorts. Each filterable column and each declared sort consumes optimization units, and the data set has a budget of them — that budget is the limit.

POST /north/v80/datasets/provision/organizations/{organizationName}/optimizationPlan

Creating

POST /north/v80/datasets/provision/organizations/{organizationName}

Updating

PUT /north/v80/datasets/provision/organizations/{organizationName}/{identifier}

Updating can affect the data already stored or the structure holding it, which starts an adaptation process. Until it completes, dirty values may be present.

These fields can be modified:

Name · Description · IdentifierColumn · Columns · Sorts

Rules for columns:

• Names are unique. You cannot add or rename a column to a name already in use.
• filter: ALWAYS is immutable. You cannot add or remove a column that has it, you cannot set it on an existing column, and you cannot change it away once set.
• Paths cannot be edited. Remove the column and create it again, which gets you the same result.

The optimization unit budget applies to updates as well, so run optimizationPlan before adding filterable columns or sorts to a definition that is already close to it.

Listing and deleting

GET    /north/v80/datasets/provision/organizations/{organizationName}
GET    /north/v80/datasets/provision/organizations/{organizationName}/{identifier}
DELETE /north/v80/datasets/provision/organizations/{organizationName}/{identifier}

Read the organization’s data sets:

curl --request GET \
     --header "X-ApiKey: <your-api-key>" \
     https://api.opengate.es/north/v80/datasets/provision/organizations/{organizationName}

Reading a data set is a POST with the data set identifier in the URL:

POST /north/v80/datasets/provision/organizations/{organizationName}/{identifier}/data

Copy this and change the identifiers:

curl --request POST \
     --header "X-ApiKey: <your-api-key>" \
     --header "Content-Type: application/json" \
     --data '{"filter": {"eq": {"Prov Identifier": "MyDevice1"}}}' \
     https://api.opengate.es/north/v80/datasets/provision/organizations/{organizationName}/{identifier}/data

The response names its columns and gives you rows in that order:

{
  "page": { "number": 26 },
  "columns": ["Coll Mobile ICC value", "Coll Mobile ICC date", "Prov Identifier", "Coll manufacturer", "Coll model", "Prov Mobile ICC value"],
  "data": [
    ["icc1", "2021-04-06T12:35:22.784Z", "MyDevice1", "OpenGate", "OpenGate", "icc1"],
    ["icc2", "2021-04-06T07:45:57.468Z", "MyDevice2", "OpenGate", "OpenGate", "icc2"]
  ]
}

Without a select clause, columns holds the identifier column first, then the defined columns in declaration order.

The request body

Data set queries use the same clauses as any other search, with two differences worth memorising:

The full comparison against the other query dialects is in Query dialects.

Asking for a sort

You do not compose an ordering in the request. You name one that already exists:

{ "filter": {}, "sort": "sortByDeviceAsc" }

Valid values are the identifier of any sort in the data set definition, plus the automatically exposed reverse of each one, so declaring an ascending sort gives you the descending direction too.

Omit sort and results come back sorted by the identifier column, ascending.

There is no fixed limit on how many sorts a definition can hold; the constraint is the optimization unit budget, described in Defining a data set.

Pagination and CSV

Data sets answer in JSON or CSV, and the format changes what an absent limit means:

CSV retrieval turns sorting off on purpose: it is what makes large exports fast, and CSV output is usually consumed by something that will sort it anyway.

CSV formatting is customizable through HTTP header options — the quoting character (double quotes by default), the escape character (a backslash by default), the end-of-line sequence (\n by default) and how nulls are represented. You are responsible for the combination producing well-formed CSV. The exact header names are not currently published, so ask your platform contact for them.

The other data set endpoints

Three more endpoints exist, and one of them is not what its URL suggests:

POST /north/v80/search/catalog/datasets
POST /north/v80/search/organizations/{organizationName}/datasets/{datasetId}
POST /north/v80/search/organizations/{organizationName}/datasets/{datasetId}/summary

search/catalog/datasets lists the data sets available to you.

The other two are not a mirror of the .../data read above: they take a different request body. The .../data endpoint uses the data set’s own dialect — sort as a declared identifier, select as an array of column names, no group. These two take the generic Data Lake search body, with sort as the {"parameters": [{"name": ..., "type": ...}]} object, select in its object form, and group available.

Use .../data unless you specifically need the generic clauses. Which of the two is intended to be the long-term path has not been confirmed by the product team.

Defining a time series is declaring, up front, what the engine should compute as data arrives. This is administration work: done once, changed rarely, and with consequences for data already stored — the last section of this page covers those.

Columns

A time series has four kinds of column, and only the first two are yours to name freely.

Aggregated columns (columns)

These hold a value aggregated over each time bucket. Each one names an aggregation function, and the engine re-applies it every time new data lands in an existing row. The available functions come from the time series functions catalog.

When timeBucket is 0, all received data is stored instead of aggregated, and only FIRST (keep the first value) or LAST (overwrite with the newest) make sense. Two consequences worth knowing:

1. The search endpoint returns all the historical data collected.
2. Several data streams can land in different columns of the same row when they share an at value.

When timeBucket is greater than 0, bucketColumn becomes required: it names the column the engine adds to search responses holding the end date of each bucket.

Context columns (context)

Context columns capture the value at the moment the bucket was created, and are never updated afterwards even if the aggregated columns keep changing. That is why they take no aggregation function. Use them for the things you want to know about the device at that point in time — its serial number, its firmware version, its subscription — so that a row is self-describing.

Identifier column (identifierColumn)

Required. It names the column that identifies the device, and always maps to provision.administration.identifier._current.value with filter=YES. The engine adds it to every row of every search result, using the name you chose.

Bucket columns (bucketColumn, bucketInitColumn)

Named by you, filled by the engine, holding the end and start instants of the bucket.

The path field

Every column and context needs a path, which the engine uses as a query to extract a data stream value and project it into the column. A path has two or three parts:

1. The data stream identifier — a data stream defined in an OpenGate data model.

2. The data stream field — appended with a dot, one of:

_current.value · _current.date · _current.at · _current.feedId · _current.source · _current.sourceInfo

3. The value path — only when the data stream holds a JSON object or array, a JSONPath down to a primitive value.

What a column can be filtered by

Every column and context carries a filter value that decides how queries may use it:

RANGE is only allowed on numeric columns, integer and number. Columns of type date-time are always range-searchable whatever the value says, so you do not need RANGE for a bucket or a timestamp.

Retention

retention sets how long rows stay in the time series, in seconds. It cannot exceed the retention allowed by your organization’s policies.

The sorts section

Sorting is declared in the definition, not composed at query time. The sorts section holds a list of named sorts, each one an ordered list of columns with a direction, and a query then asks for a sort by its identifier.

"sorts": [
  {
    "identifier": "signalStrengthAsc",
    "description": "Sort by average signal strength ascending",
    "columns": [
      { "name": "Average Signal strength", "direction": "ASC" }
    ]
  },
  {
    "identifier": "bucket_id_desc",
    "columns": [
      { "name": "bucket_id", "direction": "DESC" }
    ]
  }
]

At least one sort is mandatory. Order matters inside columns: the list is the sort precedence.

Filtering and sorting limits

There is no fixed maximum number of filterable columns or declared sorts. Instead, each filterable column, each context and each declared sort consumes optimization units, and each time series has a budget of them. That budget is the real limit, and it is what keeps queries fast.

Simulate with optimizationPlan while you are still designing. It is much cheaper than discovering the budget is spent after the fact — and since the reverse of each sort is free, declaring both directions wastes units for nothing.

Creating and updating

Creating a time series starts collecting data from devices into it. Updating one is where care is needed: changes can affect the data already stored or its structure, which triggers an adaptation process. Until that process finishes, dirty values can be present.

These fields can be modified:

Name · Description · IdentifierColumn · BucketColumn · BucketInitColumn · Retention · TimeBucket · Context · Columns · Sorts

Rules for columns and contexts:

• Names are unique across all columns, contexts, the identifier and both bucket columns. You cannot rename something to a name already in use.
• filter: ALWAYS is immutable. You cannot add or remove a column or context that has it, you cannot set it on an existing one, and you cannot change it away once it is set.
• Paths cannot be edited. Remove the column and create it again, which gets you the same result.

Creation example

A daily time series (timeBucket: 86400) retained for 30 days, with one context column and one aggregated column summing the bytes a device sent:

curl --request POST \
     --header "X-ApiKey: <your-api-key>" \
     --header "Content-Type: application/json" \
     --data @timeserie.json \
     https://api.opengate.es/north/v80/timeseries/provision/organizations/{organizationName}

Content of timeserie.json:

{
  "name": "basic_timeserie",
  "organizationId": "organizationName",
  "description": "time series description",
  "timeBucket": 86400,
  "retention": 2592000,
  "origin": "2021-01-01 00:00:00+00:00",
  "bucketColumn": "bucket_id",
  "identifierColumn": "Admin Identifier",
  "context": [
    {
      "path": "provision.device.serialNumber._current.value",
      "name": "Prov serial",
      "filter": "YES"
    }
  ],
  "columns": [
    {
      "path": "device.communicationModules[].subscription.traffic.sentBytes._current.value",
      "name": "Daily sent bytes",
      "filter": "NO",
      "aggregationFunction": "SUM"
    }
  ],
  "sorts": [
    {
      "identifier": "bucket_id_desc",
      "description": "Most recent bucket first",
      "columns": [
        { "name": "bucket_id", "direction": "DESC" }
      ]
    }
  ]
}

The reverse of that sort, oldest bucket first, is available without declaring it.

Changing the time bucket

Buckets have a fixed length, so changing timeBucket changes the length of new ones. As a precaution, buckets in the future are deleted when you do this. The situations below are the edge cases worth understanding before you change it on a live time series.

Buckets that started before the change and end after it

The engine closes them at the instant of the update, and if needed the next bucket starts at that same instant and runs until the following one would start according to the new definition.

Changing the time bucket to a lower value

Changing the time bucket to a bigger value

Devices with no buckets yet

Adaptation only applies to devices that collected data before the change. A device whose first data arrives after the update simply gets a bucket following the new definition.

Changing the time bucket before the first data collection of a device

Both at once

Combine the two and different devices end up with buckets that do not line up with each other. A device can also collect data belonging to a bucket in the past: if that bucket exists the engine uses it as is, otherwise it creates a new one following the new definition. Both are the price of changing the bucket length.

Two devices with different buckets after changing the time bucket

Two devices with different buckets in the past after changing the time bucket

From zero to a higher value

Going from timeBucket: 0 to a real length means each collection now creates a bucket of the new length. Zero-length buckets that the new bucket would overlap are absorbed rather than left behind, and their values feed the aggregation functions of each column.

Changing the time bucket from zero to a higher value

Deleting

DELETE /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}

Removes the time series identified in the URL. Confirm the identifier before sending the request.

Reading a time series is a POST with a JSON body, like every other OpenGate query:

POST /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/data

Copy this and change the identifiers:

curl --request POST \
     --header "X-ApiKey: <your-api-key>" \
     --header "Content-Type: application/json" \
     --data '{"filter": {"eq": {"Prov Identifier": "MyDevice1"}}, "sort": "EntityAscBucketDesc"}' \
     https://api.opengate.es/north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/data

The response is a columns array naming the fields and a data array of rows in that order:

{
  "page": { "number": 26 },
  "columns": ["Bucket id", "Prov identifier", "Manufacturer", "ICC", "Daily sent bytes", "Daily received bytes", "Last presence", "Average Signal strength"],
  "data": [
    ["2021-04-06T12:00:00.000Z", "MyDevice1", "OpenGate", "icc1", 23500, 532, "IP", 75],
    ["2021-04-06T12:01:00.000Z", "MyDevice1", "OpenGate", "icc1", 3500, 14532, "IP", 65]
  ]
}

The request body

Two things differ from a plain Data Lake search, and both come from the time series being pre-computed: you can only filter on columns declared filterable, and you can only sort by sorts declared in the definition. See Defining a time series for how those are declared, and Query dialects for the full comparison.

Column order when you omit select

columns tells you the order, so read values off it rather than hardcoding positions. If you do depend on the order, it is:

1. The bucketColumn, holding the end date of the bucket
2. The identifierColumn, holding provision.administration.identifier._current.value
3. The context columns
4. The aggregated columns

Asking for a sort

You do not compose an ordering in the request. You name one that already exists:

{ "filter": {}, "sort": "bucket_id_desc" }

Valid values are the identifier of any sort in the time series definition, plus the automatically exposed reverse of each one. So a definition declaring bucket_id_desc gives you both directions without declaring the second.

Read the definition to see what is available — GET the time series, or use expand=sorts, and the sorts list comes back with the derived ones included. There is no fixed limit on how many sorts a definition can hold; the constraint is the optimization unit budget, described in Defining a time series.

Pagination and CSV

The response format changes what limit means, which catches people out:

CSV retrieval also turns sorting off, deliberately, so that large exports stay fast. If you need ordered output, sort downstream or read JSON.

The CSV formatting itself — quoting character, escape character, end-of-line sequence and how nulls are represented — is set through HTTP header options, and you are responsible for the result being well-formed. The exact header names are not currently published, so ask your platform contact for them.

Aggregated read: one row per device

Besides reading buckets, you can collapse every bucket of a device into a single row:

POST /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/dataset

Here select.columns describes the output variables, each with the source column, an alias for the output name, and the aggregation function to apply across buckets:

{
  "filter": {
    "gt": { "bucket_id": "device_200" }
  },
  "limit": { "start": 1, "size": 50 },
  "select": {
    "columns": [
      { "column": "temperature", "alias": "first", "aggregation": "FIRST" },
      { "column": "temperature", "alias": "last",  "aggregation": "LAST" },
      { "column": "temperature", "alias": "avg",   "aggregation": "AVG" },
      { "column": "temperature", "alias": "max",   "aggregation": "MAX" },
      { "column": "temperature", "alias": "min",   "aggregation": "MIN" },
      { "column": "cpu",         "alias": "p_avg", "aggregation": "AVG" },
      { "column": "cpu",         "alias": "p_count", "aggregation": "COUNT" }
    ]
  }
}

filter and limit behave as above, and CSV output is available too. Two rules are specific to this endpoint:

• The output is always sorted ascending by identifierColumn.
• The identifierColumn is always included, added as the first column if you did not ask for it.

Parquet export

For bulk analytical work, a time series can be exported to a Parquet file:

POST /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/export
GET  /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/export

POST starts the export, GET reports the state of the current one. The output order is decided internally and cannot be changed.

Introduction

The objective of custom time series functions is to augment the capabilities of the default functions (see Common Platform Functions) through the addition of bespoke functions written in JavaScript. These functions will be managed through the creation of a catalogue tailored to the specific requirements of each organisation.

Common Platform Functions

A common catalogue will be established for all organisations, comprising default platform time series functions. The following functions have been defined:

• FIRST: Please note that the engine will store only the first received value per time bucket. Consequently, the collection engine will ignore the following values obtained in the same time bucket.
• LAST: Please note that the engine will store only the last received value per time bucket, overwriting the previous ones.
• AVG: The engine will calculate the arithmetic mean of all values received within the specified time interval. This feature is only available for numeric values.
• MAX: The engine will save the maximum value of all received values within the configured time frame. Please note that this feature is only available for numeric values.
• MIN: The engine will save the lowest value of all received values within the configured time frame. This feature is only available for numeric values.
• SUM: The engine will accumulate the total of all received values within the specified time interval. This feature is only available for numeric values.
• COUNT: The engine will record the total number of values received in each time bucket for subsequent analysis.
• MEDIAN: The engine will calculate the median of all received values within the configured time bucket. Please note that this feature is only available for numeric values.
• GEO_AVG: The engine will calculate the geometric average of all received values within the configured time bucket. Please note that this feature is only available for numeric values.
• VARIANCE: The engine will calculate the variance of all received values within the configured time bucket. Please note that this feature is only available for numeric values.
• STD_DEVIATION: The engine will calculate the standard deviation of all received values within the configured time bucket. Please note that this feature is only available for numeric values.

Custom Catalog

Each organisation will have access to a custom time series functions catalogue, which will enable them to manage their functions effectively. These functions can be used to define time series columns, which can then be referenced in the aggregationFunction field. The following example illustrates this process:

{
  "name": "basic_timeserie",
  "organizationId": "organizationName",
  "description": "time series description",
  "timeBucket": 86400,
  "retention": 2592000,
  "origin": "2021-01-01T00:00:00.000Z",
  "bucketColumn": "bucket_id",
  "identifierColumn": "Admin Identifier",
  "context": [
    ...
  ],
  "columns": [
      {
          "path":"some.path.A.value._current.value",
          "name":"columnaA",
          ...
          ...
          "aggregationFunction":"AVG", //platform aggregation function
      },
      
      
      {
          "path":"some.path.B.value._current.value",
          "name":"columnaB",
          ...
          ...
          "aggregationFunction":"myCustomTimeserieFunction" //custom function
      }
  ]
}

Custom Time series Function script

Considerations when developing Custom Aggregation Function:

The following are the code’s implicit input parameters:

• receivedValues: an array of new values to be used for the final value calculation.

• currentValue: the column’s current value.

• extra: JSON object containing the current extra variables for the column, used for the value calculation. The code should utilise implicit input values to calculate the final value and subsequently construct the result object. It will be possible to use helper functions defined in the JS API. The code must return a JSON with three specific properties:

• executionResult: If the execution was completed successfully, the OK value must be returned. If not, an error description must be provided.

• value: The calculated value

• extra: A JSON with the updated extra variables

  ```json
  {
    "executionResult": "OK",
    "value": 5, 
    "extra":{
      "sum": 10,
      "count": 2
    }
  }
  ```
  

Functions values types

To confirm that a time series function can be assigned to a time series column, the valueTypes field will be used. This field is an array of strings that accepts the following values:

• integer
• number
• string
• boolean
• date-time

These types are the same as those specified for time series column type fields. When assigning a time series function to a specific column, the system will verify that the time series function in the array matches the type specified for that column.

Comprehensive API actions

Existing Timeseries Functions List

Getting custom timeserie functions full catalog

In both instances, only the metadata will be retrieved (no script) from both the organisations’ custom timeseries functions and the platform timeseries functions.

Usage examples

Get the full time series functions catalog (organization custom functions plus platform functions, metadata only):

curl --request GET \
     --header "X-ApiKey: <your-api-key>" \
     https://api.opengate.es/north/v80/timeseries/provision/organizations/{organizationName}/catalog

Trimmed JSON response:

[
  {
    "id": "01234567890abcdeffffffff",
    "name": "customAvg",
    "description": "Custom implementation for avg function.",
    "valueType": ["integer", "number"],
    "catalog": "ORGANIZATION"
  },
  {
    "id": "AVG",
    "name": "AVG",
    "description": "The engine will calculate the arithmetic average of all received values in the configured time bucket. Only available in numeric values.",
    "valueType": ["integer", "number"],
    "catalog": "PLATFORM"
  }
]

Create a new custom function (multipart request with a metadata JSON part and a script plain text part):

curl --request POST \
     --header "X-ApiKey: <your-api-key>" \
     --form 'metadata={"name": "customAvg", "description": "Custom implementation for avg function."};type=application/json' \
     --form 'script=@custom_avg.js;type=text/plain' \
     https://api.opengate.es/north/v80/timeseries/provision/organizations/{organizationName}/catalog

API specification