Trend Visualization not working with Native Query (MongoDb)

Get Help
1

I am creating a native query question for Trend Visualization using following MongoDB query -

[
  {
    "$project": {
      "date": {
        "$dateToString": {
          "format": "%Y-%m-%d",
          "date": "$createdAt"
        }
      },
      "totalLeads": "$totalLeads"
    }
  },
  {
    "$project": {
      "_id": "$date",
      "totalLeads": "$totalLeads"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "min_count": {
        "$first": "$totalLeads"
      },
      "max_count": {
        "$last": "$totalLeads"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "date": {
        "$dateFromString": {
          "dateString": "$_id",
          "format": "%Y-%m-%d"
        }
      },
      "leadsCreated": {
        "$subtract": [
          "$max_count",
          "$min_count"
        ]
      }
    }
  },
  {
    "$sort": {
      "date": 1
    }
  }
]

The query is returning correct data --> date and leadsCreated, but the Trend Visualization is still disabled and when clicked this error message is shown -
Group by a time field to see how this has changed over time

PFA screenshot for references. Please help me out.

30%20PM
30%20PM2880Ă—1644 327 KB

Diagnostic Info -
{
"browser-info": {
"language": "en-GB",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.89 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_252-8u252-b09-1~18.04-b09",
"java.vendor": "Private Build",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_252",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.252-b09",
"os.name": "Linux",
"os.version": "4.15.0-1063-aws",
"user.language": "en",
"user.timezone": "Etc/UTC"
},
"metabase-info": {
"databases": [
"googleanalytics",
"mongo"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.8"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.36.0-rc2",
"date": "2020-07-08",
"branch": "remove-equiv-method-implementations",
"hash": "5956b4d"
},
"settings": {
"report-timezone": null
}
}
}

2

Hi @ankit939
0.36.0 has been released, so you should use that instead of rc2.
You need to return a date-type, not a string, to be able to use Trend.

3

“date” field is date-type only. It is not a string. Used $dateFromString on date field in last $project block as you can see in the query.

4

@ankit939 I don’t use MongoDB, but try using the interface to generate a query that works with Trend and then compare the differences.
I know SQLite causes some problems, so perhaps it’s the same for MongoDB:
https://github.com/metabase/metabase/issues/12388

5

I have the same problem, leave the field in date, string format, multiple ways and nothing.
Metabase version 0.37.8

it's a time field

image
image784Ă—688 32.7 KB

group by date and quantity

image
image817Ă—568 26.3 KB

6

@jlcornejo And if you use the interface, does that work? Can you compare the two queries to see if you can spot the problem.

7

with the interface it works, the problem is that I have to join 2 collections, and that is not supported by the interface

8

@jlcornejo Okay, then compare the queries and result data to see if you can spot the problem.

9

I cannot compare, when creating the trend graph from the interface and transforming to query, the functionality is lost, the trend graph is disabled

image
image877Ă—821 141 KB

10

@jlcornejo You are most likely seeing this issue:
https://github.com/metabase/metabase/issues/13710 - upvote by clicking :+1: on the first post

11

I still have the same problem. I tried to create the query using the UI and it is working fine. But the moment I convert it to a native query without changing anything the trend chart stops showing.

The reason I'm trying to change the question to a native query is because caching is not working with questions that are created via the UI with mongodb.

Any idea how this can be solved?

12

you should upgrade at least to 45.2. Caching Mongo UI queries will be available in v46

1 Like