Filters

Some APIs in Workspaces make use of an optional "filters" attribute in the JSON Query object.
If provided, only the entity records that satisfy the filters are returned. The following APIs support filtering:

Filter Attributes

Filters consist of a list of map objects.
Each map can be thought of as a separate "filter" with the following attributes.

Attribute Type Description
criteria List Specifies a list of criteria strings in the format "attribute operator value".
The attributes are the supported criteria attributes specific to the API, the full list can be found in the API documentation for Jobs, Txns.
Supports the use of params, criteria string in the format "attribute operator {N}" uses the Nth item in the params list.
Without params: "formCode = 'FTX-CCA'"
With params: "formCode = {0}" (Optional)
operator String Specifies the operator, either "AND" or "OR", to join each string in the criteria attribute by.
If not specified, "AND" is used. (Optional)
params List Specifies a list of parameters to substitute in the place of the "value" in a criteria string.
The Nth item in the list becomes the "value" of the criteria string if the criteria string is in the format "attribute operator {N}".
The index of the list is 0. (Optional)

If no filters are specified in the API, the equivalent is:

``` { "fetchLimit": 100, ... "filters": [ { "criteria": [ ], "params": [ ], "operator": "AND" } ] } ```

Example 1: Single Filter

The following is an example of a basic Txn Query a single filter without the "param" attribute. 

{
    "fetchLimit": 100,
    "fetchOffset": 0,
    "filters": [
        {
            "criteria": [
                "trackingCode = 'ZT73VWL'",
                "timeUserLastModified < '2018-09-06'"
            ],
            "operator": "AND"
        }
    ],
    "sorts": [
        "submitKey asc"
    ]
}

The "params" attribute provides a list of values that are used to replace placeholders in a criteria string in the format "{N}" where N is the position of the value to substitute in the param list.
The above query converted to use "params" would resemble: 

{
    "fetchLimit": 100,
    "fetchOffset": 0,
    "filters": [
        {
            "criteria": [
                "trackingCode = {0}",
                "timeUserLastModified < {1}"
            ],
            "params": [
                "ZT73VWL",
                "2018-09-06"
            ],
            "operator": "AND"
        }
    ],
    "sorts": [
        "submitKey asc"
    ]
}

The equivalent pseudo query is the following:

``` SELECT * FROM SUBMISSIONS S WHERE ( S.trackingCode = 'ZT73VWL' AND S.timeUserLastModified < '2018-09-06' ) ORDER BY S.submitKey ASC ```

Example 2: Multiple Filters

Multiple filters can be specified with each "filter" is joined by "AND".
The following is an example of Txn Query with multiple filters, and its equivalent pseudo Query. 

{
    "fetchLimit": 100,
    "fetchOffset": 0,
    "filters": [
        {
            "criteria": [
                "trackingCode = {0}",
                "timeUserLastModified &lt; '2018-09-06'"
            ],
            "params": [
                "ZT73VWL"
            ],
            "operator": "AND"
        },
        {
            "criteria": [
                "formName = {0}",
                "spaceName = {0}",
                "spaceName = {1}"
            ],
            "params": [
                "workspace",
                "Work Spaces"
            ],
            "operator": "OR"
        },
        {
            "criteria": [
                "timeUserLastModified > '2018-05-06'"
            ]
        }
    ],
    "sorts": [
        "submitKey asc"
    ]
}

SELECT * FROM
SUBMISSIONS S
WHERE (
    S.trackingCode = 'ZT73VWL' AND
    S.timeUserLastModified &lt; '2018-09-06'
)
AND
(
    S.formName = 'workspace' OR
    S.spaceName = 'workspace' OR
    S.spaceName = 'Work Spaces'
)
AND
(
    S.timeUserLastModified > '2018-05-06'
)
ORDER BY S.submitKey ASC
← Workspaces APISort →