Custom Queries

The Custom Queries section enables you to perform direct queries against the database in which the data model is stored.

The database used by SmartWorks is ArangoDB, which allows you to perform queries using the capabilities of a graphical database.

Thing Diagram

When accessing the Custom Queries panel, the first thing to look at in some detail is the Thing Diagram. This diagram defines the structure that the AnythingDB database follows internally. This structure must be followed to perform the queries correctly.

Run a Custom Query

To perform a custom query, you must directly insert the query text into the "query" field inside the box below the Execute button. Below are two sample queries using the parking system example.

Check occupied parking slots.
1. To obtain a list with the identifiers of all the parking slots that are currently occupied, look for the sensors type "Parking sensor" and select the ones that have the Presence property set to True, which means it is currently occupied by a vehicle.
  The following code can be used to get the desired result:
```
{
  "query": "FOR t IN things FILTER t.title == @thing_title AND t.status.@presence == @value RETURN t._key",
  "count": true,
  "batchSize": 100,
  "bindVars": { 
  "thing_title": "Parking Sensor",
  "presence": "Presence",
  "value": true
  }
}
```
2. Click Run.
  You should get the following results:
```
{
    "code": 201,
    "count": 4,
    "error": false,
    "extra": {
        "stats": {
            "executionTime": 0.0043545961380000488,
            "peakMemoryUsage": 32768
        }
    }
    "hasMore": false,
    "result": [
    "01GYEXVS365Z2112Y96C9RMBGF",
    "01GYEXVJRNXTCEDF4323VHRR7D"
    "O1GYEXVCEB2D37A5JP38DGBCZ3"
    ]
}
```
  The results show the identifier for the Parking Sensor with the Property Presence set to true in AnythingDB.

Get all Traffic Lights location.

In this example you will launch a query to obtain the location of all the traffic lights.

Enter the following code:

{
  "query": "FOR t IN things FILTER t.title == @thing_title RETURN [t._key,t.status['Geo Location']]]",
  "count": true,
  "batchSize": 100,
  "bindVars": { 
     "thing_title": "Traffic Light"
     }
}

The response returned by the platform will include a list of lights with the coordinates of the location and the identifier of the device.

{
    "code": 201,
    "count": 10,
    "error": false,
    "extra": {
        "stats": {
            "executionTime": 0.0020188000053167343,
            "peakMemoryUsage": 32768
        }
    }
    "hasMore": false,
    "result": [
        [
            "01GYAFJYSD61ZXH0PCNK8YK44Z",
            
            {
              "Latitude": "40.43504455987032",
              "Longitude": "3.545557"
            }
        ],
        [
            "01GYAFM9Q3V3RBWBC58QSYY9C",
            {
              "Latitude": "40.82504457987032",
              "Longitude": "3.687757"
            }
        ],
        [
            '01GYEX58JBFYFVJZN87SN4VP9C',
            
            {
              "Latitude": "41.43504455987032",
              "Longitude": "2.545557"
            }
        ],
        [
            "O1GYEX6FQ20XWE3D82FTZKMDX6",
            
            {
              "Latitude": "45.03504455987032",
              "Longitude": "1.245557"
            }
        ],
    ...

Get the average "Power Consumption" of all the Traffic lights.

In this example you will launch a query to obtain the average value of the property "Power Consumption" of all the things titled "Traffic Light" that belong to the category "TrafficManagement."

{
  "query": "FOR c in categories FILTER c.name == @category_name FOR t IN things FILTER   t.title == @thing_title FILTER c._id IN t.categories FILTER HAS(t.status, @property_name) COLLECT AGGREGATE average = AVG(t.status.@property_name) RETURN average",
  "count": true,
  "bindVars": {
    "category_name": "TrafficManagement",
    "thing_title":"Traffic Light",
    "property_name": "Power Consumption"
  }
}

The response returned by the platform will include the result:

de": 201,
  "count": 1,
  "error": false,
  "extra": {
    "stats": {
      "executionTime": 0.009605439845472574,
      "peakMemoryUsage": 32768
    }
  },
  "hasMore": false,
  "result": [
    14.75
  ]
}