How to create a GROUP BY with map reduce index

Do you want to know how to GROUP BY in RavenDB? This one is for you then! In this article I’m going to show you how to perform a group equivalent to the GROUP BY in SQL but in the noSQL RavenDB offering. I really like RavenDB and that’s why I keep writing articles about it, it’s just so versatile, fast and easy to work with that I would start any new project with it.

For this tutorial I’m going to use as source code the RavenNest app that I already created for the series and the code for this tutorial is located in the tutorial5-group-by branch.

The test data

Like the previous articles, we are going to use some sample data which contains info about some movies:

For convenience, you can find the database dump file right here in my repo.

Creating the Javascript index

Our first task would be the Javascript index creation. In all these tutorials, I used a code-first approach which I advice you to follow accordingly in order to make sure the database is kept in a consistent state. This index will be a bit similar to the one in the previous article with minor modifications:

export class MovieGroupByYearMap {
  public year: number;
  public movies: MovieEntity[];
}

export class MovieGroupByYearIndex extends AbstractJavaScriptIndexCreationTask<
  MovieEntity,
  MovieGroupByYearMap
> {
  constructor() {
    super();

    this.map(new MovieEntity().collectionName, (doc) => {
      return {
        year: doc.year,
        movies: [doc],
      };
    });

    this.reduce((res) => {
      return res
        .groupBy((x) => x.year)
        .aggregate((g) => ({
          year: g.key,
          movies: g.values.reduce((movies, val) => [...movies, ...val.movies], []),
        }));
    });
  }
}

In the code above, we simply declare the map type that the index will return which contains the year and the movies array and also the index which actually gathers and reduces the data. Once we add it to the PersistenceService to be executed, the index should be shown in the RavenDB Studio:

Here you go, now the RavenDB is aware of this new index which can be queried at any time.

Adding to the movie repository

Our job is not yet complete, for we need to create the function inside the movie repo which will allow us to programmatically query this index. So inside our movie.repo.ts file we need to add this function:

public async retrieveMoviesGroupByYear(): Promise<MovieGroupByYearMap[]> {
  const session = this.documentStore.openSession();

  const query = session.advanced
    .rawQuery<MovieGroupByYearMap>(
      `from index '${MovieGroupByYearIndex.name}'`,
    )
    .projection('FromIndex');

  const results = await query.all();

  session.dispose();

  // To remove @metadata which is unnecessary
  return results.map(this.metadataRemove);
}

After this is done, your next job is to add an endpoint to test it out:

@Get('by/yearMovies')
async getMoviesGroupByYear() {
  return await this.movieRepo.retrieveMoviesGroupByYear();
}

Testing it out

Now that we have everything in place, time to hit a Postman request and see what data comes back from the RavenDB map reduce index:

You can now observe that the endpoint returns the data grouped by year as expected 😉

Conclusion

So, once you are a bit familiar with how indexes in RavenDB work, you can pretty much use them to your advantage. Many business requirements in your app can be successfully implemented due to the index definition flexibility and reliability. In our case, we can observe that grouping by year it’s very easy to achieve. Just now I was wondering if it’s possible to group by tag as well, this could prove more challengeful, so let’s see, perhaps I will also deal with this one!

Cool, hope you found this article useful, please let me know in a comment or via contact form if you have any suggestions! Cheers!

Thanks for reading, I hope you found this article useful and interesting. If you have any suggestions don’t hesitate to contact me. If you found my content useful please consider a small donation. Any support is greatly appreciated! Cheers  😉