Using JSON Filters in Query Services
JSON filters can be used in many query services. The following sections explain how to use these JSON filters in any of the query 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
◦ as
◦ localizedFieldName
• 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
▪ NOTIN—Not in
▪ LIKE—Like
▪ NOTLIKE—Not like
▪ BETWEEN—Between
▪ NOTBETWEEN—Not between
▪ TAGGED—Tagged
▪ NOTTAGGED—Not tagged
▪ MATCHES—Matches
▪ NOTMATCHES—Not matches
▪ MISSINGVALUE—Missing value
▪ NOTMISSINGVALUE—Not missing value
◦ isCaseSensitive—Case-sensitive comparison if true; used with EQ, NE, LIKE, NOTLIKE, MATCHES, and NOTMATCHES.
• 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
• alias—Alias for the Data Shape that the query service is querying against. When an alias is specified, you must use alias in the select, filters, joins, and sorts keys.
• function:
◦ MIN
◦ MAX
◦ COUNT—Not supported for DATETIME fields.
◦ AVG
◦ SUM
• groupBy
• locale
 |
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.
For more information, see Adding Custom Properties to Data Model Entities.
|
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. If an alias is specified for a Data Shape, use the alias any time you want to refer to that specific data table within the select key. When no alias is defined for a Data Shape, the query defaults to using a generated unique alias value for that data table. Any time you do not specify the alias and your JSON references that same Data Shape, that same generated unique alias value for that data table is used.
When as is used, the output infotable adds a field for that select key using the as value as the field name.
Functions (MIN, MAX, COUNT, AVG, SUM) can be used within a select key. An example JSON filter including the COUNT function is available here.
|
|
If an aggregate function is used in the select key and there is more than one select object in the select key, then groupBy must also be used in the filter, or the query fails.
|
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"
}
]
}
"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.
|
|
The BuildSelectFromDataShapes service on the PTC.DBConnection.QueryUtils Thing can be used to build the select key for specified Data Shapes. The following example service input results in a select key for the PTC.SCA.SCO.JobOrderPRocessingResourceRequirement, PTC.SCA.SCO.JobORder, and PTC.SCA.SCO.ProcessingResource Data Shapes:
{
"dataShapes": [{ "name": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement" }, { "name": "PTC.SCA.SCO.JobOrder" }, { "name": "PTC.SCA.SCO.ProcessingResource" } ] } |
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. If an alias is specified for a Data Shape, use the alias any time you want to refer to that specific data table within the filters key. When no alias is defined for a Data Shape, the query defaults to using a generated unique alias value for that data table. Any time you do not specify the alias and your JSON references that same Data Shape, that same generated unique alias value for that data table is used.
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.DBConnection.HistoricalData",
"fieldName": "DataShapeName",
"type": "EQ",
"value": "PTC.SCA.SCO.JobOrder",
"isCaseSensitive": true
},
{
"dataShapeName": "PTC.DBConnection.HistoricalData",
"fieldName": "ReferenceKey",
"type": "IN",
"values": [1,2]
}
],
"type": "AND"
}
}
"filters": {
"filters": [
{
"dataShapeName": "PTC.DBConnection.HistoricalData",
"fieldName": "DataShapeName",
"type": "EQ",
"value": "PTC.SCA.SCO.JobOrder",
"isCaseSensitive": true
},
{
"dataShapeName": "PTC.DBConnection.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. If an alias is specified for a Data Shape, use the alias any time you want to refer to that specific data table within the sorts key. When no alias is defined for a Data Shape, the query defaults to using a generated unique alias value for that data table. Any time you do not specify the alias and your JSON references that same Data Shape, that same generated unique alias value for that data table is used.
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
}
]
}
"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).
Use a sourceAlias and targetAlias for the source and target Data Shapes of the join, respectively. The targetAlias must be unique. The sourceAlias must either reference the main alias for the query or a previous targetAlias. When no sourceAlias or targetAlias is defined for a Data Shape, the query defaults to using a generated unique alias value for that data table. Any time you do not specify the soureAlias or targetAlias and your JSON references that same Data Shape, that same generated unique alias value for that data table is used.
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.
Self joins are supported when the sourceAlias and targetAlias values for the join are different. If your self join is on the primary Data Shape, then the sourceAlias must match the alias value for the primary 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"
}
]
}
"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"
}
]
}
Group By
The groupBy key allows you to aggregate rows in the output into summary rows based on a specific Data Shape field (dataShapeName and fieldName). 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. The groupBy key is often used with functions (MIN, MAX, COUNT, AVG, SUM).
If an alias is specified for a Data Shape, use the alias any time you want to refer to that specific data table within the groupBy key. When no alias is defined for a Data Shape, the query defaults to using a generated unique alias value for that data table. Any time you do not specify the alias and your JSON references that same Data Shape, that same generated unique alias value for that data table is used.
For example, the following groupBy key can be used in a JSON filter for a query service to group the results by the name of the reason type.
{
"groupBy": [
{
"dataShapeName": "PTC.ReasonCode.ReasonType",
"fieldName": "name"
}
}
"groupBy": [
{
"dataShapeName": "PTC.ReasonCode.ReasonType",
"fieldName": "name"
}
}
Locale
The locale key allows you to return localized output in query results. For the locale value, specify which locale in which you want the output. Locale values correspond with the code field in the Language table. For more information, see [link to topic explaining the DB localization functionality, forthcoming].
When the locale key is specified, the localizedFieldName must be specified within the select key along with the fieldName and dataShapename to provide and name the column used in the query output for the localized token.
For example, the following locale and select keys can be used in a JSON filter for a query service to return reason code names in French.
{
"locale":"fr",
"select":[
{
"fieldName":"displayName_Token",
"dataShapeName":"PTC.ReasonCode.Reason",
"localizedFieldName":"reason_name_localized"
}
],
"filters":{
"filters":[
{
"dataShapeName":"PTC.ReasonCode.Reason",
"fieldName":"name",
"type":"EQ",
"value":"Unknown"
}
],
"type":"AND"
}
}
"locale":"fr",
"select":[
{
"fieldName":"displayName_Token",
"dataShapeName":"PTC.ReasonCode.Reason",
"localizedFieldName":"reason_name_localized"
}
],
"filters":{
"filters":[
{
"dataShapeName":"PTC.ReasonCode.Reason",
"fieldName":"name",
"type":"EQ",
"value":"Unknown"
}
],
"type":"AND"
}
}
Example JSON Filters
Example 1: 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.DBConnection.HistoricalData",
"fieldName": "DataShapeName",
"type": "EQ",
"value": "PTC.SCA.SCO.JobOrder"
},
{
"dataShapeName": "PTC.DBConnection.HistoricalData",
"fieldName": "ReferenceKey",
"type": "EQ",
"value": 1
}
],
"type": "AND"
},
"sorts": [
{
"fieldName": "TimeStamp",
"isAscending": true,
"isCaseSensitive": false
}
]
}
"filters": {
"filters": [
{
"dataShapeName": "PTC.DBConnection.HistoricalData",
"fieldName": "DataShapeName",
"type": "EQ",
"value": "PTC.SCA.SCO.JobOrder"
},
{
"dataShapeName": "PTC.DBConnection.HistoricalData",
"fieldName": "ReferenceKey",
"type": "EQ",
"value": 1
}
],
"type": "AND"
},
"sorts": [
{
"fieldName": "TimeStamp",
"isAscending": true,
"isCaseSensitive": false
}
]
}
Example 2: 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
}
]
}
"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
}
]
}
Example 3: The following example shows a filter that can be used when querying against the PTC.ReasonCode.ReasonCategory Data Shape as the primary Data Shape for the query. This filter includes as keys, the COUNT function, and groupBy.
{
"select": [
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"as": "count",
"function": "COUNT"
},
{
"dataShapeName": "PTC.ReasonCode.ReasonType",
"fieldName": "name",
"as": "typeName"
}
],
"joins": [
{
"type": "LEFT",
"sourceDataShapeName": "PTC.ReasonCode.ReasonCategory",
"sourceFieldName": "reasonTypeUid",
"targetDataShapeName": "PTC.ReasonCode.ReasonType",
"targetFieldName": "uid"
}
],
"groupBy": [
{
"dataShapeName": "PTC.ReasonCode.ReasonType",
"fieldName": "name"
}
]
}
"select": [
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"as": "count",
"function": "COUNT"
},
{
"dataShapeName": "PTC.ReasonCode.ReasonType",
"fieldName": "name",
"as": "typeName"
}
],
"joins": [
{
"type": "LEFT",
"sourceDataShapeName": "PTC.ReasonCode.ReasonCategory",
"sourceFieldName": "reasonTypeUid",
"targetDataShapeName": "PTC.ReasonCode.ReasonType",
"targetFieldName": "uid"
}
],
"groupBy": [
{
"dataShapeName": "PTC.ReasonCode.ReasonType",
"fieldName": "name"
}
]
}
Example 4: The following example shows a filter that can be used when querying against the PTC.ReasonCode.ReasonCategory Data Shape as the primary Data Shape for the query. This filter includes a self join against the PTC.ReasonCode.ReasonCategory Data Shape using sourceAlias and targetAlias, as keys, the COUNT function, and groupBy.
{
"alias": "ReasonCategory1",
"select": [
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"as": "name",
"alias": "ReasonCategory1"
},
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"as": "name2",
"alias": "ReasonCategory2"
},
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"as": "countName",
"function": "COUNT",
"alias": "ReasonCategory1"
}
],
"filters": {
"filters": [
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"type": "EQ",
"value": "Scrap",
"alias": "ReasonCategory1"
}
],
"type": "AND"
},
"joins": [
{
"type": "LEFT",
"sourceDataShapeName": "PTC.ReasonCode.ReasonCategory",
"sourceFieldName": "uid",
"sourceAlias": "ReasonCategory1",
"targetDataShapeName": "PTC.ReasonCode.ReasonCategory",
"targetFieldName": "uid",
"targetAlias": "ReasonCategory2"
},
{
"type": "LEFT",
"sourceDataShapeName": "PTC.ReasonCode.ReasonCategory",
"sourceFieldName": "reasonTypeUid",
"sourceAlias": "ReasonCategory1",
"targetDataShapeName": "PTC.ReasonCode.ReasonType",
"targetFieldName": "uid",
"targetAlias": "ReasonType"
}
],
"groupBy": [
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"alias": "ReasonCategory1"
},
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"alias": "ReasonCategory2"
}
]
}
"alias": "ReasonCategory1",
"select": [
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"as": "name",
"alias": "ReasonCategory1"
},
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"as": "name2",
"alias": "ReasonCategory2"
},
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"as": "countName",
"function": "COUNT",
"alias": "ReasonCategory1"
}
],
"filters": {
"filters": [
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"type": "EQ",
"value": "Scrap",
"alias": "ReasonCategory1"
}
],
"type": "AND"
},
"joins": [
{
"type": "LEFT",
"sourceDataShapeName": "PTC.ReasonCode.ReasonCategory",
"sourceFieldName": "uid",
"sourceAlias": "ReasonCategory1",
"targetDataShapeName": "PTC.ReasonCode.ReasonCategory",
"targetFieldName": "uid",
"targetAlias": "ReasonCategory2"
},
{
"type": "LEFT",
"sourceDataShapeName": "PTC.ReasonCode.ReasonCategory",
"sourceFieldName": "reasonTypeUid",
"sourceAlias": "ReasonCategory1",
"targetDataShapeName": "PTC.ReasonCode.ReasonType",
"targetFieldName": "uid",
"targetAlias": "ReasonType"
}
],
"groupBy": [
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"alias": "ReasonCategory1"
},
{
"dataShapeName": "PTC.ReasonCode.ReasonCategory",
"fieldName": "name",
"alias": "ReasonCategory2"
}
]
}
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 these JSON filters.