Purpose

Complex queries in JQL allow you to perform advanced data retrieval and manipulation by working with deeply nested structures and applying transformations to the data before it is returned. These capabilities are essential for handling sophisticated data requirements, such as formatting dates, transforming values, and managing nested relationships between different entities, including reverse accessors to pull related data.

Syntax

To structure a complex query in JQL, you typically include the following components:

• __meta__: Metadata about the query, including the target namespace, schema, intent, filters, and pagination details.
• Data Fields: Fields from the resource being queried, which may include nested objects and transformations.
• Transforms: Special instructions within the query that manipulate or format the data before it is returned. These are specified using the __transform__ key.

General Structure of a Complex 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>": {
        "__transform__": {
            "<transform_type>": {
                "<transform_options>": "<value>"
            }
        }
    },
    "<field_name_2>": {
        // Nested fields or further transforms
    },
    // Additional fields and transforms
}

Example: Sample Complex Query

Below is an example of a complex query involving a Customer entity with deeply nested fields and transformations, including a reverse accessor to pull related transactions.

Example: Retrieving a Customer with Transactions and Transformations

This query retrieves a list of customers, applying a transformation to the created field to format it as a human-readable date, and uses a reverse accessor to pull related transactions for each customer. Additionally, we transform the status field of each transaction to display a human-readable label.

{
    "__meta__": {
        "namespace": "objects.Customer",
        "schema": "model",
        "intent": "retrieve",
        "filter": {
            "isActive": true
        },
        "ordering": ["created_at"],
        "limit": 10,
        "offset": 0,
        "authenticationClass": "session"
    },
    "id": null,
    "name": null,
    "email": null,
    "created": {
        "__transform__": {
            "dateFormat": {
                "format": "%b %d, %Y - %I:%M%p" // Transforms datetime to a readable format
            }
        }
    },
    "address": {
        "street": null,
        "city": null,
        "postalCode": null,
        "country": {
            "name": null,
            "code": null
        }
    },
    "transactions": { // Reverse accessor to pull related transactions
        "id": null,
        "amount": null,
        "transactionDate": {
            "__transform__": {
                "dateFormat": {
                    "format": "%Y-%m-%d" // Transforms the transaction date
                }
            }
        },
        "status": {
            "__transform__": true,
            "label": "tuple" // Transforms the status field into a tuple of value and label
        },
        "items": { // Nested fields within transactions
            "id": null,
            "productName": null,
            "quantity": null,
            "price": null
        }
    }
}

In this example:

• namespace: Targets the objects.Customer module.
• filter: Filters customers to include only those who are active (isActive: true).
• ordering: Orders results by the created_at field in ascending order.
• created field: The dateFormat transform is applied to format the date as "Month Day, Year - Hour:MinuteAM/PM".
• address field: Includes nested fields such as street, city, postalCode, and country, which itself is a nested object.
• transactions field: Uses a reverse accessor to pull related transactions. Each transaction includes nested fields like id, amount, transactionDate, status, and items.
  • transactionDate field: The dateFormat transform is applied to format the transaction date.
  • status field: The __transform__ key with the label: "tuple" option converts the status field into a tuple, showing both the raw database value and its human-readable label.
  • items field: Further nested fields within each transaction, including details about the items involved in the transaction.

Example: Sample Response

The response for this complex query might look like this:

{
    "status": "OK",
    "data": [
        {
            "id": "12345",
            "name": "John Doe",
            "email": "john.doe@example.com",
            "created": "Aug 21, 2024 - 10:15AM",
            "address": {
                "street": "123 Main St",
                "city": "Anytown",
                "postalCode": "12345",
                "country": {
                    "name": "United States",
                    "code": "US"
                }
            },
            "transactions": [
                {
                    "id": "98765",
                    "amount": "250.00",
                    "transactionDate": "2024-08-20",
                    "status": ["COM", "Completed"], // Transformed into a tuple with raw value and label
                    "items": [
                        {
                            "id": "1",
                            "productName": "Laptop",
                            "quantity": 1,
                            "price": "250.00"
                        }
                    ]
                }
                // More transactions
            ]
        }
        // More customers
    ],
    "errors": null,
    "il8n": "en-gb",
    "extras": {
        "filtered": 10,
        "display": 10,
        "queries": 1
    },
    "system_info": null,
    "intent": "retrieve",
    "message": null
}

In this response:

• created field: The date is formatted as specified in the query.
• transactions field: Includes details about each transaction related to the customer, with the status field transformed into a tuple showing the status code and its label.
• items field: Shows the details of items involved in each transaction.

These examples illustrate how to leverage complex queries in JQL to retrieve and manipulate deeply nested data, apply transformations, and use reverse accessors to pull related records. This allows for advanced data handling and presentation in Antly’s system.