Version: 1.0 (Latest)

Aggregating functions

The aggregating functions MIN, MAX, SUM, AVG, and COUNT allow you to compute operations on groups of documents, such as the average rating of a group of books, or counting how many books are in each group. You can also use them at the root level and compute such operations on the whole result set.

Display database setup

To reproduce the example results from this page, your database needs the following setup.

Database schema
type Book {
  title: String!
  genre: String
  plot: String
  rating: Float
  ratings: [Float]
  author: Person
  seller: Company
}

type Person {
  name: String!
  authoredBooks: [Book]
}

type Company {
  name: String!
  sells: [Book]
}

Documents setup
mutation {
  a1:add_Person(input: {
    name: "George Orwell"
  }) { _docID name }
  a2:add_Person(input: {
    name: "William Golding"
  }) { _docID name }
  a3:add_Person(input: {
    name: "David Foster Wallace"
  }) { _docID name }
  a4:add_Person(input: {
    name: "Victor Hugo"
  }) { _docID name }

  c1:add_Company(input: {
    name: "The Independent Hipster Bookshop"
  }) { _docID name }
  c2:add_Company(input: {
    name: "The World-Destroying Large Chain"
  }) { _docID name }

  b11:add_Book(input: {
    title: "1984",
    genre: "Fiction",
    plot: "A masterpiece of rebellion and imprisonment where war is peace, freedom is slavery, and Big Brother is watching.",
    rating: 4.20,
    ratings: [3.8, 4.91, 3.1, 2.8],
    _authorID: "bae-bc532931-4843-50bc-bbdd-3e31549c8cc6",
    _sellerID: "bae-a5300933-fb0a-5b8f-b38e-202565993ff0"
  }) { _docID title }
  b12:add_Book(input: {
    title: "Down and Out in Paris and London",
    genre: "Memoir",
    plot: "The adventures of a penniless British writer among the down-and-outs of two great cities.",
    rating: 4.09,
    _authorID: "bae-bc532931-4843-50bc-bbdd-3e31549c8cc6",
    _sellerID: "bae-a5300933-fb0a-5b8f-b38e-202565993ff0"
  }) { _docID title }
  b21:add_Book(input: {
    title: "Lord of the Flies",
    genre: "Fiction",
    plot: "At the dawn of the next world war, a plane crashes on an uncharted island, stranding a group of schoolboys.",
    rating: 3.70,
    _authorID: "bae-6025af65-e57e-5db5-84dd-d349b130c6d9",
    _sellerID: "bae-a5300933-fb0a-5b8f-b38e-202565993ff0"
  }) { _docID title }
  b31:add_Book(input: {
    title: "Infinite Jest",
    genre: "Fiction",
    plot: "A gargantuan, mind-altering tragi-comedy about the Pursuit of Happiness in America.",
    rating: 4.25,
    ratings: [3.1, 4.1, 4.5],
    _authorID: "bae-26c791a7-fa81-5d86-95c5-4119e2fef915",
    _sellerID: "bae-81d5fadb-c2a3-5d95-b235-a220c220bf79"
  }) { _docID title }
  b32:add_Book(input: {
    title: "Consider the Lobster and Other Essays",
    genre: "Nonfiction",
    plot: "Do lobsters feel pain? Did Franz Kafka have a funny bone? What is John Updike's deal, anyway? And what happens when adult video starlets meet their fans in person? Essays that are also enthralling narrative adventures.",
    rating: 4.18,
    _authorID: "bae-26c791a7-fa81-5d86-95c5-4119e2fef915",
    _sellerID: "bae-81d5fadb-c2a3-5d95-b235-a220c220bf79"
  }) { _docID title }
  b33:add_Book(input: {
    title: "Girl with Curious Hair",
    genre: "Fiction",
    plot: "Remarkable and unsettling reimaginations of reality.",
    rating: 3.85,
    _authorID: "bae-26c791a7-fa81-5d86-95c5-4119e2fef915",
    _sellerID: "bae-81d5fadb-c2a3-5d95-b235-a220c220bf79"
  }) { _docID title }
  b41:add_Book(input: {
    title: "Les Misérables",
    genre: "Fiction",
    plot: "Victor Hugo's tale of injustice, heroism and love follows the fortunes of Jean Valjean, an escaped convict determined to put his criminal past behind him.",
    rating: 4.21,
    ratings: [3.9, 4.1],
    _authorID: "bae-4bfe5f4c-d668-5dc3-9de2-eb598af3da7d",
    _sellerID: "bae-81d5fadb-c2a3-5d95-b235-a220c220bf79"
  }) { _docID title }
}

Syntax

MIN
MAX
SUM
AVG
COUNT

Syntax – MIN
MIN(GROUP: { 
  field: String, 
  filter: Object,
  limit: Int, offset: Int
})

field – Field on which to run the aggregating function.
filter – Restrict which documents are included, see Filter documents.
limit, offset – Restrict how many documents are included, see Limit and paginate results.

Syntax – MAX
MAX(GROUP: { 
  field: String, 
  filter: Object,
  limit: Int, offset: Int
})

field – Field on which to run the aggregating function.
filter – Restrict which documents are included, see Filter documents.
limit, offset – Restrict how many documents are included, see Limit and paginate results.

Syntax – SUM
SUM(GROUP: { 
  field: String, 
  filter: Object,
  limit: Int, offset: Int
})

field – Field on which to run the aggregating function.
filter – Restrict which documents are included, see Filter documents.
limit, offset – Restrict how many documents are included, see Limit and paginate results.

Syntax – AVG
AVG(GROUP: { 
  field: String, 
  filter: Object,
  limit: Int, offset: Int
})

field – Field on which to run the aggregating function.
filter – Restrict which documents are included, see Filter documents.
limit, offset – Restrict how many documents are included, see Limit and paginate results.

Syntax – COUNT
COUNT(GROUP: { 
  filter: Object,
  limit: Int, offset: Int
})

filter – Restrict which documents are included, see Filter documents.
limit, offset – Restrict how many documents are included, see Limit and paginate results.

Usage with groups

When grouping results, aggregating functions take as input the documents belonging to each group and produce a result for each group. It is optional to include the GROUP sub-object in the return fields.

Compute average rating for each book genre
{
  Book(groupBy: [genre]) {
    genre
    AVG(GROUP: { field: rating })
    GROUP {  # optional
      title
      rating
    }
  }
}

Result
{
  "data": {
    "Book": [
      {
        "AVG": 4.042,
        "GROUP": [
          {
            "rating": 4.2,
            "title": "1984"
          },
          {
            "rating": 3.7,
            "title": "Lord of the Flies"
          },
          {
            "rating": 4.25,
            "title": "Infinite Jest"
          },
          {
            "rating": 3.85,
            "title": "Girl with Curious Hair"
          },
          {
            "rating": 4.21,
            "title": "Les Misérables"
          }
        ],
        "genre": "Fiction"
      },
      {
        "AVG": 4.09,
        "GROUP": [
          {
            "rating": 4.09,
            "title": "Down and Out in Paris and London"
          }
        ],
        "genre": "Memoir"
      },
      {
        "AVG": 4.18,
        "GROUP": [
          {
            "rating": 4.18,
            "title": "Consider the Lobster and Other Essays"
          }
        ],
        "genre": "Nonfiction"
      }
    ]
  }
}

Count how many documents are in each genre
{
  Book(groupBy: [genre]) {
    genre
    COUNT(GROUP: {})
    GROUP {
      title
    }
  }
}

Result
{
  "data": {
    "Book": [
      {
        "COUNT": 5,
        "GROUP": [
          {
            "title": "1984"
          },
          {
            "title": "Lord of the Flies"
          },
          {
            "title": "Infinite Jest"
          },
          {
            "title": "Girl with Curious Hair"
          },
          {
            "title": "Les Misérables"
          }
        ],
        "genre": "Fiction"
      },
      {
        "COUNT": 1,
        "GROUP": [
          {
            "title": "Down and Out in Paris and London"
          }
        ],
        "genre": "Memoir"
      },
      {
        "COUNT": 1,
        "GROUP": [
          {
            "title": "Consider the Lobster and Other Essays"
          }
        ],
        "genre": "Nonfiction"
      }
    ]
  }
}

Filter documents

The filter object in the aggregating function only affects which documents are used to compute the function; it does not control which documents the query returns. To affect which documents are returned, place the filter at the root or group level.

Note how the following example result differs from the previous one only in the AVG value for Fiction; the documents returned for the group still include ratings lower than 4.

Compute average rating for each book genre, excluding ratings lower than 4
{
  Book(groupBy: [genre]) {
    genre
    AVG(GROUP: { field: rating, filter: { rating: { _geq: 4 } } })
    GROUP {
      title
      rating
    }
  }
}

Result
{
  "data": {
    "Book": [
      {
        "AVG": 4.22,
        "GROUP": [
          {
            "rating": 4.2,
            "title": "1984"
          },
          {
            "rating": 3.7,
            "title": "Lord of the Flies"
          },
          {
            "rating": 4.25,
            "title": "Infinite Jest"
          },
          {
            "rating": 3.85,
            "title": "Girl with Curious Hair"
          },
          {
            "rating": 4.21,
            "title": "Les Misérables"
          }
        ],
        "genre": "Fiction"
      },
      {
        "AVG": 4.09,
        "GROUP": [
          {
            "rating": 4.09,
            "title": "Down and Out in Paris and London"
          }
        ],
        "genre": "Memoir"
      },
      {
        "AVG": 4.18,
        "GROUP": [
          {
            "rating": 4.18,
            "title": "Consider the Lobster and Other Essays"
          }
        ],
        "genre": "Nonfiction"
      }
    ]
  }
}

Multiple fields

When grouping on multiple fields, you can run an aggregating function inside a GROUP sub-object, and use its output as input to another, higher-level aggregating function:

Compute the maximum of group averages
{
  Book(groupBy: [genre]) {
    genre
    author { name }
    MAX(GROUP: { field: AVG })
    GROUP (groupBy: [author]) {
      author { name }
      AVG(GROUP: { field: rating })
      GROUP {
        title
        rating
      }
    }
  }
}

Result
{
  "data": {
    "Book": [
      {
        "GROUP": [
          {
            "AVG": 4.2,
            "GROUP": [
              {
                "rating": 4.2,
                "title": "1984"
              }
            ],
            "author": {
              "name": "George Orwell"
            }
          },
          {
            "AVG": 3.7,
            "GROUP": [
              {
                "rating": 3.7,
                "title": "Lord of the Flies"
              }
            ],
            "author": {
              "name": "William Golding"
            }
          },
          {
            "AVG": 4.05,
            "GROUP": [
              {
                "rating": 4.25,
                "title": "Infinite Jest"
              },
              {
                "rating": 3.85,
                "title": "Girl with Curious Hair"
              }
            ],
            "author": {
              "name": "David Foster Wallace"
            }
          },
          {
            "AVG": 4.21,
            "GROUP": [
              {
                "rating": 4.21,
                "title": "Les Misérables"
              }
            ],
            "author": {
              "name": "Victor Hugo"
            }
          }
        ],
        "MAX": 4.21,
        "author": {
          "name": "Victor Hugo"
        },
        "genre": "Fiction"
      },
      {
        "GROUP": [
          {
            "AVG": 4.09,
            "GROUP": [
              {
                "rating": 4.09,
                "title": "Down and Out in Paris and London"
              }
            ],
            "author": {
              "name": "George Orwell"
            }
          }
        ],
        "MAX": 4.09,
        "author": {
          "name": "George Orwell"
        },
        "genre": "Memoir"
      },
      {
        "GROUP": [
          {
            "AVG": 4.18,
            "GROUP": [
              {
                "rating": 4.18,
                "title": "Consider the Lobster and Other Essays"
              }
            ],
            "author": {
              "name": "David Foster Wallace"
            }
          }
        ],
        "MAX": 4.18,
        "author": {
          "name": "David Foster Wallace"
        },
        "genre": "Nonfiction"
      }
    ]
  }
}

Usage at root level

You can provide the whole result set to an aggregating functions, and have its result as the only return field.

Compute average rating of all books rated above 1
{
  AVG(Book: {
    field: rating,
    filter: { rating: { _geq: 1 } }
  })
}

Result
{
  "data": {
    "AVG": 4.0685714285714285
  }
}

Count how many books are rated 4 or higher
{
  COUNT(Book: {
    filter: { rating: { _geq: 4 } }
  })
}

Result
{
  "data": {
    "COUNT": 5
  }
}

Syntax​

Usage with groups​

Filter documents​

Multiple fields​

Usage at root level​

Syntax

Usage with groups

Filter documents

Multiple fields

Usage at root level