Aggregate Records

Suggest Edits

Overview

You can obtain a list of Records calculated and grouped based on given conditions using the Aggregate Records endpoint in Morph API.

You need to define several parameters to build the request for that endpoint.

These parameters are:

  • teamSlug, databaseId and tableSlug as well as API Key.
  • The parameters of the AggregateRecordRequestBodyObject object of the body parameter.

This guide will focus on explaining how to the define and use the parameters of that AggregateRecordRequestBodyObject object.

📘

The steps to obtain teamSlug, databaseId and tableSlug as well as the API Key are detailed in the Quickstart.


Brief overview of the AggregateRecordRequestBodyObject object:

  • the groupKeys property is the field on which the results of the aggregation calculation will be done. The results of the aggregation calculation which you define in aggregation will be returned for each value of this field.

  • the aggregation property is where you define the field and the aggregation calculation variables to run.

  • Pass a filter property if you wish to narrow down the aggregation calculation on a subset of your Table.

  • The sort property is to order your aggregated results (by ascending or descending order)

  • The join property

  • the limit property is to limit the aggregated results. This is an optional parameter. By default Morph API returns the 20 first Records

  • the skip property if for pagination. This parameter is optional. By default Morph API returns the 20 first Records.


Example for this guide

We will see examples to:

  • Aggregate records with a sort

Aggregate Records with sort

Let's look at a simple example of aggregating records and ordering them using sort.

We will make an API request to the Aggregate Records endpoint for the [Sample] Employees Table to obtain the total annual_salary for each business_unit. And we want to obtain the results from the highest to the lowest annual_salary total.


Build the request

To build the request, you will need the following parameters.

Headers

  • authorization header: x-api-key: {YOUR_API_KEY}
  • client-type: widget
  • Content-Type: application/json

ℹ️

How to get my API Key?

Head to Create an API Key.


Path parameters

  • teamSlug
  • databaseId
  • tableSlug

ℹ️

Where can I find my teamSlug, databaseId and tableSlug?

Go to How to find teamSlug, databaseId and tableSlug.


Body parameter

As shown in the sample Table and body parameter, for our example we will need to define:

  • The groupKeys array with the field name. The aggregation results will be returned for each value of this field.

  • The aggregation object to define the aggregation calculation:

    • key : field name to run aggregation calculation on
    • operator : the type aggregation calculation (count, sum, mean, median - AggregationOperatorType)
    • (Optional) Record aggregation filter object: additional condition to apply on the aggregation calculation. This object is optional. If you apply this filter, you need define both its operator and value properties:
      • operator: apply one of the Record Aggregation Filter operators.
      • value: the value associated with the operator.

  • (Optional) The sort array: to order the aggregated results by ascending or descending order. The sort condition is applied to the field name you define in aggregation (key property) not on the field name of the groupKeys. This object is optional. If you apply this sort, you need define both its operator and value properties:

    • key: needs to be the exact string morph_aggregation_result
    • direction: either ascending or descending

🚧

The sort condition is applied to the field name you define in aggregation (key property) not on the field name of the groupKeys.


📘

Look at the sample Table for a simple example.


{
  "groupKeys": ["{FIELD NAME - RESULTS FOR EACH VALUE}"],
  "aggregation": {
    "key": "{FIELD NAME - AGGREGATION CALCULATION}",
    "operator": "{OPERATOR - AGGREGATION CALCULATION}",
    "filter": { // OPTIONAL
      "operator": "{OPERATOR FOR RECORD AGGREGATION}",
      "value": "{VALUE}"
    }
  },
  "sort": [
    {
      "key": "morph_aggregation_result", // ALWAYS THIS VALUE
      "direction": "{SORT OPERATOR}"
    }
  ]
}

For our example to obtain the total annual_salary for each business_unit with the results ordered from the highest to the lowest total annual_salary.

{
  "groupKeys": ["business_unit"],
  "aggregation": {
    "key": "annual_salary",
    "operator": "sum",
    "filter": {
      "operator": "greaterThanOrEqual",
      "value": 0
    }
  },
  "sort": [
    {
      "key": "morph_aggregation_result",
      "direction": "descending"
    }
  ]
}

Make the API request

cURL

To make the HTTP request directly using cURL:

  • Open a Terminal window.
  • Replace the following placeholders in the cURL template below with your own values.
    • In the URL:
      • {YOUR_TEAM_SLUG}
      • {YOUR_DATABASE_ID}
      • {YOUR_TABLE_SLUG}
    • In the headers:
      • {YOUR_API_KEY} for the x-api-key
  • Run the command in your Terminal.

Replace the placeholders with your own values

curl --X POST 'https://{YOUR_TEAM_SLUG}.api.morphdb.io/v0/record/{YOUR_DATABASE_ID}/{YOUR_TABLE_SLUG}/aggregate' \
--header 'Content-Type: application/json' \
--header 'client-type: widget' \
--header 'x-api-key: {YOUR_API_KEY}' \
--data '{
  "groupKeys": ["{FIELD NAME - RESULTS FOR EACH VALUE}"],
  "aggregation": {
    "key": "{FIELD NAME - AGGREGATION CALCULATION}",
    "operator": "{OPERATOR - AGGREGATION CALCULATION}",
    "filter": { // OPTIONAL
      "operator": "{OPERATOR FOR RECORD AGGREGATION}",
      "value": "{VALUE}"
    }
  },
  "sort": [
    {
      "key": "morph_aggregation_result", // ALWAYS THIS VALUE
      "direction": "{SORT OPERATOR}"
    }
  ]
}'

With values for our example:

curl --X POST 'https://acme.api.morphdb.io/v0/record/dw5g1h89-2637-253k-452b-7803j4x7e3vi/employee/aggregate' \
--header 'Content-Type: application/json' \
--header 'client-type: widget' \
--header 'x-api-key: ms5sCDEOVNuIw92MNA3qLKih4xzY25D9PQY6D7Az2' \
--data '{
  "groupKeys": ["business_unit"],
  "aggregation": {
    "key": "annual_salary",
    "operator": "sum",
    "filter": {
      "operator": "greaterThanOrEqual",
      "value": 0
    }
  },
  "sort": [
    {
      "key": "morph_aggregation_result",
      "direction": "descending"
    }
  ]
}'

Response

A successful response will return all the results of the aggregation calculation. In our example, we obtained the total annual_salary for each business_unit with the results ordered from the highest to the lowest total annual_salary.

You should see the following successful response in your Terminal:

{
    "items": [
        {
            "business_unit": "Specialty Products",
            "morph_aggregation_result": "4203410"
        },
        {
            "business_unit": "Corporate",
            "morph_aggregation_result": "3261906"
        },
        {
            "business_unit": "Research & Development",
            "morph_aggregation_result": "1930679"
        },
        {
            "business_unit": "Manufacturing",
            "morph_aggregation_result": "1561576"
        }
    ],
    "count": 4
}

Debugging

📘

Not the expected result?

Try the following debugging steps.


Updated 2 months ago