Filter query results

Some Workspaces APIs make use of an optional "filters" attribute in the JSON Query object. The "filters" attribute limits the entity records returned to only those that satisfy the specified filters.

The following APIs support filtering:

• POST Form Query
• POST Group Query
• POST Job Count
• POST Job Query
• POST Txn Count
• POST Txn Query
• POST User Query

Attributes

A filter consists of a list of map objects, each of which can be considered as a separate filter with the following attributes.

All attributes are optional except where otherwise indicated.

Attribute	Description
criteria	List of String A list of filter criteria strings, each of which uses the format "ATTRIBUTE OPERATOR VALUE". The available filter attributes are specific to each API, and documented for each API that supports filtering. You can specify a parameterized filter criteria string using the format "ATTRIBUTE OPERATOR {N}" which uses the Nth item from the params list. Example: Without params: "formCode = 'FTX-CCA'" With params: "formCode = {0}"
operator	String The operator, either "AND" or "OR", to join the filter criteria strings in the criteria attribute. If not specified, "AND" is used.
params	List A list of parameters to substitute for the "VALUE" in a filter criteria string. The Nth item in the list becomes the "VALUE" in a filter criteria string using the format "ATTRIBUTE OPERATOR {N}". The first list index is 0.

Multiple filters can be specified. In this case, the items in the "filters" attribute are joined by AND. See Multiple filters below for an example.

If no filters are specified in an API, this is equivalent to:

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

Examples

Single filter

This example illustrates a basic Txn Query with a single filter.

The filter can be specified without using the "params" attribute.

Single filter without params

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

The same filter can be specified using the "params" attribute.

Single filter with params

{
    "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:

Single filter - Equivalent pseudo-query

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

Multiple filters

This example illustrates a Txn Query with multiple filters using the "params" attribute.

Multiple filters

{
    "fetchLimit": 100,
    "fetchOffset": 0,
    "filters": [
        {
            "criteria": [
                "trackingCode = {0}",
                "timeUserLastModified < '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"
    ]
}

The equivalent pseudo-query is:

Multiple filters - Equivalent pseudo-query

SELECT * FROM
SUBMISSIONS S
WHERE (
    S.trackingCode = 'ZT73VWL' AND
    S.timeUserLastModified < '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