Filter Objects

Filter objects are used as parameters in method calls to the History worker. They are used to select a subset of the histories that exist on the database. Filter objects are standard JSON objects that conform to a set of conventions.

There are further worked examples in the Interrogating History article.

Volumes of Data

It's possible to write filters that will query very large volumes of data. You should always structure your filters to be as targeted as possible. We recommend using the created date of histories to select records in twenty-four hour or weekly periods - any longer than that then you could see performance issues. If you do need to report across large spans of time, you should use the Data Reporting Service.

Boolean Operators

AND

The AND operator must appear as the only key in a structure. Its value must be an array of predicates. For the AND predicate to evaluate to true all of the predicates in the array must evaluate to true.

{ "AND" : [ {...}, {...}, {...}, ...] }

OR

The OR operator must appear as the only key in a structure. Its value must be an array of predicates. For the OR predicate to evaluate to true one of the predicates in the array must evaluate to true.

{ "OR" : [ {...}, {...}, {...}, ...] }

NOT

The NOT operator must appear as the only key in a structure. Its value must be another predicate structure. The result of the NOT predicate is the opposite (boolean complement) of the value predicate.

{ "NOT" : {...} }

Comparison Operators

EQ

The EQ operator must appear as one of two keys in a structure, the other key is called "key". The value of "key" is the name of the field that will be compared. The value of "EQ" is the expected value, it can be string/int/real/bool/null. The EQ predicate evaluates to true if the specified field is found to contain the expected value.

{ "key" : "labela", "EQ" : "Disruption" }

NEQ

The NEQ operator must appear as one of two keys in a structure, the other key is called "key". The value of "key" is the name of the field that will be compared. The value of "NEQ" is the expected value, it can be string/int/real/bool/null. The NEQ predicate evaluates to true if the specified field is found to differ from the expected value.

Note that NEQ matches everything that EQ doesn't. There are some examples in Interrogating History which demonstrate the behaviour.

{ "key" : "reply.delay.days", "NEQ" : -1 }

LT

The LT operator must appear as one of two keys in a structure, the other key is called "key". The value of "key" is the name of the field that will be compared. The value of "LT" is the expected value, it can be string/int/real. The LT predicate evaluates to true if the specified field is less than the expected value.

{ "key" : "length.mm", "LT" : 6.67 }

LTE

The LTE operator must appear as one of two keys in a structure, the other key is called "key". The value of "key" is the name of the field that will be compared. The value of "LTE" is the expected value, it can be string/int/real. The LTE predicate evaluates to true if the specified field is less than or equal to the expected value.

{ "key" : "surname", "LTE" : "D" }

GT

The GT operator must appear as one of two keys in a structure, the other key is called called "key". The value of "key" is the name of the field that will be compared. The value of "GT" is the expected value, it can be string/int/real. The GT predicate evaluates to true if the specified field is greater than the expected value.

{ "key" : "created", "GT" : 1713098624596}

GTE

The GTE operator must appear as one of two keys in a structure, the other key is called "key". The value of "key" is the name of the field that will be compared. The value of "GTE" is the expected value, it can be string/int/real. The GTE predicate evaluates to true if the specified field is greater than or equal to the expected value.

{ "key" : "overdue.months", "GTE" : 1 }

KEYEXISTS

The KEYEXISTS predicate evaluates to true if the specified key exists with any value. The KEYEXISTS predicate can be used inside SUBJECT and EVENTEXISTSWHERE contexts.

"SUBJECT": {
    "KEYEXISTS": "processDefinitionKey"
}

Comparison Key Format

The comparison operators all have a field called "key" that describes which component of the data to examine. In the value of "key" dots and square brackets are used to show nested structures and array positions. If an event data packet contained this structure:

{
    "first_name": "Fozzy",
    "last_name": "Bear",
    "referees": [{
        "first_name": "Rowlf",
        "last_name": "Dog"
    }, {
        "first_name": "Kermit",
        "last_name": "Frog"
    }, {
        "first_name": "Bunsen",
        "last_name": "Honeydew"
    }]
}

Then the first name of the first referee could be checked with:

{ "key" : "referees[0].first_name", "EQ" : "Rowlf" }

If a key in the data structure contains an actual dot, square bracket, or backslash, then that character has to be escaped with a backslash in the value of "key".

Example Data

Note how the backslash has to be escaped when storing:

{
  "misc":{
    "J.R.R. Tolkien":{
      "isa":"author"
    },
    "C:\\temp":{
      "isa":"dos path"
    }
  }
}

Example Comparisons

The backslashes themselves also have to be escaped:

"filter": {
    "EVENTEXISTSWHERE": {
        "key": "misc.J\\.R\\.R\\. Tolkien.isa",
        "EQ": "author"
    }
}and:

"filter": {
    "EVENTEXISTSWHERE": {
        "key": "misc.C:\\\\temp.isa",
        "EQ": "dos path"
    }
}

No Key

It is possible, although unlikely, that an event is a simple string and therefore has no "key" which can be used in the comparison. Consider this simplified history:

{
    "labela": "strangeevent",
    "labelb": "123",
    "event": "value"
}

Using the EVENTEXISTSWHERE modifier (see below) it could be retrieved using the following filter in a call to getHistories():

"filter": {
    "EVENTEXISTSWHERE": {
        "key": "",
        "EQ": "value"
    }
}

Context Modifiers

By default the comparison operators examine basic properties of the history. The "key" in the comparison must be set to one of "id", "labela", "labelb", "labelc", "labeld", "labele", "created" or "lastupdated".

The context modifiers switch the comparison operators to either the "Subject" or "Event" data packets and return any histories that have a subject or events matching the supplied filter.

The SUBJECT, LASTSUMMARY and EVENTEXISTSWHERE context modifiers cannot be used in combination with each other.

SUBJECT

The SUBJECT modifier must appear as the only key in a structure. Its value must be another predicate structure. Every comparison operator within the SUBJECT predicate refers to top level "Subject" data packets within a history.

"filter": {
    "SUBJECT": {
        "key": "userId",
        "EQ": "TIMG"
    }
}

LASTSUMMARY

The LASTSUMMARY modifier must appear as the only key in a structure. Its value must be another predicate structure. Every comparison operator within the LASTSUMMARY predicate refers to the last (ie most recent) event in a history where "event": "SUMMARY".

"filter": {
    "LASTSUMMARY": {
        "key": "formData.data.CASETYPE",
        "EQ": "example"
    }
}

EVENTEXISTSWHERE

The EVENTEXISTSWHERE modifier must appear as the only key in a structure. Its value must be another predicate structure. Every comparison operator within the EVENTEXISTSWHERE predicate refers to "Event" data packets. A history matches the EVENTEXISTSWHERE predicate if any one (or more) of its events match the conditions.

"filter": {
    "EVENTEXISTSWHERE": {
        "key": "proxyUserId",
        "EQ": "ADMIN"
    }
}

INTOP

The INTOP modifier must appear as the only key in a structure. Its value must be an integer. It must appear somewhere within an EVENTEXISTSWHERE predicate. INTOP evaluates to true if an event is within the first n events of the history. INTOP will usually be ANDed with comparison operators to look for events early in a history that match certain conditions.

{ "INTOP" : 3 }

INTAIL

The INTAIL modifier must appear as the only key in a structure. Its value must be an integer. It must appear somewhere within an EVENTEXISTSWHERE predicate. INTAIL evaluates to true if an event is within the last m events of the history. INTAIL will usually be ANDed with comparison operators to look for events late in a history that match certain conditions.

{ "INTAIL" : 1 }

Examples

This filter object demonstrates boolean operators, comparison operators, and a context modifier. It selects all histories that match one of three criteria.

1. It selects histories that have "labela":"Licence" AND "labelb":"5-July-2015"
2. It selects histories that have an event with "chocolate":"aero" OR "chocolate":"bounty" 
3. It selects a history with a known ID

{
    "OR": [{
        "AND": [{
            "key": "labela",
            "EQ": "Licence"
        }, {
            "key": "labelb",
            "EQ": "5-July-2015"
        }]
    }, {
        "EVENTEXISTSWHERE": {
            "OR": [{
                "key": "chocolate",
                "EQ": "aero"
            }, {
                "key": "chocolate",
                "EQ": "bounty"
            }]
        }
    }, {
        "key": "id",
        "EQ": "01234567-89AB-CDEF-0123-456789ABCDEF"
    }]
}

This filter is looking for histories of licence applications that apply to one of a couple of combinations of bars and landlords and where the history started out with a phone call and where the the application has been rejected.

{
    "AND": [{
        "key": "labela",
        "EQ": "Licence"
    }, {
        "SUBJECT": {
            "OR": [{
                "AND": [{
                    "key": "applicant.surname",
                    "EQ": "Brown"
                }, {
                    "key": "premises.name",
                    "EQ": "Green's Bar"
                }]
            }, {
                "AND": [{
                    "key": "applicant.surname",
                    "EQ": "Green"
                }, {
                    "key": "premises.name",
                    "EQ": "Brown's Bar"
                }]
            }]
        }
    }, {
        "AND": [{
            "EVENTEXISTSWHERE": {
                "AND": [{
                    "INTOP": 1
                }, {
                    "key": "contact.method",
                    "EQ": "Phone"
                }]
            }
        }, {
            "EVENTEXISTSWHERE": {
                "key": "status",
                "EQ": "Rejected"
            }
        }]
    }]
}

This example returns histories with the labela value "Feedback Review" created in the last 24 hours.

{
    "AND": [{
        "key": "labela",
        "EQ": "Feedback Review"
    }, {
        "key": "created",
        "GT": Date.now() - 24 * 60 * 60 * 1000
    }]
}

There are further examples in the Interrogating History article in the knowledge base.

eventFilter

All of the filters above are used to return histories. The eventFilter uses a subset of the filter syntax, and is used to filter the events returned within a history.

The boolean and comparison operators are valid at the top level of the filter. The comparison operators apply to fields within an event.

The context modifiers are not valid anywhere in an eventFilter. EventFilters do not support the keys "created" or "lastupdated".

Example

This example returns just the private events for a known history.

{
    "labela": "Example Workflow",
    "labelb": "9024-6411-7272-1022",
    "eventfilter": {
        "key": "private",
        "EQ": true
    }
}

Geographic Filters

The log() method recognises and records data in event and subject packets that contain geographic values.  This includes data generated by the location related fields of the forms designer (currently the Location Picker, Client Location and Hidden Location field types) saved in the formData packet of an event. The geographic filters can then be used with the boolean operators to return or exclude histories, or events within a history, a given distance from a location or within a defined polygon or multipolygon.

GEOGKEYEXISTS

Checks that a key exists and that it contains geographic data, then returns/excludes histories or events within a history, depending upon the boolean operator it is used with. Its value should be the name of the field to test. May be used in the SUBJECT and EVENTEXISTSWHERE contexts, or as part of an eventFilter when calling getHistory.

{ "GEOGKEYEXISTS": "formData.data.MYLOCATIONFIELD" }

GEOGDISTLT

Filters histories, or events within a single history, containing geographic data a given distance from a point, polygon or multipolygon.

Example - Filter Histories with Events

This filter would return any histories with an event that has coordinates saved in a field called formData.data.LOCATIONPICKER and is within 1000 metres of -0.161243, 51.474358. 

this.callWorkerMethod("history", "getHistories", {
    "filter": {
        "EVENTEXISTSWHERE": {
            "GEOGDISTLT": 1000,
            "key": "formData.data.LOCATIONPICKER",
            "to": {
                "type": "Point",
                "coordinates": [-0.161243, 51.474358]
            }
        }
    }
});

Example - Filter Histories by Subject

This example returns histories with event data saved in their subjects, in a property called "mySubjectPoint". The histories must be within 1000 metres of -0.161243, 51.474358.

this.callWorkerMethod("history", "getHistories", {
    "filter": {
        "SUBJECT": {
            "GEOGDISTLT": 1000,
            "key": "mySubjectPoint",
            "to": {
                "type": "Point",
                "coordinates": [-0.161243, 51.474358]
            }
        }
    },
});

GEOGWITHIN

Filters histories, or events within a single history, containing geographic data within a polygon or multipolygon.

Example - Events within a Polygon

This example returns any histories that have events with location data in roughly the London area.

this.callWorkerMethod("history", "getHistories", {
    "filter": {
        "EVENTEXISTSWHERE": {
            "key": "formData.data.LOCATIONPICKER",
            "GEOGWITHIN": {
                "type": "Polygon",
                "coordinates": [
                    [
                        [-0.28564453125, 51.82219818336938],
                        [-0.68115234375, 51.536085601784755],
                        [-0.406494140625, 51.25503952021694],
                        [0.274658203125, 51.26191485308451],
                        [0.37353515625, 51.62483746174322],
                        [-0.28564453125, 51.82219818336938]
                    ]
                ]
            }
        }
    }
});

Example - Exclude a Polygon

This example uses geographic filters with the boolean operators. It returns histories that have events with location data in (roughly) the UK but outside of the London area. We need to set two areas to check. First the filter looks for histories with events in the UK, then excludes those in London. Just setting "not the London area" would return every history not in the London area in the database, even those without any location data at all.

this.callWorkerMethod("history", "getHistories", {
    "filter": {
        "AND": [{
            "EVENTEXISTSWHERE": {
                "key": "formData.data.LOCATIONPICKER",
                "GEOGWITHIN": {
                    "type": "Polygon",
                    "coordinates": [
                        [
                            [-8.349609375, 49.63917719651036],
                            [1.9116210937499998, 49.63917719651036],
                            [1.9116210937499998, 59.45624336447568],
                            [-8.349609375, 59.45624336447568],
                            [-8.349609375, 49.63917719651036]
                        ]
                    ]
                }
            }
        }, {
            "NOT": {
                "EVENTEXISTSWHERE": {
                    "key": "formData.data.LOCATIONPICKER",
                    "GEOGWITHIN": {
                        "type": "Polygon",
                        "coordinates": [
                            [
                                [-0.28564453125, 51.82219818336938],
                                [-0.68115234375, 51.536085601784755],
                                [-0.406494140625, 51.25503952021694],
                                [0.274658203125, 51.26191485308451],
                                [0.37353515625, 51.62483746174322],
                                [-0.28564453125, 51.82219818336938]
                            ]
                        ]
                    }
                }
            }
        }]
    }
});

Geographic Form Field Types

Note that the form location fields using Google maps record their geographic data as latitude/longitude values rather than longitude/latitude, so while the coordinates in saved form data may look like this:

"formData": {
    "data": {
        "TEST1": "test",
        "LOCATIONPICKER": "51.4828989175, -0.1246892262" //This was a point in central London, not somewhere off the coast of Somalia
    },
    "formName": "STARTWORKFLOW",
    "typeName": "FORM_STARTWORKFLOW",
    "formDefinitionType": "FORMDEFINITIONEX"
}

The history worker will retrieve the event using this filter:

{
    "EVENTEXISTSWHERE": {
        "GEOGDISTLT": 10,
        "key": "formData.data.LOCATIONPICKER",
        "to": {
            "type": "Point",
            "coordinates": [-0.1246892263, 51.4828989176]
        }
    }
}

Data from Repeating Form Pages

If a form has repeating pages, and the form data is written to history, the data of the repeating page is structured as an array of objects. The array name is the name of the page, and the keys of each object the field names. For example, history written by a workflow action field, with two repeats of page 1 would look like this:

"formData": {
    "data": {
        "PAGE1": [{
            "NAME": "Tim",
            "AGE": "41"
        }, {
            "NAME": "Leo",
            "AGE": "40"
        }]
    },
    "formName": "TIMSFORM",
    "typeName": "FORM_TIMSFORM",
    "formDefinitionType": "FORMDEFINITIONEX"
}

So that it's possible to write filters that query this data, the keys of form data from repeating pages are stored using generic array indexes.

For example, given the form data above, the keys would be written to the database as formData.data.PAGE1[*].NAME, formData.data.PAGE1[*].AGE. These keys can then be used in your filter objects. The actual keys formData.data.PAGE1[0].NAME, formData.data.PAGE1[1].NAME etc are also saved, and used when the history is returned via the various API methods, so the event can be reconstructed and appear in the same format as the example above. Note that it is not possible to write filters using the true array index.

To filter by the form data of a repeating page, the generic key for the data above would be:

"filter": {
    "AND": [{
        "key": "labela",
        "EQ": "WRITEHISTORY"
    }, {
        "EVENTEXISTSWHERE": {
            "key": "formData.data.PAGE1[*].AGE",
            "EQ": "41"
        }
    }]
}

This will match against the AGE key in any instance of PAGE1.