# Aggregate

> Perform aggregation operations like count, sum, average, etc., on data within a collection using API Maker.

Source: https://docs.apimaker.dev/v4/examples/sys/db/aggregate.html

> **The code in every language**
>
> Each example is the inside of `main`, written in every [language](https://docs.apimaker.dev/v4/docs/languages/index.html) API Maker runs : pick yours in the tabs, and every page shows it from then on. The types a call takes (`GetAll`, `DeepItem`…) are those of `am` : `from am import GetAll` in Python, `use am::GetAll;` in Rust, `import am.GetAll;` in Java and Kotlin ; PHP and C# have them without an import, Go names them `am.GetAll`.

`g.sys.db.aggregate` calls the [aggregate schema API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-aggregate-api.html) of any table from your code, with the same headers and query parameters as over HTTP. It returns the data. The whole envelope (`success`, `statusCode`, `data`, `totalCount`, `errors`…) comes back when `true` is passed as the second argument in TypeScript, and through `full` in the other languages : `g.sys.db.full` (`g.Sys.Db.Full` in C# and Go, `$g->sys->db->full` in PHP).

Aggregation runs on MongoDB. `aggregateQuery` is a pipeline of MongoDB : every stage and operator of the [MongoDB manual](https://www.mongodb.com/docs/manual/reference/operator/aggregation-pipeline/) works, so this page shows three common shapes.

## Group, filter and count : $group, $match

**TypeScript**

**MongoDB Basic aggregation**

```typescript
let aggregate = await g.sys.db.aggregate({
    instance: "mongodb", database: "inventory", collection: "customers",
    aggregateQuery: [
        {
            "$group": {
                "_id": "$_id",
                "total": {
                    "$sum": "$customer_id"
                }
            }
        },
        {
            "$match": {
                "total": {
                    "$gt": 1
                }
            }
        },
        {
            "$sort": {
                "total": -1
            }
        },
        {
            "$count": "CountValue"
        }
    ]
});
```

**Python**

**MongoDB Basic aggregation**

```python
aggregate = await g.sys.db.aggregate(
    'mongodb', 'inventory', 'customers',
    aggregate_query=[
        {
            '$group': {
                '_id': '$_id',
                'total': {
                    '$sum': '$customer_id',
                },
            },
        },
        {
            '$match': {
                'total': {
                    '$gt': 1,
                },
            },
        },
        {
            '$sort': {
                'total': -1,
            },
        },
        {
            '$count': 'CountValue',
        },
    ],
)
```

**PHP**

**MongoDB Basic aggregation**

```php
$aggregate = $g->sys->db->aggregate(
    'mongodb', 'inventory', 'customers',
    aggregateQuery: [
        [
            '$group' => [
                '_id' => '$_id',
                'total' => [
                    '$sum' => '$customer_id',
                ],
            ],
        ],
        [
            '$match' => [
                'total' => [
                    '$gt' => 1,
                ],
            ],
        ],
        [
            '$sort' => [
                'total' => -1,
            ],
        ],
        [
            '$count' => 'CountValue',
        ],
    ],
);
```

**C#**

**MongoDB Basic aggregation**

```csharp
var aggregate = await g.Sys.Db.Aggregate(new Aggregate("mongodb", "inventory", "customers")
{
    AggregateQuery = [
        new JsonObject
        {
            ["$group"] = new JsonObject
            {
                ["_id"] = "$_id",
                ["total"] = new JsonObject
                {
                    ["$sum"] = "$customer_id",
                },
            },
        },
        new JsonObject
        {
            ["$match"] = new JsonObject
            {
                ["total"] = new JsonObject
                {
                    ["$gt"] = 1,
                },
            },
        },
        new JsonObject
        {
            ["$sort"] = new JsonObject
            {
                ["total"] = -1,
            },
        },
        new JsonObject
        {
            ["$count"] = "CountValue",
        },
    ],
});
```

**Go**

**MongoDB Basic aggregation**

```go
aggregate, err := g.Sys.Db.Aggregate(am.Aggregate{
	Instance: "mongodb", Database: "inventory", Collection: "customers",
	AggregateQuery: []any{
		am.M{
			"$group": am.M{
				"_id": "$_id",
				"total": am.M{
					"$sum": "$customer_id",
				},
			},
		},
		am.M{
			"$match": am.M{
				"total": am.M{
					"$gt": 1,
				},
			},
		},
		am.M{
			"$sort": am.M{
				"total": -1,
			},
		},
		am.M{
			"$count": "CountValue",
		},
	},
})
if err != nil {
	return nil, err
}
```

**Rust**

**MongoDB Basic aggregation**

```rust
let aggregate = g.sys.db.aggregate(
    Aggregate::new("mongodb", "inventory", "customers")
        .aggregate_query([
            json!({
                "$group": {
                    "_id": "$_id",
                    "total": {
                        "$sum": "$customer_id",
                    },
                },
            }),
            json!({
                "$match": {
                    "total": {
                        "$gt": 1,
                    },
                },
            }),
            json!({
                "$sort": {
                    "total": -1,
                },
            }),
            json!({
                "$count": "CountValue",
            }),
        ]),
)?;
```

**Java**

**MongoDB Basic aggregation**

```java
var aggregate = g.sys.db.aggregate(new Aggregate("mongodb", "inventory", "customers")
    .aggregateQuery(
        Map.of(
            "$group", Map.of(
                "_id", "$_id",
                "total", Map.of(
                    "$sum", "$customer_id"
                )
            )
        ),
        Map.of(
            "$match", Map.of(
                "total", Map.of(
                    "$gt", 1
                )
            )
        ),
        Map.of(
            "$sort", Map.of(
                "total", -1
            )
        ),
        Map.of(
            "$count", "CountValue"
        )
    ));
```

**Kotlin**

**MongoDB Basic aggregation**

```kotlin
val aggregate = g.sys.db.aggregate(
    Aggregate("mongodb", "inventory", "customers")
        .aggregateQuery(
            mapOf(
                "\$group" to mapOf(
                    "_id" to "\$_id",
                    "total" to mapOf(
                        "\$sum" to "\$customer_id",
                    ),
                ),
            ),
            mapOf(
                "\$match" to mapOf(
                    "total" to mapOf(
                        "\$gt" to 1,
                    ),
                ),
            ),
            mapOf(
                "\$sort" to mapOf(
                    "total" to -1,
                ),
            ),
            mapOf(
                "\$count" to "CountValue",
            ),
        ),
)
```

## Several groupings at once : $facet and $bucket

**TypeScript**

**MongoDB $facet with multiple $bucket**

```typescript
let aggregate = await g.sys.db.aggregate({
    instance: "mongodb", database: "inventory", collection: "customers",
    aggregateQuery:[
        {
            "$facet": {
                "price": [
                    {
                        "$bucket": {
                            "groupBy": "$price",
                            "boundaries": [10000,20000,30000,40000,50000,60000,70000,80000],
                            "default": "Other",
                            "output": {
                                "count": {
                                    "$sum": 1
                                },
                                "product_details": {
                                    "$push": {
                                        "name": "$name",
                                        "price": "$price"
                                    }
                                }
                            }
                        }
                    }
                ],
                "categories": [
                    {
                        "$bucket": {
                            "groupBy": "$category_id",
                            "boundaries": [10000,20000,30000,40000,50000,60000,70000,80000],
                            "default": "Other",
                            "output": {
                                "count": {
                                    "$sum": 1
                                },
                                "product_details": {
                                    "$push": {
                                        "name": "$name",
                                        "id": "$id",
                                        "category_id": "$category_id"
                                    }
                                }
                            }
                        }
                    }
                ]
            }
        }
    ]
});
```

**Python**

**MongoDB $facet with multiple $bucket**

```python
aggregate = await g.sys.db.aggregate(
    'mongodb', 'inventory', 'customers',
    aggregate_query=[
        {
            '$facet': {
                'price': [
                    {
                        '$bucket': {
                            'groupBy': '$price',
                            'boundaries': [10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000],
                            'default': 'Other',
                            'output': {
                                'count': {
                                    '$sum': 1,
                                },
                                'product_details': {
                                    '$push': {
                                        'name': '$name',
                                        'price': '$price',
                                    },
                                },
                            },
                        },
                    },
                ],
                'categories': [
                    {
                        '$bucket': {
                            'groupBy': '$category_id',
                            'boundaries': [10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000],
                            'default': 'Other',
                            'output': {
                                'count': {
                                    '$sum': 1,
                                },
                                'product_details': {
                                    '$push': {
                                        'name': '$name',
                                        'id': '$id',
                                        'category_id': '$category_id',
                                    },
                                },
                            },
                        },
                    },
                ],
            },
        },
    ],
)
```

**PHP**

**MongoDB $facet with multiple $bucket**

```php
$aggregate = $g->sys->db->aggregate(
    'mongodb', 'inventory', 'customers',
    aggregateQuery: [
        [
            '$facet' => [
                'price' => [
                    [
                        '$bucket' => [
                            'groupBy' => '$price',
                            'boundaries' => [10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000],
                            'default' => 'Other',
                            'output' => [
                                'count' => [
                                    '$sum' => 1,
                                ],
                                'product_details' => [
                                    '$push' => [
                                        'name' => '$name',
                                        'price' => '$price',
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],
                'categories' => [
                    [
                        '$bucket' => [
                            'groupBy' => '$category_id',
                            'boundaries' => [10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000],
                            'default' => 'Other',
                            'output' => [
                                'count' => [
                                    '$sum' => 1,
                                ],
                                'product_details' => [
                                    '$push' => [
                                        'name' => '$name',
                                        'id' => '$id',
                                        'category_id' => '$category_id',
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],
            ],
        ],
    ],
);
```

**C#**

**MongoDB $facet with multiple $bucket**

```csharp
var aggregate = await g.Sys.Db.Aggregate(new Aggregate("mongodb", "inventory", "customers")
{
    AggregateQuery = [
        new JsonObject
        {
            ["$facet"] = new JsonObject
            {
                ["price"] = new JsonArray
                {
                    new JsonObject
                    {
                        ["$bucket"] = new JsonObject
                        {
                            ["groupBy"] = "$price",
                            ["boundaries"] = new JsonArray { 10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000 },
                            ["default"] = "Other",
                            ["output"] = new JsonObject
                            {
                                ["count"] = new JsonObject
                                {
                                    ["$sum"] = 1,
                                },
                                ["product_details"] = new JsonObject
                                {
                                    ["$push"] = new JsonObject
                                    {
                                        ["name"] = "$name",
                                        ["price"] = "$price",
                                    },
                                },
                            },
                        },
                    },
                },
                ["categories"] = new JsonArray
                {
                    new JsonObject
                    {
                        ["$bucket"] = new JsonObject
                        {
                            ["groupBy"] = "$category_id",
                            ["boundaries"] = new JsonArray { 10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000 },
                            ["default"] = "Other",
                            ["output"] = new JsonObject
                            {
                                ["count"] = new JsonObject
                                {
                                    ["$sum"] = 1,
                                },
                                ["product_details"] = new JsonObject
                                {
                                    ["$push"] = new JsonObject
                                    {
                                        ["name"] = "$name",
                                        ["id"] = "$id",
                                        ["category_id"] = "$category_id",
                                    },
                                },
                            },
                        },
                    },
                },
            },
        },
    ],
});
```

**Go**

**MongoDB $facet with multiple $bucket**

```go
aggregate, err := g.Sys.Db.Aggregate(am.Aggregate{
	Instance: "mongodb", Database: "inventory", Collection: "customers",
	AggregateQuery: []any{
		am.M{
			"$facet": am.M{
				"price": []any{
					am.M{
						"$bucket": am.M{
							"groupBy":    "$price",
							"boundaries": []any{10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000},
							"default":    "Other",
							"output": am.M{
								"count": am.M{
									"$sum": 1,
								},
								"product_details": am.M{
									"$push": am.M{
										"name":  "$name",
										"price": "$price",
									},
								},
							},
						},
					},
				},
				"categories": []any{
					am.M{
						"$bucket": am.M{
							"groupBy":    "$category_id",
							"boundaries": []any{10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000},
							"default":    "Other",
							"output": am.M{
								"count": am.M{
									"$sum": 1,
								},
								"product_details": am.M{
									"$push": am.M{
										"name":        "$name",
										"id":          "$id",
										"category_id": "$category_id",
									},
								},
							},
						},
					},
				},
			},
		},
	},
})
if err != nil {
	return nil, err
}
```

**Rust**

**MongoDB $facet with multiple $bucket**

```rust
let aggregate = g.sys.db.aggregate(
    Aggregate::new("mongodb", "inventory", "customers")
        .aggregate_query([
            json!({
                "$facet": {
                    "price": [
                        {
                            "$bucket": {
                                "groupBy": "$price",
                                "boundaries": [10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000],
                                "default": "Other",
                                "output": {
                                    "count": {
                                        "$sum": 1,
                                    },
                                    "product_details": {
                                        "$push": {
                                            "name": "$name",
                                            "price": "$price",
                                        },
                                    },
                                },
                            },
                        },
                    ],
                    "categories": [
                        {
                            "$bucket": {
                                "groupBy": "$category_id",
                                "boundaries": [10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000],
                                "default": "Other",
                                "output": {
                                    "count": {
                                        "$sum": 1,
                                    },
                                    "product_details": {
                                        "$push": {
                                            "name": "$name",
                                            "id": "$id",
                                            "category_id": "$category_id",
                                        },
                                    },
                                },
                            },
                        },
                    ],
                },
            }),
        ]),
)?;
```

**Java**

**MongoDB $facet with multiple $bucket**

```java
var aggregate = g.sys.db.aggregate(new Aggregate("mongodb", "inventory", "customers")
    .aggregateQuery(
        Map.of(
            "$facet", Map.of(
                "price", List.of(
                    Map.of(
                        "$bucket", Map.of(
                            "groupBy", "$price",
                            "boundaries", List.of(10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000),
                            "default", "Other",
                            "output", Map.of(
                                "count", Map.of(
                                    "$sum", 1
                                ),
                                "product_details", Map.of(
                                    "$push", Map.of(
                                        "name", "$name",
                                        "price", "$price"
                                    )
                                )
                            )
                        )
                    )
                ),
                "categories", List.of(
                    Map.of(
                        "$bucket", Map.of(
                            "groupBy", "$category_id",
                            "boundaries", List.of(10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000),
                            "default", "Other",
                            "output", Map.of(
                                "count", Map.of(
                                    "$sum", 1
                                ),
                                "product_details", Map.of(
                                    "$push", Map.of(
                                        "name", "$name",
                                        "id", "$id",
                                        "category_id", "$category_id"
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    ));
```

**Kotlin**

**MongoDB $facet with multiple $bucket**

```kotlin
val aggregate = g.sys.db.aggregate(
    Aggregate("mongodb", "inventory", "customers")
        .aggregateQuery(
            mapOf(
                "\$facet" to mapOf(
                    "price" to listOf(
                        mapOf(
                            "\$bucket" to mapOf(
                                "groupBy" to "\$price",
                                "boundaries" to listOf(10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000),
                                "default" to "Other",
                                "output" to mapOf(
                                    "count" to mapOf(
                                        "\$sum" to 1,
                                    ),
                                    "product_details" to mapOf(
                                        "\$push" to mapOf(
                                            "name" to "\$name",
                                            "price" to "\$price",
                                        ),
                                    ),
                                ),
                            ),
                        ),
                    ),
                    "categories" to listOf(
                        mapOf(
                            "\$bucket" to mapOf(
                                "groupBy" to "\$category_id",
                                "boundaries" to listOf(10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000),
                                "default" to "Other",
                                "output" to mapOf(
                                    "count" to mapOf(
                                        "\$sum" to 1,
                                    ),
                                    "product_details" to mapOf(
                                        "\$push" to mapOf(
                                            "name" to "\$name",
                                            "id" to "\$id",
                                            "category_id" to "\$category_id",
                                        ),
                                    ),
                                ),
                            ),
                        ),
                    ),
                ),
            ),
        ),
)
```

## Join another collection : $lookup

**TypeScript**

**MongoDB $lookup**

```typescript
let aggregate = await g.sys.db.aggregate({
    instance: "mongodb", database: "inventory", collection: "customers",
    aggregateQuery:[
        {
            "$lookup": {
                "from": "products",
                "localField": "customer_id",
                "foreignField": "owner_id",
                "as": "customer_data"
            }
        }
    ]
});
```

**Python**

**MongoDB $lookup**

```python
aggregate = await g.sys.db.aggregate(
    'mongodb', 'inventory', 'customers',
    aggregate_query=[
        {
            '$lookup': {
                'from': 'products',
                'localField': 'customer_id',
                'foreignField': 'owner_id',
                'as': 'customer_data',
            },
        },
    ],
)
```

**PHP**

**MongoDB $lookup**

```php
$aggregate = $g->sys->db->aggregate(
    'mongodb', 'inventory', 'customers',
    aggregateQuery: [
        [
            '$lookup' => [
                'from' => 'products',
                'localField' => 'customer_id',
                'foreignField' => 'owner_id',
                'as' => 'customer_data',
            ],
        ],
    ],
);
```

**C#**

**MongoDB $lookup**

```csharp
var aggregate = await g.Sys.Db.Aggregate(new Aggregate("mongodb", "inventory", "customers")
{
    AggregateQuery = [
        new JsonObject
        {
            ["$lookup"] = new JsonObject
            {
                ["from"] = "products",
                ["localField"] = "customer_id",
                ["foreignField"] = "owner_id",
                ["as"] = "customer_data",
            },
        },
    ],
});
```

**Go**

**MongoDB $lookup**

```go
aggregate, err := g.Sys.Db.Aggregate(am.Aggregate{
	Instance: "mongodb", Database: "inventory", Collection: "customers",
	AggregateQuery: []any{
		am.M{
			"$lookup": am.M{
				"from":         "products",
				"localField":   "customer_id",
				"foreignField": "owner_id",
				"as":           "customer_data",
			},
		},
	},
})
if err != nil {
	return nil, err
}
```

**Rust**

**MongoDB $lookup**

```rust
let aggregate = g.sys.db.aggregate(
    Aggregate::new("mongodb", "inventory", "customers")
        .aggregate_query([
            json!({
                "$lookup": {
                    "from": "products",
                    "localField": "customer_id",
                    "foreignField": "owner_id",
                    "as": "customer_data",
                },
            }),
        ]),
)?;
```

**Java**

**MongoDB $lookup**

```java
var aggregate = g.sys.db.aggregate(new Aggregate("mongodb", "inventory", "customers")
    .aggregateQuery(
        Map.of(
            "$lookup", Map.of(
                "from", "products",
                "localField", "customer_id",
                "foreignField", "owner_id",
                "as", "customer_data"
            )
        )
    ));
```

**Kotlin**

**MongoDB $lookup**

```kotlin
val aggregate = g.sys.db.aggregate(
    Aggregate("mongodb", "inventory", "customers")
        .aggregateQuery(
            mapOf(
                "\$lookup" to mapOf(
                    "from" to "products",
                    "localField" to "customer_id",
                    "foreignField" to "owner_id",
                    "as" to "customer_data",
                ),
            ),
        ),
)
```

## Multi-tenant
- `instance: "crm::acme"` works on the database of the tenant `acme` of the [multi-tenant](https://docs.apimaker.dev/v4/docs/features/multi-tenant.html) instance `crm`.
- With `instance: "crm"`, the call works on the database of the tenant of the request: a custom API called with the [x-am-tenant-username](https://docs.apimaker.dev/v4/docs/apis-all/header/requestHeader.html#x-am-tenant-username) header passes it on to the APIs it calls. A request without a tenant works on the structure database.

**TypeScript**

**Aggregate the customers of the tenant acme (MongoDB)**

```typescript
let aggregate = await g.sys.db.aggregate({
    instance: "crm::acme", database: "crm", collection: "customers",
    aggregateQuery: [{
        "$group": {
            "_id": "$city",
            "customers": { "$sum": 1 }
        }
    }]
});
```

**Python**

**Aggregate the customers of the tenant acme (MongoDB)**

```python
aggregate = await g.sys.db.aggregate(
    'crm::acme', 'crm', 'customers',
    aggregate_query=[
        {
            '$group': {
                '_id': '$city',
                'customers': {'$sum': 1},
            },
        },
    ],
)
```

**PHP**

**Aggregate the customers of the tenant acme (MongoDB)**

```php
$aggregate = $g->sys->db->aggregate(
    'crm::acme', 'crm', 'customers',
    aggregateQuery: [
        [
            '$group' => [
                '_id' => '$city',
                'customers' => ['$sum' => 1],
            ],
        ],
    ],
);
```

**C#**

**Aggregate the customers of the tenant acme (MongoDB)**

```csharp
var aggregate = await g.Sys.Db.Aggregate(new Aggregate("crm::acme", "crm", "customers")
{
    AggregateQuery = [
        new JsonObject
        {
            ["$group"] = new JsonObject
            {
                ["_id"] = "$city",
                ["customers"] = new JsonObject { ["$sum"] = 1 },
            },
        },
    ],
});
```

**Go**

**Aggregate the customers of the tenant acme (MongoDB)**

```go
aggregate, err := g.Sys.Db.Aggregate(am.Aggregate{
	Instance: "crm::acme", Database: "crm", Collection: "customers",
	AggregateQuery: []any{
		am.M{
			"$group": am.M{
				"_id":       "$city",
				"customers": am.M{"$sum": 1},
			},
		},
	},
})
if err != nil {
	return nil, err
}
```

**Rust**

**Aggregate the customers of the tenant acme (MongoDB)**

```rust
let aggregate = g.sys.db.aggregate(
    Aggregate::new("crm::acme", "crm", "customers")
        .aggregate_query([
            json!({
                "$group": {
                    "_id": "$city",
                    "customers": { "$sum": 1 },
                },
            }),
        ]),
)?;
```

**Java**

**Aggregate the customers of the tenant acme (MongoDB)**

```java
var aggregate = g.sys.db.aggregate(new Aggregate("crm::acme", "crm", "customers")
    .aggregateQuery(
        Map.of(
            "$group", Map.of(
                "_id", "$city",
                "customers", Map.of("$sum", 1)
            )
        )
    ));
```

**Kotlin**

**Aggregate the customers of the tenant acme (MongoDB)**

```kotlin
val aggregate = g.sys.db.aggregate(
    Aggregate("crm::acme", "crm", "customers")
        .aggregateQuery(
            mapOf(
                "\$group" to mapOf(
                    "_id" to "\$city",
                    "customers" to mapOf("\$sum" to 1),
                ),
            ),
        ),
)
```

## Without a schema

`g.sys.db.gen.aggregateGen` takes the same arguments and calls the [aggregate generated API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-aggregate-api.html#without-a-schema) : no schema, so the data is read and written as it is in the table, without the conversions, validations and defaults of the schema.

| Language | The call |
|---|---|
| TypeScript | `g.sys.db.gen.aggregateGen(…)` |
| Python | `g.sys.db.gen.aggregate_gen(…)` |
| PHP | `$g->sys->db->gen->aggregateGen(…)` |
| C#, Go | `g.Sys.Db.Gen.AggregateGen(…)` |
| Rust | `g.sys.db.aggregate_gen(Aggregate::new(…))` |
| Java, Kotlin | `g.sys.db.gen.aggregateGen(…)` |

## Related

- [Aggregate API](https://docs.apimaker.dev/v4/docs/apis-all/schema-apis/auto-generated-schema-based-aggregate-api.html) · [Generated API from code](https://docs.apimaker.dev/v4/examples/sys/db/aggregate.html#without-a-schema) · [The global object g](https://docs.apimaker.dev/v4/docs/pre-defined-terms/global-object-g.html)