Purpose

Aggregation operations in JQL allow you to perform calculations on data, such as counting, summing, averaging, or other forms of aggregation, directly within your queries. These operations enable you to generate summaries or insights from your data without needing to retrieve and process it externally. Aggregations are particularly useful for reporting, analytics, and data analysis within Antly’s backend system.

Syntax

The JSON structure for an aggregation operation in JQL includes the following components:

• __meta__: Metadata about the query, including the target namespace, schema, intent, filters, and pagination details.
• Aggregation Fields: Special fields within the query that perform aggregation functions, often using subqueries and specific database functions.

General Structure of an Aggregation Query:

{
    "__meta__": {
        "authenticationClass": "<authentication_method>",
        "intent": "retrieve",
        "namespace": "<module_name>",
        "schema": "<schema_type>",
        "filter": { "<filter_criteria>": "<value>" },
        "ordering": [
            "<field_name>", 
            "-<field_name>"
        ],
        "limit": <number_of_results>,
        "offset": <number_of_results_to_skip>
    },
    "<field_name_1>": null,
    "<aggregation_field>": {
        "__meta__": {
            "namespace": "<related_module_name>",
            "intent": "subquery",
            "ordering": "<field_to_order_by>",
            "onField": "<related_field>",
            "outerRefMapping": [
                {
                    "field": "<field_to_map>",
                    "referenceField": "<related_field_to_map>"
                }
            ],
            "sequencedProcess": [
                {
                    "function": "<db_function>",
                    "castType": "<data_type>",
                    "onField": "<target_field>"
                }
            ]
        }
    }
}

Example: Sample Aggregation Query

Below is an example of an aggregation query that counts the number of subcategories within each category. The subcategoriesCount field is an aggregation that counts all subcategories related to a category.

Example: Counting Subcategories for Each Category

{
    "__meta__": {
        "namespace": "tickets.Category",
        "schema": "model",
        "intent": "retrieve",
        "filter": {},
        "ordering": ["pk"],
        "limit": 10,
        "offset": 0,
        "authenticationClass": "session"
    },
    "id": null,
    "fqn": null,
    "created": null,
    "name": null,
    "description": null,
    "subcategoriesCount": {
        "__meta__": {
            "namespace": "tickets.SubCategory",
            "intent": "subquery",
            "ordering": "category",
            "onField": "category",
            "outerRefMapping": [
                {
                    "field": "pk",
                    "referenceField": "category.pk"
                }
            ],
            "sequencedProcess": [
                {
                    "function": "count",
                    "castType": "int",
                    "onField": "*"
                }
            ]
        }
    }
}

In this example:

• namespace: Targets the tickets.Category module, where the primary data is retrieved.
• subcategoriesCount field: This field uses a subquery to count the number of subcategories associated with each category.
  • namespace in subquery: Targets the tickets.SubCategory module, where the subcategories are stored.
  • onField: Specifies the relationship field (category) that links categories to subcategories.
  • outerRefMapping: Maps the primary key (pk) of the category to the category.pk in the subcategory.
  • sequencedProcess: Uses the count function to count all records (*) in the subcategory table related to the category.

Example: Sample Response

The response for this aggregation query might look like this:

{
    "status": "OK",
    "data": [
        {
            "id": "1",
            "fqn": "General Support",
            "created": "2024-01-01T10:00:00Z",
            "name": "General Support",
            "description": "Support related to general inquiries",
            "subcategoriesCount": 5 // The number of subcategories under this category
        },
        {
            "id": "2",
            "fqn": "Technical Support",
            "created": "2024-01-01T11:00:00Z",
            "name": "Technical Support",
            "description": "Support for technical issues",
            "subcategoriesCount": 3 // The number of subcategories under this category
        }
        // More categories
    ],
    "errors": null,
    "il8n": "en-gb",
    "extras": {
        "filtered": 10,
        "display": 10,
        "queries": 1
    },
    "system_info": null,
    "intent": "retrieve",
    "message": null
}

In this response:

• subcategoriesCount: Shows the count of subcategories for each category as determined by the aggregation query.
• Other fields: Include standard fields such as id, fqn, created, name, and description for each category.