Trend Visualization not working with MongoDB

1

So I have this MongoDB data source. I used the interface to create a trend visualization and it works.
image
image

But I have a native code that needs to be used because the interface isn't good enough. It seems that the Trend visualization is disabled
image

image
image337×523 12.7 KB

Here is the full query

[
    {
        "$match": {
          "$expr": {
            "$lt": [
              "$created",
              {
                "$dateFromString": {
                  "dateString": "2023-01-01T00:00Z"
                }
              }
            ]
          }
        }
    },
    {
        "$addFields": {
            "activityCount": {
                "$cond": {
                    "if": {
                        "$and": [
                            {
                                "$isArray": "$divisions"
                            },
                            {
                                "$gt": [
                                    {
                                        "$size": "$divisions"
                                    },
                                    0
                                ]
                            }
                        ]
                    },
                    "then": {
                        "$size": "$divisions"
                    },
                    "else": 1
                }
            }
        }
    },
    {
        "$match": {
            "document_status": "final"
        }
    },
    {
        "$project": {
            "created": 1,
            "divisions": 1,
            "activityCount": 1
        }
    },
    {
        "$group": {
            "_id": {
                "year": {
                    "$let": {
                        "vars": {
                          "parts": {
                            "$dateToParts": {
                              "timezone": "GMT",
                              "date": "$created"
                            }
                          }
                        },
                        "in": {
                          "$dateFromParts": {
                            "timezone": "GMT",
                            "year": "$$parts.year"
                          }
                        }
                    }
                }
            },
            "count": {
                "$sum": "$activityCount"
            }
        }
    },
    {
        "$sort": {
            "_id": 1
        }
    },
    {
        "$set": {
            "year": "$_id.year"
        }
    },
    {
        "$unset": [
            "_id"
        ]
    },
    {
        "$setWindowFields": {
            "sortBy": {
                "year": 1
            },
            "output": {
                "cumulative": {
                    "$sum": "$count",
                    "window": {
                        "documents": [
                            "unbounded",
                            "current"
                        ]
                    }
                }
            }
        }
    },
    {
        "$project": {
            "cumulative": 1,
            "year": 1
        }
    }
]

Here is the data from Admin > Troubleshooting

{
  "browser-info": {
    "language": "en-US",
    "platform": "Linux x86_64",
    "userAgent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.17+8",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.17",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.17+8",
    "os.name": "Linux",
    "os.version": "5.15.0-58-generic",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "mongo",
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "mysql",
    "application-database-details": {
      "database": {
        "name": "MariaDB",
        "version": "10.10.2-MariaDB-1:10.10.2+maria~ubu2204"
      },
      "jdbc-driver": {
        "name": "MariaDB Connector/J",
        "version": "2.7.6"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-01-17",
      "tag": "v0.45.2",
      "branch": "release-x.45.x",
      "hash": "819ce29"
    },
    "settings": {
      "report-timezone": "Asia/Jakarta"
    }
  }
}
1 Like
2

Hey, I'm not super familiar with MongoDB, but in your native query, can you convert the "year" column to a datetime data type?

I think the Trend viz expects a numeric column and a datetime column to work.

You can tell "year" isn't a datetime because Metabase is formatting it with a comma :slight_smile:

3

Hi,

Could you finally make it work ? Because I have exactly the same issue, and I have the feeling that there's really a bug somewhere.
In my case, I have a collection containing a date, a year (integer format, computed from the date), an amount, and some other fields. I would like to sum the amounts per year, and display the trend between 2 years. It works fine when doing it from the Metabase UI. I sum the amount, group by year, apply a filter on the date to select the previous year (with including the current year), and it gives me the expected result.

image
image850×418 8.32 KB

image

Note that it seems that the filter on the date is optional, because when I remove it, I have exactly the same result (so I guess that by default, Metabase takes the most recent year and compares it with the previous one).

Now I convert the query into a native query, Metabase generates this. We can see that the filter has disappeared, but it's not a problem anyway.

[
  {
    "$group": {
      "_id": {
        "date~~~year": {
          "$let": {
            "vars": {
              "parts": {
                "$dateToParts": {
                  "timezone": "GMT",
                  "date": "$date"
                }
              }
            },
            "in": {
              "$dateFromParts": {
                "timezone": "GMT",
                "year": "$$parts.year"
              }
            }
          }
        }
      },
      "sum": {
        "$sum": "$feeNetAmount"
      }
    }
  },
  {
    "$sort": {
      "_id": 1
    }
  },
  {
    "$project": {
      "_id": false,
      "date~~~year": "$_id.date~~~year",
      "sum": true
    }
  },
  {
    "$sort": {
      "date~~~year": 1
    }
  }
]

Without changing anything to the automatically generated query, I can already realize that the Trend visualization is not available anymore (which I think is a bug, since it worked well before the conversion to native query). When I display the results in table format, I see this :
image

But when I try to select the Trend visualization, I have the error message "Group by a time field to see how this has changed over time", which is strange since I have well a date in the results.

The reason for which I want to use a native query is because I would like to make a dashboard which is a bit dynamic, and I would like the user to be able to choose the reference year, and display the trend between that year and the previous one.

I re-worked the native query to try to do that:

[
    {
        $match: {
            $or: [
                { $expr: {$eq: ["$year", {{year}} ] } },
                { $expr: {$eq: ["$year",{ $subtract: [ {{year}}, 1 ] }]} }
            ]
        }
    },
    {
        $addFields: {
            year_date: { 
                $convert: {
                    input: {$concat: [ {$toString: "$year"},"-01-01"]},
                    to: "date"
                }
            }
        }
    },
    {
        "$group": {
            "_id": "$year_date",
            "totalNBI": {"$sum": "$feeNetAmount"}
        }
    },
    {
        "$project": {
        "_id": true
        "totalNBI": true
        }
    },
    {
        "$sort": {
        "_id": 1
        }
    }
]

The user is then able to select a specific year (in number format). Based on that I first apply a filter on that year and the previous one. After that I add a new "fake" date based on the year (1st of January of the year), and then I sum the amounts, grouped by that fake year. In table format, it gives me this kind of result, which looks correct:
image

But when I try to select the Trend visualization, I have the same error message as above.
Could you confirm that it's well a bug ? (or suggest a solution if it's not ? ). For info, I'm using Metabase v0.44.6.
Thank you in advance.