topic Re: Complex Queries - Nested filters in ThingWorx Developers

Complex Queries - Nested filters

nadams1 — Thu, 27 Oct 2016 07:38:51 GMT

I have logged properties that I want to filter I'm writing a service to build a query based on the users selections however I've run into an issue.

It does not appear to support nested filters, any suggestions on how to approach this problem?

{

"filters": {

"type": "AND",

"filters": [

{"filters": {

"type": "OR",

"filters": [{

"fieldName": "gender",

"type": "EQ",

"value": "Male"

{

"fieldName": "gender",

"type": "EQ",

"value": "Female"

}]

}

{"filters": {

"type": "AND",

"filters": [{

"fieldName": "age",

"type": "GE",

"value": 50

{

"fieldName": "age",

"type": "LT",

"value": 60

}]

}

]

}

In the JSON above the taking lines 5 - 18 or 19 - 32 as the queries work however when run it as a whole I get the following error message:

Unable to Invoke Service QueryPropertyHistory on myThing : JSONObject["type"] not found.

I want to provide three or more groups of filtering options where each group will filter based on one property. I need an 'AND' between each of the groups and in general there will be an 'OR' between the elements of the group. As I want to filter based on age groups rather than an actual value for this I will need and 'OR' and an 'AND' something like this...

{

"filters": {

"type": "AND",

"filters": [{

"filters": {

"type": "OR",

"filters": [{

"fieldName": "gender",

"type": "EQ",

"value": "Male"

{

"fieldName": "gender",

"type": "EQ",

"value": "Female"

}]

}

{

"filters": {

"type": "OR",

"filters": [{

"filters": {

"type": "AND",

"filters": [{

"fieldName": "age",

"type": "GE",

"value": 50

{

"fieldName": "age",

"type": "LT",

"value": 60

}]

}

{

"filters": {

"type": "AND",

"filters": [{

"fieldName": "age",

"type": "GE",

"value": 30

{

"fieldName": "age",

"type": "LT",

"value": 40

}]

}

}]

}

}]

}

Re: Complex Queries - Nested filters

jmay1 — Thu, 09 Nov 2017 14:56:08 GMT

Did you ever receive an answer for this? I am running into same problem.

Re: Complex Queries - Nested filters

jamesm1 — Fri, 10 Nov 2017 04:32:27 GMT

From the Thingworx documentation:

Nesting Filters with Different And/Or Types

In order to nest filters with different And/Ortypes, there must be a type and filters keyword for each level of the JSON Object. The following JSON syntax for the query will obtain the desired results (other than the empty string, which is replaced with empty in the example below).

var query3 ={  
   "filters":{  
      "type":"AND",
      "filters":[  
         {  
            "type":"OR",
            "filters":[  
               {  
                  "fieldName":"Status",
                  "type":"LIKE",
                  "value":"*-none-*"
               },
               {  
                  "fieldName":"Status",
                  "type":"LIKE",
                  "value":"empty"
               }
            ]
         },
         {  
            "fieldName":"IsDeleted",
            "type":"EQ",
            "value":false
         }
      ]
   }
};

You're creating too many Filter's objects, that only exists as an object at the top level, the rest are arrays.

I think this is what you want, but it's difficult without being able to test:

{

"filters":{

"type":"AND",

"filters":[

{

"type":"OR",

"filters":[

{

"fieldName":"gender",

"type":"EQ",

"value":"Male"

{

"fieldName":"gender",

"type":"EQ",

"value":"Female"

}

]

{

"type":"OR",

"filters":[

{

"type":"AND",

"filters":[

{

"fieldName":"age",

"type":"GE",

"value":50

{

"fieldName":"age",

"type":"LT",

"value":60

}

]

{

"type":"AND",

"filters":[

{

"fieldName":"age",

"type":"GE",

"value":30

{

"fieldName":"age",

"type":"LT",

"value":40

}

]

}

]

}

]

}