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

[IWO]

Hello

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

Example:

And here the value:

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.

[AdamR · Accepted solution]

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);

[IWO]

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);

[AdamR]

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.

[slangley]

Hi @IWO.

If the response provided by @AdamR allowed you to resolve your issue, please mark it as the Accepted Solution for the benefit of others with the same question.

Regards.

--Sharon