Complex Queries - Nested filters

[nadams1]

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
                        }]
                    }
                }]
            }
        }]
    }
}

[jmay1]

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

[jamesm1]

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
                     }
                  ]
               }
            ]
         }
      ]
   }
}