Queries

Introduction

You can join collections by lookup-references, and select what properties from each of the joined collections you would like to include in the output.

Queries share namespace with regular collections, and the endpoints for queries use the following URL format:

https://<host-name>/<api-path>/<query-name>

Use the query builder in the developer portal to design and edit Queries.

Sample queries

Using the following sample-schema, we can create scalar- and aggregate-queries with one or multiple joins.

Sample schema:

Simple query

Sample query with orders joined with their matching customers:

The selected properties to include in the output need to be uniquely aliased within the scope of the query.

You can execute the query in the Query explorer in the developer portal. The model of the result will contain the selected properties, using the aliases provided:

{
    "data": [
        {
            "customer": "Customer C",
            "state": "CA",
            "date": "2024-02-25"
        },
        {
            "customer": "Customer E",
            "state": "WA",
            "date": "2024-02-25"
        },
        {
            "customer": "Customer B",
            "state": "NV",
            "date": "2024-02-26"
        },
        {
            "customer": "Customer A",
            "state": "CA",
            "date": "2024-02-26"
        },
        {
            "customer": "Customer E",
            "state": "WA",
            "date": "2024-02-26"
        },
        {
            "customer": "Customer D",
            "state": "AZ",
            "date": "2024-02-27"
        }
    ]
}

Aggregation query

Sample query with customers joined with their matching orders joined with their matching orderLines, and aggregated by customers.name with the alias customer and using the SUM function for the property orderLines.lineTotal with the alias totalAmount to calculate the total value of the orders.

You can execute the query in the Query explorer in the developer portal. The model of the result will contain the selected properties, using the aliases provided:

{
    "data": [
        {
            "customer": "Customer D",
            "totalAmount": 13453.9000
        },
        {
            "customer": "Customer E",
            "totalAmount": 12912.0000
        },
        {
            "customer": "Customer A",
            "totalAmount": 8766.4500
        },
        {
            "customer": "Customer E",
            "totalAmount": 5901.0000
        },
        {
            "customer": "Customer B",
            "totalAmount": 5088.5000
        },
        {
            "customer": "Customer C",
            "totalAmount": 1850.0000
        }
    ]
}