MongoDB how to subtract two sums?

Hi there!

I’d love to create a question in metabase using MongoDB as backend. I do have a document with the following structure:

{ "change": { "added": 5, "removed": 2 } }

Now like to do SUM(added) - SUM(removed), but I can’t figure out how since I’m not able to combine the two expressions in any way :frowning:

Hi, have you tried creating a custom field (at the right of the query builder):

Does that work?

I think this is exactly what I need. Unfortunately I do not have a clue how to get there. This is how it looks on my end - running Metabase v0.27.2

oh - I use an SQL Database, that might be the reason that you don´t have the functionality there? (and I use v0.25 but I doubt they would change that in v0.27).

Can you build questions using SQL syntax rather than the query builder?

Again, not sure if that is possible if you use MongoDB. But using SQL syntax you can calculate fields quite easily.
Hope you figure it out!!

Mhm… seems like I do not have another choice. I’m able to switch to “SQL”, but the generated code is a bunch of JSON. Seems like I have to adhere to the weird (at least for me) MongoDB query syntax:

sorry for that :frowning:

The custom expressions editor has been folded into the main “view” or aggregation selection in the query builder. See below –

@sameer for some reason I do not have a “Custom Expression” Editor. See screenshot below. Metabase v0.27.2, MongoDB v2.6.10.

I can confirm that Custom Fields are not available on MongoDB. Using version 0.28.6 of Metabase.

This would be indeed very much needed functionality, is there existing feature requests for this?

1 Like

In fact you can add column using raw aggregation pipeline. If you click on “View the native query” you’ll see an aggregation pipeline. In any $project stage you can add your column like that:

{$project: {

customColumn: {$subtract: ["$column1", “$column2”]}

I’d say it’s extremely cumbersome but it’s how MongoDB works