Hello,
I’m using Metabase (dashboarding tool), that allows to write queries in MongoDB language. I have a collection containing a locality, a year (in number format) and an amount. I wrote a query that accepts an input variable for the year. Based on that input variable, the query first applies a filter and selects the documents matching the year provided or the previous year (so if I provide 2023 in input, it selects the documents for which the year is equal to 2023 or 2022). Then I do a sum of amounts grouped by locality and year, and I apply some pivot transformations so that the years become new fields. At the end I have something like this :
I would like to add a new column containing the difference between the sum of 2023 and the sum of 2022, for each locality. If I simply do this, it works fine:
{
$addFields: {
diff: { $subtract: [ "$2023", "$2022"] }
}
},
However, I cannot hardcode the field names like I did, since they depend on the value that I provided in the filter at the beginning. Basically, the field “2023” corresponds to {{yearFilter}} (the double brackets is how the filters are represented in Metabase), and the field “2022” corresponds to {$subtract: [ {{yearFilter}}, 1] }.
So I tried to use those patterns in the code above:
{
"$addFields": {
"diff": {
$subtract: [
"${{yearFilter}}",
"${$toString: {$subtract: [ {{yearFilter}}, 1] } }"
]
}
}
},
But it doesn’t work. I see the new column, but it is empty. In order to understand where the problem comes from, I did the following:
{
$addFields: {
YEAR1test: "${{yearFilter}}"
}
},
{
$addFields: {
"YEAR2test": {$toString: {$subtract: [ {{yearFilter}}, 1] } }
}
},
{
"$addFields": {
"YEAR3test": "${$toString: {$subtract: [ {{yearFilter}}, 1] } } "
}
},
The first addFields works fine: I have well a new field containing the same content that I had in the field ‘2023’.
The second addFields gives me a new field containing always the value ‘2022’. So it means that the {{yearFilter}} is correctly interpreted, as well as the subtract. However what I want is not the value ‘2022’, but the content of the field named ‘2022’. That’s what I try to reach with the third addFields, but it doesn’t work, as no field is added at all.
I have the feeling that it’s probably just a stupid syntax issue, but I already tried many combinations, playing with the quotes, the $, the [ ] and the { }, but nothing works.
Would someone have a magic idea on how I could do that ?
Thank you in advance !