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

Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X

Complex Queries - Nested filters

nadams1
3-Visitor

Complex Queries - Nested filters

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

                        }]

                    }

                }]

            }

        }]

    }

}

2 REPLIES 2
jmay1
5-Regular Member
(To:nadams1)

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

jamesm1
5-Regular Member
(To:nadams1)

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

                     }

                  ]

               }

            ]

         }

      ]

   }

}

Top Tags