The Queries Folder of a Json Schema stores Queries whose results are then available to be used in that schema. This allows Json content to be generated from the results of bql or neql queries - for example including a report of overridden points, active alarms, or history logs for a given point to name just a few.
Query Interval is an important property of the queries folder. It determines how often the queries will be executed, and therefore how up to date any data exported by the JsonSchema will be when an Update strategy of COV is used.
Note: On each execution of the schema each query is run in parallel if mutliple exist.
Queries are not executed on each schema generation in change of value mode, otherwise you could find a query is run every time a point value changes - which could have a negative impact on the performance of control strategy running in a station. Instead the results are cached and then added to the schema via the Bound Query Result as explained below.
Schemas in on-demand mode and Relative Json schemas will execute each query on every single schema generation. For a more detailed schema flow diagram see How Schema Generation Works
It is possible to manually invoke query execution using the execute queries action of the JsonSchema, which could also be linked to some appropriate logic to trigger execution when needed.
As ever when executing queries against your station, please bear in mind the potential performance implications of running queries frequently, and reduce the scope of the query by focusing the first part of the ord to the location where the data is likely to be found, or by using the stop keyword to prevent depth recursion.
Queries are added below the Queries folder found at the top level of the Json Schema.
A query can be any valid transform, neql or bql statement which returns a BITable.
Some examples which might be useful to include in a Json Schema are:
slot:/Drivers/BacnetNetwork|bql:select name, out.value from control:ControlPoint where status.overridden = 'true'
history:/Newhaven/waveHeight|bql:select timestamp, value
station:|transform:slot:/VelocityServlet/lineChart/TransformGraph
alarm:|bql:select timestamp, uuid, ackState, source as 'origin'
Note: You may rename the columns using the ‘as’ keyword which will be reflected in the resultant Json.
Used in conjunction with a Relative Json Schema The Query Pattern Property allows the current base item to be prepended to a bql query, so query data can be included in the payload for given set of points or devices:
%baseHistoryOrd%?period=today|bql:select timestamp, value
This may be used in conjunction with a base query that returns a HistoryConfig
or a HistoryExt
(or the parent of these types):
station:|slot:/JsonExampleComponents|bql:select * from history:HistoryConfig
Consideration should be given to the effect on performance running many queries on an embedded controller may have.
Once a Query has been defined, the Bound Query Result is used to determine where and how to insert the results in the payload.
Query results can be mixed with all other schema member types such as bound properties, or other query results, in the same payload. For example a floor summary with historical data and current alarms could be constructed if required by the target platform.
The jsonToolkit provides various output formats as demonstrated below, new output formats can be created by a developer as explained here.
Note: the examples below use only two columns of data brevity, more can be added to most examples
Note: the timestamp returned by a query can be formatted using the time format options in the Schema config folder.
Reminder: executing a bql query does not trigger subscription of the component in question, the values used will be the last one known to the station.
"data":
[
[
"timestamp",
"value"
],
[
"2019-02-07 23:27:42.116+0000",
45
],
[
"2019-02-07 23:28:03.157+0000",
15
],
[
"2019-02-07 23:28:24.197+0000",
85
],
[
"2019-02-07 23:28:45.222+0000",
55
],
[
"2019-02-07 23:29:06.247+0000",
25
]
]
"data":
[
[
"2019-02-07 23:27:42.116+0000",
45
],
[
"2019-02-07 23:28:03.157+0000",
15
],
[
"2019-02-07 23:28:24.197+0000",
85
],
[
"2019-02-07 23:28:45.222+0000",
55
],
[
"2019-02-07 23:29:06.247+0000",
25
]
]
"data":
[
{
"timestamp": "2019-02-07 23:27:42.116+0000",
"value": 45
},
{
"timestamp": "2019-02-07 23:28:03.157+0000",
"value": 15
},
{
"timestamp": "2019-02-07 23:28:24.197+0000",
"value": 85
},
{
"timestamp": "2019-02-07 23:28:45.222+0000",
"value": 55
},
{
"timestamp": "2019-02-07 23:29:06.247+0000",
"value": 25
}
]
The first column is assumed to represent the object name.
"data":
[
"2019-02-07 23:27:42.116+0000": {
"value": 45
},
"2019-02-07 23:28:03.157+0000": {
"value": 15
},
"2019-02-07 23:28:24.197+0000": {
"value": 85
},
"2019-02-07 23:28:45.222+0000": {
"value": 55
},
"2019-02-07 23:29:06.247+0000": {
"value": 25
}
]
"data":
[
[
"timestamp",
"2019-02-07 23:27:42.116+0000",
"2019-02-07 23:28:03.157+0000",
"2019-02-07 23:28:24.197+0000",
"2019-02-07 23:28:45.222+0000",
"2019-02-07 23:29:06.247+0000"
],
[
"value",
45,
15,
85,
55,
25
]
]
"data":
[
[
"2019-02-07 23:27:42.116+0000",
"2019-02-07 23:28:03.157+0000",
"2019-02-07 23:28:24.197+0000",
"2019-02-07 23:28:45.222+0000",
"2019-02-07 23:29:06.247+0000"
],
[
45,
15,
85,
55,
25
]
]
Note: The query used to populate the Bound Query Result should only return one column, it would be wasteful to select data that is not expected to emerge in the payload.
"data":
[
45,
15,
85,
55,
25
]
The hidden query folder property queriesMaxExecutionTime
may be used to increase the amount of time granted to completing all the queries on each cycle. Failure to complete in this time will fail the schema generation.
Copyright 2019 Tridium, Inc. All Rights Reserved.