fbpx

Group by in Bubble.io: how to aggregate data

Follow me:

During coaching sessions I’ve found that the Group by and Aggregate features in Bubble.io are both somewhat overlooked, and often misunderstood. I’m not really surprised – Bubble’s documentation is not inaccurate, but the feature itself is different  from the visual way we’re used to set up stuff in Bubble. In some ways both the setup and the result behaves more like code, and it can be tricky to wrap your head around what exactly the result is.

In this article, we’ll look into what the Group by feature is for, and in this case, what it’s not for – ‘cause that’s where the misunderstandings come in.

What the Group by feature in Bubble is for

In short, the Group by feature is for producing a table of aggregated data from your database. It can perform calculations based on a given set of constraints, such as the total sum of sales in a given month or city. The result is a list of data points that you can then present in a repeating group or in a graph.

Let’s set up a quick example: your company has a CRM set up where you keep a list of clients in the US. You would like to know how many clients you have per city. There are three ways you could solve this:

  1. Set up a repeating group of all cities, and then perform a search within each row where you count all the clients in that city
  2. Save the number of clients as a separate field on the city data type
  3. Use the Group by feature to return a list of all cities along with the number of clients in each city

The first option would be the less performant one: each city would require a separate database search that may slow down the page significantly as the number grows. The second option would be faster to load, but require more upkeep. You would have to count the number of clients in each city every time one is created, deleted or moved by using backend triggers or some other method.

The third option is both performant and requires no upkeep, but comes with its own set of pros and cons.

Setting up a Group by aggregation in Bubble.io

Many assume that the Group by feature is used to set up multiple lists that are grouped by some given condition, such as listing all the Companies in each city. Wait, wasn’t that what you just said it was for?

Yes and no: yes, we can separate Companies by City, but what we get back is not a list of Companies – it’s an array of aggregated data such as the number of Companies per city.

What can be confusing about the Group by feature is that it only returns the database records by which it is grouped. Returning to our CRM example, if you set up the data source of a Repeating Group to contain a Group by expression, you will see that the Type of content changes to Grouping. 

From then on we can fetch the data stored on the City (such as the city’s name), but we can’t get any data on the Company, since this is aggregated. In other words, what we get back is a calculation that we specify, such as the number of Companies in each city, but we don’t get the actual list of cities.

First, let’s set up what a Group by expression looks like.

xryEsqX04zFu hyX8hPQ3usTpi1K6Gl51wVVtXbtEKmdKuND5I7MbHFgKgnZRkkrewHAN fWt0p1fGlVK5 ykhf8b5 zraoaiEn0YKNDKJ ibLgOEynYb7r5QL5Pl br4ADU UnRTYT ERIDQg

In this search, we’re looking for Companies, and then choosing to group those Companies by City. As you can see in the bottom rectangle, we’re setting up an aggregation of the Count. This means that what we’ll get back is a list of Cities with the aggregated total number of Companies in each city represented by a number. In a repeating group, the result would look like this:

8GFKlDKOySUrZp927RdxxYfmG6B7OMM3koSGJGGg5SBEHLFkbL6GIz90eNKX08gHv1Becgo14Nxy7hD9pPv jyz4qh2 UW9kHABE18Xl5bFZrzgLtmiiq zgKMhuPedsjACBvI20zsA MV803A

Another typical use case is to set up charts, as illustrated below:

47MS tGXKixd68sm3KuEu6nza0x8jRIuYtYUkV oCJDLNFY11aLr0EmWxjxyMgTotrEnjV404FYF3oMoIAv6z1FuPCJ89vTW7fXLzyQT9yTb63KG
Group by makes it very easy to set up fast-loading graphs.

To understand how this is all structured, it’s helpful to have a look at our old friend Chrome Developer Tools and use the Network tab to investigate what kind of data the Bubble server is actually sending to our browser. Let’s have a look:

Checking the DevTools result from Group By in Bubble.io
  1. First, we navigate to the Network tab…
  2. … and then filter the network activity by Fetch/XHR, which lets us isolate the network activity related to the database
  3. The Group by query is named group_by. As you can see from the highlighted tooltip, the Group by feature uses the Elasticsearch API, just like other Bubble database queries.
  4. Clicking the Preview tab, we can see the actual data being sent.

Each of the numbered rows in this JSON-code represents one Grouping. The Grouping in our case is a City, since we specified that we want to group by City. You can see the Cities represented by their Unique ID and then the aggregated data (number of companies) preceded by agg0 and then the numerical value. What can we learn from this?

The first important point is that we only get actual database data for each Grouping (City) and not for the actual Companies. Technically, the browser is still oblivious to what Companies we’re working with, as it only knows the total number of Companies in each city. When you think about it, this makes sense: each row represents multiple companies (aggregated with a count) and not a single one. In other words, we can’t reference a specific Company, since all we’re looking at is a number.

A second point is that each City does not then send a list of those companies. The only thing the Group by operator can do is to group and aggregate data. If you’re looking to list all Companies in a given city, you will have to resort to using a nested Repeating Group 

Nested Repeating Group in Bubble
Listing the Companies in one city would require nested repeating groups – this is not what the Group by feature is for.

Group by multiple groupings

As you can see in the inspector window for the Group by feature, you can set up more than one Grouping in each search expression. This will split the search further into every possible combination of groupings and allow you to again aggregate based on each grouping combination.

Let’s expand our example from earlier and introduce a Category to each company: Finance and Health Care. By adding the Category as a second Grouping, Bubble generates a list of all possible combinations. The expression would then look like this:

Multiple Groupings in Group by in Bubble
Setting up a second grouping tells Bubble to create one row per possible combination of groupings.

Visualizing this in a Repeating Group, we can see that Bubble now generates one row of each possible combination, like New York – Finance and Los Angeles – Health Care. This way, you can dig further into the data, but still rely on a single search expression and not a complex combination of nested repeating groups and searches:

List showing the city and category as well as aggregated count of companies

Having another look in DevTools to see the data, we can see that Bubble now returns two lookups (the City (grouping0) and Category (grouping1)), represented by their Unique ID’s. You can also see the aggregated number 2, which is the total number of Companies in New York under the Finance Category:

DevTools screenshot showing multiple groupings
DevTools tells us that Bubble is returning two database records per row (City and Category) as well as the aggregated count found in agg0.

Different kinds of aggregations

So far we’ve simply counted the number of Companies. This doesn’t refer to any specific field, but simply counts all the records saved in the database that matches the constraint (City/Category).

This is where it gets interesting: we can also perform calculations on fields saved on that Thing in the database. Let’s say for instance that we’re no longer looking at Companies, but on Orders delivered to a specific city. In this case we want to know the total sum of orders to one City, and to do that we use the aggregate function to calculate the total sum from a given field.

Aggregating sum in Bubble.io
Picking a number fields lets you make different calculations, such as the total sum of sales in a given City.

Highlighted in red you can see the Aggregation is set to Sum. Bubble then asks which field we would like to apply the calculation to. In this case we’re going with the price.

List of cities with aggregated sales in Bubble.io

The result in a repeating group is as above: each City has a row with the aggregated sum of sales. Sum is just one of the calculations we can perform. In the Group by expression we can specify other kinds:

The different aggregations available in Bubble
Further exploring different aggregations, we can return averages, medians, min/max and other calculated values.

Conclusion

As we’ve seen, the Group by feature is used to create tables of aggregated data with a single search expression through the Elasticsearch API. Since we don’t have to resort to adding nested searches within the cell of a repeating group, we get a result that performs very well and that can perform calculations on any field we specify, like generating a sales report for a specific area or product.

Support the site and keep it free ❤️

I love tech startups and the Bubble community, and have made it my mission to try and create content that’s valuable, easy to follow and entertaining.

Creating content next to full-time consulting work is time-consuming; if you’d like to support it and keep the site free for everyone, please consider buying me a coffee or becoming a supporting member.

Buy Me A Coffee

Follow me:
Bubble.io books

Learn Bubble the right way

Our professional Bubble books teach you how to plan, structure and build your applications right from the start.

5-star review stars

More Posts

2 Comments


Joshua Lasker
June 28, 2022 at 18:18
Reply

Hi Petter – Great article. Thanks! Is the output list of :group by always sorted by the creation date of the “field 1 to group by ” so those created first will be the first list item? I am trying to feed grouped data into a series on a chart js bar chart but the list order has to be sorted the same as the x axis values so that they correlate properly.

Thanks!


    Petter Amlie
    June 29, 2022 at 20:17
    Reply

    Hey Joshua! Thanks for the comment, and good question. It seems that Creation date is the default, but you can apply a :sorted to the expression to sort the groupings thereafter. Does that answer your question? I may have misunderstood what you’re trying to do here.

    Sort group by in Bubble

Leave A Reply

Your email address will not be published.

*

Email icon

Useful articles and tips

Join the mailing list to get guides, opinions and articles on Bubble, no-code, automation and the tech industry.

We don't share your email address with anyone, and you can unsubscribe at any time.

You have successfully subscribed