cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Highlighted
IWO
Level 7

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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

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

4 REPLIES 4

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

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

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

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

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

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.

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

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