Skip to main content
I
IWO1-Visitor
1-Visitor
December 14, 2018
Solved

How to create a query filter with FieldName in a child-InfoTable

  • December 14, 2018
  • 1 reply
  • 6733 views

Hello

 

I have a think-Property based on a InfoTable. That means i have some rows with a ID, Timestamp and a "value" .

Example:

Result.png

And here the value:

value.png
Now i want to create a Service to filter some rows from this Table ("QueryInfoTablePropertyHistory").

 

How do i have to create a Query-Filter to select only Rows with positive Values inside the value-Object?

Do i have to use a special JSON-Path?

 

My current query is not working:

 

{
	"maxItems":20,
	"propertyName":"mo_Result",
	"query":{
		"filters": {
			"type": "GT",
			"fieldName": "value.rows[0].PedalPushForce",
			"value": 0
		}
	}
}

It seams that my fieldName is the problem.

 

 

Best answer by AdamR

In order to filter on the nested infotable you will need to directly reference that element of the infotable.  Since infotables are simply the Thingworx recognized JSON structure you can use standard notation for JSON to do this.

 

So in your example to access the nested element you would need.

itName.rows[0].values.rows[0].moResult

 

You can then use the InfotableFunctions resource to do a query...

var params = {
t: itName.rows[0].values,
query: yourQueryJson
};
var result = Resources["InfoTableFunctions"].Query(params);

1 reply

A
AdamR14-AlexandriteAnswer
14-Alexandrite
December 14, 2018

In order to filter on the nested infotable you will need to directly reference that element of the infotable.  Since infotables are simply the Thingworx recognized JSON structure you can use standard notation for JSON to do this.

 

So in your example to access the nested element you would need.

itName.rows[0].values.rows[0].moResult

 

You can then use the InfotableFunctions resource to do a query...

var params = {
t: itName.rows[0].values,
query: yourQueryJson
};
var result = Resources["InfoTableFunctions"].Query(params);

I
IWO1-VisitorAuthor
1-Visitor
December 17, 2018

The second "rows[0]" is o.k. for me. Cause i only have one entry.

But what about the "itName.rows[0].values". Can i use a placeholder like "%"?

The Query-JSON should work for each row in my result.

 

Example-Data:

{
	"rows": [
		{
			"id": "2671982",
			"value": {
				"rows": [
					{
						"mo_Result": false,
						"mo_Result_Number": 0.002132468368546446,
						"mo_Result_Integer": 0,
						"BrakeForce": 0.0,
						"PedalPushForce": 0.0
					}
				]
			},
			"timestamp": 1544541927078
		},
		{
			"id": "2671984",
			"value": {
				"rows": [
					{
						"mo_Result": false,
						"mo_Result_Number": 0.002132468368546446,
						"mo_Result_Integer": 0,
						"BrakeForce": 5.0,
						"PedalPushForce": 0.0
					}
				]
			},
			"timestamp": 1544541932082
		},
		{
			"id": "2671986",
			"value": {
				"rows": [
					{
						"mo_Result": false,
						"mo_Result_Number": 0.002132468368546446,
						"mo_Result_Integer": 0,
						"BrakeForce": 5.0,
						"PedalPushForce": 0.0
					}
				]
			},
			"timestamp": 1544541937081
		}
	]
}

Now i want to select the last 2 Rows with "BrakeForce > 0". The name of my InfoTable are "currentTable".

 

My code (do not work):

var query = {
 "filters": {
 "type": "GT",
 "fieldName": "currentTable.rows[0].value.rows[0].BrakeForce",
 "value": "0"
 }
};
var params2 = {
	t: currentTable.rows[0].value, /* INFOTABLE */
	query: query /* QUERY */
};
var newTable = Resources["InfoTableFunctions"].Query(params2);
A
AdamR14-Alexandrite
14-Alexandrite
December 31, 2018

Thank you for the additional information.  

 

Currently that level of query would not work in the service structure as you have noted.  The only way to accomplish this with current structure would be to loop through the top level rows and do a comparison on each nested infotable.

 

That is not the most efficient answer but it is the way you can make it work for the short term.  I will also add a request to our development team to see if the structure you note can be supported in the future.

Terms & ConditionsAccessibility statement