Zylo allows for users to query individual resources (such as Applications, Payments, etc.) that can be returned via a HTTP request.

The queries are used to select specific fields, aggregate data, filter results and more.

Queries are built by creating JSON objects that specify:

• Which resource the operation should be ran on.
• How the fields should be displayed/aggregated.
• How the results should be sorted, filtered, and limited.

📘

Important Note

Query builder does not work with savingsEvents and activityHistory at this time.

Query Builder Syntax

Below is an example of a query JSON object to get the "Top 10 Applications by Category". Details on the query syntax are beneath the example.

{
    "builder": {
        "applications": {
            "fields": [
                {
                    "name": "category",
                    "as": "category"
                },
                {
                    "name": "id",
                    "op": "count",
                    "as": "total_count"
                }
            ]
        }
    },
    "sort": "+total_count",
    "limit": 10,
    "skip": 0
}

Let's break down what is happening in the query object above.

Top-Level Structure

Every query object follows the same structure:

{
    "builder": {
        ...
    },
    "sort": "+total_count",
    "limit": 10,
    "skip": 0
}

• builder - This field is used to tell Zylo what resource you want to run the query on. Each query needs to have the builder field to work.
• sort - Used to sort the query. In the above example, it will sort the query by the "total_count" field.
• limit - Used to set the limit of returned rows in the query. If not provided, it will default to the API limit (50 results).
• skip - Used for paginating the results. If not provided, it will default to the API limit (0).

Builder Syntax

The builder object defines your primary resource and any related resource.

Primary Resource

{
    "builder": {
        "applications": {
            ...
        }
    },
    "sort": "+total_count",
    "limit": 10,
    "skip": 0
}

• applications - This is the resource for your query. All operations in the builder will be ran on this resource.

Resource Fields

{
    "builder": {
        "applications": {
            "fields": [
                {
                    "name": "category",
                    "as": "category"
                },
                {
                    "name": "id",
                    "op": "count",
                    "as": "total_count"
                }
            ]
        }
    },
    "sort": "+total_count",
    "limit": 10,
    "skip": 0
}

• fields - These are the fields from the resource that are brought into the query. In the above example, the query is retrieving the "category" field from the applications resource with the name parameter. The as parameter is an alias, which means the app_label will come back as label in the query. The "id" field has an op field attached to it. This indicates how the field should be queried.

The list of available op operations are:

• sum - Sum up the values.
• max - Retrieve the max value.
• min - Retrieve the min value.
• avg - Calculate the average value.
• count - Count the occurrences of the value.

Filtering

The query builder syntax allows for filtering down the results, and can be filtered down on different levels.

The filter syntax for the query is similar to the API Filter Syntax.

• Filter down the entire query results set.
• Filter down on a resource level.
• Filter down on a field-level.

Below are use cases for each filtering type.

Query-Level Filtering

Query-level filtering are filters applied to the result of the query.

Using the above example query, a filter can be added to only retrieve total_amount that is greater than 5.

{
    "builder": {
        "applications": {
            "fields": [
                {
                    "name": "category",
                    "as": "category"
                },
                {
                    "name": "id",
                    "op": "count",
                    "as": "total_count"
                }
            ]
        }
    },
    "sort": "+total_count", 
    "filters": {
        "total_count": "5,gt"
    },
    "limit": 10,
    "skip": 0
}

Take note where the filters value is positioned. It is on the top-level similar to the builder value. This indicates a query-level filter.

Resource-Level Filtering

Resouce-level filtering are filters that are applied to a single resource in the query.

Using the above example query, we can apply a filter to only get applications with a "tag" of "Engineering".

{
    "builder": {
        "applications": {
            "fields": [
                {
                    "name": "category",
                    "as": "category"
                },
                {
                    "name": "id",
                    "op": "count",
                    "as": "total_count"
                }
            ],
            "filters": {
                "tags": ["Engineering"]
            }
        }
    },
    "sort": "+total_count",
    "limit": 10,
    "skip": 0
}

Take note where the filters value is positioned. It is on the same level as fields under the applications resource. This indicates a resource-level filter.

Field-Level Filtering

Field-level filtering are filters that are applied to a single field within a resource.

Using the above example query, we can add two new fields. One is the number of those applications by category that is it_supported, and the other is how many are not it_supported.

{
    "builder": {
        "applications": {
            "fields": [
                {
                    "name": "category",
                    "as": "category"
                },
                {
                    "name": "id",
                    "op": "count",
                    "as": "total_count"
                },
                {
                    "name": "id",
                    "op": "count",
                    "filters": {
                        "it_supported": true
                    },
                    "as": "it_supports"
                },
                {
                    "name": "id",
                    "op": "count",
                    "filters": {
                        "it_supported": false
                    },
                    "as": "it_not_supported"
                }
            ]
        }
    },
    "sort": "+total_count",
    "limit": 10,
    "skip": 0
}

Take note where the filters value is positioned. It is on the same level as name for each of the fields. This indicates a field-level filter.