Query Parameter for Query Services
 |
QueryDataTableEntries on Data Table with infotable values returns
• result data with exact match for PostgreSQ
• result data that contains the search keyword for MSSQL.
|
Several ThingWorx services accept an optional query parameter. The query parameter is a values object that is configured differently depending on the type of query being requested.
Possible types are:
Matches, NotMatches TaggedWith,
NotTaggedWith
GT, LT, GE, LE, NE, EQ, LIKE, NOTLIKE, IN, NOTIN
Between, NotBetween MissingValue,
NotMissingValue Near, NotNear
NotTaggedWith
GT, LT, GE, LE, NE, EQ, LIKE, NOTLIKE, IN, NOTIN
Between, NotBetween MissingValue,
NotMissingValue Near, NotNear
Queries can be AND/OR, and can include a Sort.
The query parameter is used in all services that start with query among them are:
All Things
• QueryThingStreamEntries
All streams
• QueryStreamData
• QueryStreamEntries
• QueryStreamEntriesWithData
All Data Tables
• QueryDataTableEntries
|
|
QueryDataTableEntries on Data Table with infotable values returns
• result data with exact match for PostgreSQ
• result data that contains the search keyword for MSSQL.
|
InfoTableFunctions resource
• Query
All Thing Templates
• QueryImplementingThings
• QueryImplementingThingsWithData
All Thing Shapes
• QueryImplementingThings
• QueryImplementingThingsWithData
Audit Subsystem
Configuration
The query parameter has two configurable options: filters and sorters. After defining the query parameter, it can be passed into a query service. Below are examples of the filter and sort options for the query parameter. It is recommended that you use the scripting tools and snippets in the script configurator as a starting point and then manually create the query parameter object, as appropriate.
Single Filter Options
• Matches or NotMatches filter
var query =
{
"filters": {
"type": "Matches|NotMatches",
"fieldName": "Source",
"expression": "(Kettle)|(Filler)"
}
};
• TAGGED or NOTTAGGED filter
var query = {
"filters": {
"fieldName": "tags",
"type": "NOTTAGGED",
"tags": [
{
"vocabulary": "Applications",
"vocabularyTerm": "Testing"
},
{
"vocabulary": "Plants",
"vocabularyTerm": "Sedona"
}
]
}
};
• Single Comparator
|
|
When using LIKE/NOTLIKE you need to add your own Wild Cards (% or * or ? for a single character wild card). For example to find ThingWorx use LIKE Th%
|
var query = {
"filters":{
"type": "GT, LT, GE, LE, NE, EQ, LIKE, NOTLIKE",
"fieldName": "<field_name>",
"value": "Th\\+*"
}
};
• In or Not In filter
|
|
While using QueryImplementingThingsOptimized service with a query parameter, if the query is a simple filter or nested filter and uses multiple IN filters, the code fails when the array values for the IN filter are empty.
|
var jsonArray = [12,14];
var query = {
"filters": {
"type": "IN, NOTIN",
"fieldName": "Duration",
"values": jsonArray
}
};
• Between or NotBetween filter
var query =
{
"filters": {
"type": "Between, NotBetween",
"fieldName": "Duration",
"from": "2",
"to": "12"
}
};
• MissingValue or NotMissingValue filter
var query =
{
"filters": {
"type": "MissingValue, NotMissingValue",
"fieldName": "OrderQuantity"
}
};
• Near or NotNear filter
var query =
{
"filters": {
"type": "Near, NotNear",
"fieldName": "fieldName",
"distance": "50",
"units": "M(iles), K(ilometers), N(autical miles)",
"location": {
"latitude": "40.12",
"longitude": "51.24",
"elevation": "300",
"units": "WGS84"
}
}
};
• Composite Filter Options
It is possible to combine multiple filters using either the And or Or filter type. The example below filters for rows that have a duration greater than 12 and are also tagged with a power outage maintenance issue in the tags field.
var query =
{
"filters": {
"type": "And",
"filters": [
{
"type": "GT",
"fieldName": "Duration",
"value": "12"
},
{
"type": "TAGGED",
"fieldName": "tags",
"tags": [
{
"vocabulary": "MaintenanceIssues",
"vocabularyTerm": "PowerOutage"
}
]
}
]
}
};
• Nesting Filters with Different And/Or Types
In order to nest filters with different And/Or types, a type and filters keyword are required for each level of the JSON Object. The following JSON syntax for the query obtains the desired results (other than the empty string, which is replaced with empty in the example below).
var query =
{
"filters": {
"type": "AND",
"filters": [
{
"type": "OR",
"filters": [
{
"fieldName": "status",
"type": "LIKE",
"value":"*-none-*"
},
{
"fieldName": "status",
"type": "LIKE",
"value": "empty"
}
]
},
{
"fieldName": "isDeleted",
"type": "EQ",
"value": false
}
]
}
}
Sorting Options
The sorters option is an object array. There are three possible parameters for each of the sort objects in the sorters array fieldName, isAscending, isCaseSensitive. Only field name is required. isAscending and isCaseSensitive default to true.
• Single Sort Option
var query =
{
"sorts": [
{
"fieldName": "Material"
}
]
}
• Complex Sort Option
var query =
{
"sorts": [
{
"fieldName": "Material"
},
{
"fieldName": "OrderDate",
"isAscending": false
}
]
}
• Sort and Filter
You can apply a sort and a filter to the same query service, as in the next example:
var query =
{
"sorts": [
{
"fieldName": "LotID",
"isAscending": false,
"isCaseSensitive": false
},
{
"fieldName": "Plant"
}
],
"filters": {
"type": "And",
"filters": [
{
"type": "GT",
"fieldName": "Duration",
"value": "12"
},
{
"type": "TAGGED",
"fieldName": "tags",
"tags": [
{
"vocabulary": "MaintenanceIssues",
"vocabularyTerm": "PowerOutage"
}
]
}
]
}
};
Possible Types
Possible types are:
EQ = equal to
NE = not equal to
GT = greater than
GE = greater than or equal to
LT = less than
LE = less than or equal to
LIKE = like
BETWEEN
NOTBETWEEN