Customizing Operator Advisor > Using JSON Filters in Query Services
Using JSON Filters in Query Services
Operator Advisor supports using JSON filters in query services. The following sections explain how to use these JSON filters in any of the Operator Advisor services that use the optional filter input parameter.
Supported Keys and Operators
The following keys and operators are supported for use in JSON filters:
select
filters, with the following type values (logical operators):
EQ—Equal to
NE—Not equal to
LT—Less than
LE—Less than or equal to
GT—Greater than
GE—Greater than or equal to
AND—And
OR—Or
IN—In
sorts
isAscending—Ascending sort if true, descending sort if false
isCaseSensitive—Case-sensitive sort if true
joins, with the following type values (join types):
INNER
LEFT
RIGHT
FULL
dataShapeName—Name of the Data Shape on which the column (fieldName) resides
fieldName—Name of the column in the Data Shape
value or values—Value or values of interest in the database
* 
In the following example filters, the Name fields on both the PTC.SCA.SCO.JobOrderProcessingResourceRequirement and PTC.SCA.SCO.JobOrder are custom properties that have been added to those Data Shapes. To use the filters as shown, those fields must be added to the Data Shapes.
When specifying custom properties in a JSON filter, you can specify the primary Data Shape for the object, as shown in these examples. It is not necessary to specify the related _AP Data Shape used to add the custom property.
Select
In a select key, you can select specific fields from specific Data Shapes that you want to be returned by the query. Construct a JSON array in which each object specifies the dataShapeName and fieldName for a field to be returned by the query. The dataShapeName can be the name of the primary Data Shape (the Data Shape against which the service is being executed) or the name of another Data Shape that is included in the query by a join.
For example, the following select key can be used in a JSON filter for the GetJobOrders service to return the name of a job order processing resource requirement, the name and ID of a job order, and the name of a processing resource:
"select": [
{
"dataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"fieldName": "Name"
},
{
"dataShapeName": "PTC.SCA.SCO.JobOrder",
"fieldName": "Name"
},
{
"dataShapeName": "PTC.SCA.SCO.JobOrder",
"fieldName": "ID"
},
{
"dataShapeName": "PTC.SCA.SCO.ProcessingResource",
"fieldName": "Name"
}
]
In the preceding example, the Name fields on both the PTC.SCA.SCO.JobOrderProcessingResourceRequirement and PTC.SCA.SCO.JobOrder are additional properties that were added to the Data Shapes.
Filters
The filters key allows you to filter the query results for records matching specific values. You can string together multiple filters by constructing JSON arrays called filters, separating each filter by a comma, and joining them by the AND or the OR operator. Each filter specifies a dataShapeName, a fieldName, a type, and a value or values (for the IN operator). Both single and multiple filters must then be grouped together under another filters key.
The dataShapeName can be the name of the primary Data Shape (the Data Shape against which the service is being executed) or the name of another Data Shape that is included in the query by a join. If no dataShapeName is specified for the filter, then the Data Shape against which the service is being executed is assumed.
For example, the following filters key can be used in a JSON filter for the GetHistoricalData service to return all results in the historical data database table where a job order was created or updated:
"filters": {
"filters": [
{
"dataShapeName": "PTC.SCA.SCO.HistoricalData",
"fieldName": "DataShapeName",
"type": "EQ",
"value": "PTC.SCA.SCO.JobOrder"
},
{
"dataShapeName": "PTC.SCA.SCO.HistoricalData",
"fieldName": "ReferenceKey",
"type": "IN",
"values": [1,2]
}
],
"type": "AND"
}
Sorts
The sorts key allows you to sort the query results based on a specific Data Shape field (dataShapeName and fieldName), sort in ascending or descending order (isAscending), and determine whether the filter is case-sensitive (isCaseSensitive). You can sort on multiple fields by including multiple sort objects in the array, separated by commas.
The dataShapeName can be the name of the primary Data Shape (the Data Shape against which the service is being executed) or the name of another Data Shape that is included in the query by a join. If no dataShapeName is specified, then the Data Shape against which the service is being executed is assumed.
For example, the following sorts key can be used in a JSON filter with the GetJobOrders service to sort the results by the name of the job order processing resource requirement in ascending order, then by the job order name in descending order (assuming that the job order processing resource requirement is included in the query by a join).
"sorts":[
{
"dataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"fieldName": "Name",
"isAscending": true,
"isCaseSensitive": false
},
{
"dataShapeName": "PTC.SCA.SCO.JobOrder",
"fieldName": "Name",
"isAscending": false,
"isCaseSensitive": false
}
]
Joins
A join is used to combine rows from two or more Data Shapes, based on a related field between the Data Shapes. To use fields on the Data Shapes in select, sort, or filter keys, include those Data Shapes (other than the primary Data Shape) in the query by using a join.
Each join is made up of the join type (type), the source or left Data Shape and field name (sourceDataShapeName and sourceFieldName), and the target or right Data Shape and field name (targetDataShapeName and targetFieldName). The following types of joins are supported: INNER, LEFT, RIGHT, and FULL.
INNER—An inner join returns all records that have matching values in both Data Shapes.
LEFT—A left join returns all records from the left (source) Data Shape and the matching records from the right (target) Data Shape. If there is no match, the result from the right (target) side of the join is NULL.
RIGHT—A right join returns all records from the right (target) Data Shape and the matching records from the left (source) Data Shape. If there is no match, the result from the left (source) side of the join is NULL.
FULL—A full join returns all records where there is a match in either the left (source) or right (target) Data Shape.
For example, the following joins key can be used in a JSON filter for the GetJobOrders service to return all records where the UID on the job order matches the JobOrderUID field on the job order processing resource requirement, and where the ProcessingResourceUID field on the job order processing resource requirement matches the UID field on the processing resource.
"joins": [
{
"type": "INNER",
"sourceDataShapeName": "PTC.SCA.SCO.JobOrder",
"sourceFieldName": "UID",
"targetDataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"targetFieldName": "JobOrderUID"
},
{
"type": "INNER",
"sourceDataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"sourceFieldName": "ProcessingResourceUID",
"targetDataShapeName": "PTC.SCA.SCO.ProcessingResource",
"targetFieldName": "UID"
}
]
Example JSON Filters
The following example shows a filter that can be used with the GetHistoricalData service. This filter returns all results in the historical data database where a job order was created, and then sorts the results in ascending order by the time when each job order was created.
{
"filters": {
"filters": [
{
"dataShapeName": "PTC.SCA.SCO.HistoricalData",
"fieldName": "DataShapeName",
"type": "EQ",
"value": "PTC.SCA.SCO.JobOrder"
},
{
"dataShapeName": "PTC.SCA.SCO.HistoricalData",
"fieldName": "ReferenceKey",
"type": "EQ",
"value": 1
}
],
"type": "AND"
},
"sorts": [
{
"fieldName": "TimeStamp",
"isAscending": true,
"isCaseSensitive": false
}
]
}
The following example shows a filter that can be used with the GetJobOrders service. With this filter:
For each result, the Name and ID of the job order, the Name of the related processing resource, and the Name of related the job order processing resource requirement is returned.
The results are filtered for only those processing resources with an ID value of WC1.
The results returned are for those records where the UID on the job order matches the JobOrderUID field on the job order processing resource requirement, and where the ProcessingResourceUID field on the job order processing resource requirement matches the UID field on the processing resource.
The results are sorted by the Name of the job order processing resource requirement in ascending order, then by the Name of the job order in descending order.
{
"select": [
{
"dataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"fieldName": "Name"
},
{
"dataShapeName": "PTC.SCA.SCO.JobOrder",
"fieldName": "Name"
},
{
"dataShapeName": "PTC.SCA.SCO.JobOrder",
"fieldName": "ID"
},
{
"dataShapeName": "PTC.SCA.SCO.ProcessingResource",
"fieldName": "Name"
}
],
"filters": {
"filters": [
{
"dataShapeName": "PTC.SCA.SCO.ProcessingResource",
"fieldName": "ID",
"type": "EQ",
"value": "WC1"
}
],
"type": "AND"
},
"joins": [
{
"type": "INNER",
"sourceDataShapeName": "PTC.SCA.SCO.JobOrder",
"sourceFieldName": "UID",
"targetDataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"targetFieldName": "JobOrderUID"
},
{
"type": "INNER",
"sourceDataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"sourceFieldName": "ProcessingResourceUID",
"targetDataShapeName": "PTC.SCA.SCO.ProcessingResource",
"targetFieldName": "UID"
}
],
"sorts":[
{
"dataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"fieldName": "Name",
"isAscending": true,
"isCaseSensitive": false
},
{
"dataShapeName": "PTC.SCA.SCO.JobOrder",
"fieldName": "Name",
"isAscending": false,
"isCaseSensitive": false
}
]
}
For additional examples of using filters in queries, see Query Parameter for Query Services in the ThingWorx Help Center. Note—the Near and NotNear filters discussed in that topic are not supported in Operator Advisor.
Was this helpful?