Custom Queries

The Custom Queries feature provides the ability to retrieve and modify data stored in AnythingDB using ArangoDB Query Language (AQL). AnythingDB is built on ArangoDB.

To access Custom Queries, click AnythingDB > Custom Queries.

ArangoDB stores graphs and documents as JSON objects that can be organized in collections and databases. As defined by ArangoDB:

Databases

Databases allow isolating sets of collections from one to another. Graph databases organize and store data using a graph-like structure, where data entities are represented as nodes and their relationships as edges (also called links).

Collections

Collections store documents, similar to how files are stored in folders. You can use them to group records of similar kinds together. Collection types include:

Document collection: The documents in these collections are represented as nodes in the schema shown above.
Edge collection: Edge collections are used to store connections between documents. The documents in these collections are represented as edges/links in the schema shown above.

Documents

Documents are referred to the JSON objects store.

Following this structure, in Altair IoT Studio, each Space will have its own database in AnythingDB and the data will be separated from other Spaces.

Each database will have the following Collections (please note Collections cannot be removed and new ones cannot be created).

Type document:

Categories: store documents about the Categories defined
Models: store documents about the Models defined
Versions: store documents about the Versions of the different Models defined
Things: store documents about the Things defined
propertiesHistory: store documents with the records of Properties defined
actionsHistory: store documents with the records of Actions defined
eventsHistory: store documents with the records of Events defined

Type edge:

Links: store connections between Things. Note that connections between other elements are not allowed.

1..1 → The query navigates level 1

1..2 → The query navigates both levels 1 and 2

2..2 → The query navigates just level 2

Each Collection will contain multiple documents. Each document could be defined with a set of attributes as shown in the schema below:

It is now possible to run custom queries directly on the database. New endpoints have been added to execute AQL queries that will run in the ArangoDB database where the AnythingDB's Space information is stored.

The new endpoints that we have created are compatible with the ArangoDB API, so it means handling ArangoDB cursors. Here is the documentation about ArangoDB query and how to use it.

Running a Query

Queries can be run directly from the interface in Custom Queries. Enter the code with the appropriate format in the code block and click Run.

By default, the code corresponding to one of the examples offered by the platform is included. Find below a description for the different elements of the request:

query: The Anything DB query itself with the data to be collected from the database.
count: True/False. Define a count of items matching the query that should be returned in the result or not.
batchSize: Defines the number of elements to display in the response.
bindVars: Variables referenced in the query.

The response returned by the platform will have the following parameters:

code: Response status code
count: Number of elements that matched the query
error: True / false, depending on existing errors
extra: (stats): Stats about the execution of the query
id: See the pagination section below for more information
hasMore: True / false depending on if all elements were returned in result
result: Elements returned by the query.

Example Queries

To help you understand this tool, three query examples are included that can be chosen from the drop-down menu located at the top of the page.

To use these examples, replace the values in the bindVars parameter of the body.

Using AnythingDB Queries from a User Function

A powerful way to explore the use of AnythingDB from within the Altair IoT Studio platform is by automating these queries through the use of Functions. To be able to incorporate calls in the functions, use the https protocol using a POST call.

You must have the necessary credentials to make the call following the basic operation of our API.

https://api.swx.altairone.com/spaces/develop/query/cursor/

The body of the POST call must include in JSON format the fields defined above, where the query to be made will be included.

The following code snippet includes an example of a Python function to perform a query using the cursor endpoint.

from swx.auth.token import get_token
import requests
import json


API_HOST = 'https://api.swx.altairone.com'


CLIENT_ID = "xxx::xxxxxxxxxxxxxxxxxxxx"
CLIENT_SECRET = "xxxxxxxxxxxxxxxxxxxxxxx"


def handle(req):
  with get_token(CLIENT_ID, CLIENT_SECRET, ["query"]) as token: 
    data ={"query": "FOR c IN categories FILTER c.name ==@category_name FOR t IN things FILTER c._id IN t.categories RETURN t.status.name",
"batchSize":5,
"bindVars":{
"category_name":"CategoryName"
}
}

PATH="/spaces/spacename/query/cursor/"
headers = {"Authorization": "Bearer " + token.access_token}}
response = requests.request("POST", API_HOST + PATH , headers=headers,json=data)


return {
"body": response.json()['result'],
"status_code": response.status_code
}

The above example lists the things within the category with the name "CategoryName". For this example, the Client_Id and the Client_Secret correspond to an App previously defined in the Access Control section where the query scope has been established to be used.

Pagination

Occasionally, the result returned after a query will not show all the results. This will be indicated in the response field with the title hasMore. To access the rest of the results, use the field "id" that is returned in the query result.

Make a call with the URL indicated in the previous section, but incorporate the ID that was returned in the response.

https://api.swx.altairone.com/spaces/develop/query/cursor/id

You can make calls to this URL sequentially until you get the rest of the values. The hasMore field will return false when the last data has been displayed.

For more information about the API, refer to the documentation.

Query Cursors Examples

This API is used to execute queries directly on the ArangoDB database.

Query Cursor Normal Request

{
  "query": "FOR t IN things FILTER HAS(t.properties, @property_name) RETURN t._key",
  "count": true,
  "batchSize": 2,
  "bindVars": {
    "property_name": "cpu"
  }
}

In this request we find out multiple attributes:

query: Contains the query string to be executed. Only read-only queries are allowed.
count: This is a boolean attribute. If we set it to true, the response will return a "count" field, with the total number of elements in the database that match the query sent.
batchSize: Indicates the maximum number of results we want to be returned.
bindVars: Key/values pair representing the bind parameters in the query. In this example @property_name.

There are more parameters that can be sent in the request, which you can find in the API.

With the request sent above a possible answer that you can obtain is the following:

{
  "code": 201,
  "count": 3,
  "error": false,
  "extra": {
    "stats": {
      "executionTime": 0.006785640027374029,
      "peakMemoryUsage": 32768
    }
  },
  "hasMore": true,
  "id": "9895653453478",
  "result": [
    "01H416JXT187JZ8R58NYW6GN97",
    "01H416KYGQ0X22HKAY0KCXEM9J"
  ]
}

Here, multiple attributes are returned:

code: Indicates the HTTP status code of the response.
count: Total number of elements in the database that match the query sent.
error: A boolean attribute that indicates if there has been any error executing the request.
extra: An optional JSON object with extra information about the query result contained in its stats sub-attribute.
hasMore: Boolean attribute that indicates if there are more results available for the cursor on the server.
id: Temporary cursor id that is returned if there is more available data. It can be used on the /spaces/{space}/query/cursor/{cursor-id} endpoint.
result: Array of result data expected from the query sent.

Query Cursor with Operations to Apply

Operations can be applied to the results returned but only on Things.

{
  "query": "FOR t IN things FILTER HAS(t.properties, @property_name) RETURN t._key",
  "count": true,
  "batchSize": 2,
  "bindVars": {
    "property_name": "cpu"
  },
  "then": {
    "op": "update",
    "resource": "properties",
    "data": {
      "cpu": 10
    }
  }
}

There is a new attribute in the request called then, with the following parameters:

op: Indicates the type of operation that we want to execute. In this case, we want to update a Thing.
resource: Indicate on which resource you want to apply the indicated "op". In this case, we are updating the Properties of the Things.
data: Data to be sent. In this case, we indicate the new cpu value.

The response will be as follows:

{
  "code": 201,
  "count": 3,
  "error": false,
  "extra": {
    "stats": {
      "executionTime": 0.0069512189365923405,
      "peakMemoryUsage": 32768
    }
  },
  "hasMore": true,
  "id": "9895653458933",
  "result": [
    "01GZG7BNXKECSVFENWDVZT0FA0",
    "01H416JXT187JZ8R58NYW6GN97"
  ],
  "then": {
    "error_count": 0,
    "success_count": 2
  }
}

There is a new attribute in the response called then, and it has 2 parameters:

error_count: Number of failed operations. In this case, number of things that have not been updated.
success_count: Number of succeeded operations. In this case, the number of Things updated correctly.

Query Cursors to Return Linked Things

Since ArangoDB is a graph database, this functionality has been included for the Links of the Things, so every time a Link of a Thing points to another Thing, a connection between them will be created.

The following example shows which Things are linked to each other:

{
	"query": "FOR l in links FILTER l._from == @from RETURN l._to",
	"count": true,
	"bindVars": {
		"from": "things/01H4G9T37EYV2BQ0GXRV7V2MMG"
	}
}

The response will be as follows, with the result parameters containing all the Things IDs that matched the previous request:

{
    "result": [
        "things/01H4G4CWKPBZE6GY2SECA88RYJ",
        "things/01H4G4CYRHTNT3XSJ2XZHY9Z4R"
    ],
    "hasMore": false,
    "count": 2,
    "extra": {
        "stats": {
            "executionTime": 0.00025816199922701344,
            "peakMemoryUsage": 32768
        }
    },
    "error": false,
    "code": 201
}