Aggregate Queries NReco.GraphQL documentation

Get NReco.GraphQL

Aggregate functions allow you to perform a calculation on a set of values and get a single value. You can configure and set up aggregation functions that are supported in your SQL database.

Aggregate functions configuration

To use aggregate functions you need to configure templates for the possible aggregate functions with AggregationFunctions section in the JSON configuration:

"AggregationFunctions": [
	{
		"Name": "sum",
		"FieldTemplate": "aggregate_{0}_sum",
		"SqlTemplate": "sum({0})"
	},
	{
		"Name": "min",
		"FieldTemplate": "aggregate_{0}_min",
		"SqlTemplate": "min({0})"
	},
	{
		"Name": "max",
		"FieldTemplate": "aggregate_{0}_max",
		"SqlTemplate": "max({0})"
	},
	{
		"Name": "avg",
		"FieldTemplate": "aggregate_{0}_avg",
		"SqlTemplate": "avg({0})"
	},
	{
		"Name": "count",
		"FieldTemplate": "aggregate_count",
		"SqlTemplate": "count"
	}
]

Property	Purpose
Name	Contains the name of an aggregated function (sum, min, max, avg, or count).
FieldTemplate	Used to set up aggregation field template, how it will be represented in graphql query, where {0} is the field's name. You may change it, but {0} shouldn't be removed.
SqlTemplate	a template that is used on composing aggregation sql query (in most of the cases - default configuration is used)

Setup special 'aggregate' fields

Since we've already set templates of aggregate functions, now we can mark those fields that should have the aggregate option by setting property AggregationFunctions (an array of string; possible values - name of configured aggregate functions OR just one value "all", which means 'all configured aggregate functions').

{
  "SingleName": "order",
  "ListName": "orders",
  "Table": "Orders",
  "Fields": [
	{
		"Name": "orderId",
		"Column": "OrderID",
		"DataType": "int32"
	},
	{
		"Name": "customerId",
		"Column": "CustomerID",
		"DataType": "string",
		"AggregationFunctions": ["count"]
	},
	{
		"Name": "OrderDate",
		"DataType": "datetime"
	},
	{
		"Name": "Total",
		"DataType": "decimal",
		"AggregationFunctions": ["sum", "min"]
	}
  ]
}

How use aggregate fields in the query

Graphql query:

																					
query {
	orders {
		aggregate_count
		customerId
	}
}

Response:

{
	"data": {
		"orders": [
			{
				"aggregate_count": 6,
				"customerId": "ALFKI"
			},
			{
				"aggregate_count": 18,
				"customerId": "BERGS"
			}
		]
	}
}