MongoDB Query | Using date variable

Get Help
1

Hi there!

i have data like this in my connected MongoDB collection "orders":

{
  "_id": {
    "$oid": "63fb69e083d79a02053fe0c2"
  },
  "meta": {
    "address": {
      "firstname": "Max",
      "lastname": "Mustermann",
      "name": "Max Mustermann",
      "street": "Musterstraße 1",
      "city": "Musterhausen",
      "zip": "123456",
      "countryCode": "DE",
      "countryId": 1,
      "phone": "0123456789",
      "company": "",
      "country": "Deutschland"
    },
    "confirmTime": "2023-02-26T14:17:03.211Z",
    "branchProcessorId": 1234567,
    "processor": {
      "id": 101785,
      "type": 4,
      "inputs": {
        "phone": "015228401129"
      }
    },
    "payment": {
      "isCashPayment": false,
      "isPayed": true,
      "isDemo": false
    }
  },
  "order": {
    "id": 9029395,
    "uid": "54ec5159-8ccd-49b6-a153-e047561a1012",
    "userId": 1083642,
    "userPersonId": "123-456789",
    "userName": "Max Mustermann",
    "userFirstName": "Max",
    "userLastName": "Mustermann",
    "payerUserId": 12345678,
    "payerUserPersonId": "112-345678",
    "payerUserName": "Max Mustermann",
    "payed": true,
    "confirmed": true,
    "articles": [
      {
        "id": 16561864,
        "articleId": 1465417,
        "amount": 1,
        "name": "01 Margherita",
        "options": [
          {
            "id": 7952747,
            "optionId": 1465418,
            "price": 0,
            "name": "klein"
          }
        ],
        "price": 0,
        "priceSum": 0,
        "taxRate": 7
      }
    ],
    "subscriptions": [],
    "sum": 0
  },
  "branch": {
    "id": 123456
  },
  "__v": 0
}

I now want to use variables to specify a Date-Range in Metabase for showing me all orders in the defined date range:

[
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$gte": [
              { "$dateFromString": { "dateString": "$meta.confirmTime" } },
              { "$dateFromString": { "dateString": "{{start_date}}" } }
            ]
          },
          {
            "$lte": [
              { "$dateFromString": { "dateString": "$meta.confirmTime" } },
              { "$dateFromString": { "dateString": "{{end_date}}" } }
            ]
          }
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "confirmTime": "$meta.confirmTime",
      "branchId": "$branch.id",
      "payerUserName": "$order.payerUserName",
      "articles": "$order.articles",
      "orderSum": "$order.sum"
    }
  }
]

For this native query i´m getting "Invalid JSON number". Is this because metabase only submits date and not date-time format?

Variable type is: Date

Setting variable type to the specified field "meta.confirmTime" doesn´t makes any difference.

Any ideas?

2

So that query runs if you query directly MongoDB not via Metabase?

3

What exactly do you mean?
I can´t run this command directly to MongoDB with Compass for example, because of the variables "{{start_date}}" and "{{end_date}}". But yes this one for example is running in Compass and Metabase:

[
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$gte": [
              { "$dateFromString": { "dateString": "$meta.confirmTime" } },
              { "$dateFromString": { "dateString": "2023-02-25T00:00Z" } }
            ]
          },
          {
            "$lte": [
              { "$dateFromString": { "dateString": "$meta.confirmTime" } },
              { "$dateFromString": { "dateString": "2023-03-28T00:00Z" } }
            ]
          }
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "confirmTime": "$meta.confirmTime",
      "branchId": "$branch.id",
      "payerUserName": "$order.payerUserName",
      "articles": "$order.articles",
      "orderSum": "$order.sum"
    }
  }
]
4

No one any idea?

5

Have a look at the mongo sections here:

Remove the ""

6

okay thanks for that hint. Without the "" it´s throwing this error:

Command failed with error 241 (ConversionFailure): 'Failed to optimize pipeline :: caused by :: Failed to optimize expression :: caused by :: $dateFromString requires that 'dateString' be a string, found: date with value 2023-03-02T00:00:00.000Z' on server web01.myhost:3271. The full response is { "ok" : 0.0, "errmsg" : "Failed to optimize pipeline :: caused by :: Failed to optimize expression :: caused by :: $dateFromString requires that 'dateString' be a string, found: date with value 2023-03-02T00:00:00.000Z", "code" : 241, "codeName" : "ConversionFailure" }

So exactly what i expected. Metabase isn´t submitting the timestamp in the right format. Is there any way to change this?

7

please send troubleshooting info

8

What exactly do you need?

Diagnostic Info:

{
  "browser-info": {
    "language": "de-DE",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.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.18+10",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.18",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.18+10",
    "os.name": "Linux",
    "os.version": "4.19.0-23-amd64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "mongo"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-02-19",
      "tag": "v0.45.3",
      "branch": "release-x.45.x",
      "hash": "070f57b"
    },
    "settings": {
      "report-timezone": null
    }
  }
}
9

If possible, try upgrading to 46, we did massive changes in how Mongo works in 46

10

okay i´ll try and let u know. Thanks

11

Hey! I have since revisited the problem and am now working on version 0.46.2, but the problem still exists.

Now I have already tried to pass the date simply as a string:

[
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$gte": [
              { "$dateFromString": { "dateString": "$meta.confirmTime" } },
              { "$dateFromString": { "dateString": "{{start_date}}" } }
            ]
          },
          {
            "$lte": [
              { "$dateFromString": { "dateString": "$meta.confirmTime" } },
              { "$dateFromString": { "dateString": "{{end_date}}" } }
            ]
          }
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "confirmTime": "$meta.confirmTime",
      "branchId": "$branch.id",
      "payerUserName": "$order.payerUserName",
      "articles": "$order.articles",
      "orderSum": "$order.sum"
    }
  }
]

I also get the same error "Invalid JSON number".... I don't understand how Metabase passes this variable, so it can't be interpreted in the MongoDB query.

try
try1902×821 50.8 KB

Does anyone else have any ideas?

12

before someone asks: I just updated to 0.46.6.2. Same problem.

13

@tynie If you've set the variable type to Date, then you don't need parse it with $dateFromString. Just use the variable directly:

"$gte": [
    { "$dateFromString": { "dateString": "$meta.confirmTime" } },
    {{start_date}}
]
1 Like
14

Thank you @Petro that did the trick!