Parsing fails when calculation is included in mongo query

I want to query for documents that were created in the last 24 hours .
In SQL it'd be like this:

select * from table where table.createdAt >= date_sub( $$now, interval 1 day)

The most intuitive way to do this is to use $dateSubtract or $dateAdd like this (which by the way is posted by me)

but the thing is I am using Azure Cosmos for Mongo API, and it doesn't support those functions. You can see these two are not in there.

So the next best thing you can think of is something like this:
$lte: ISODate()-123
this is syntactically correct and does work on MongoDb Compass. But when I do this on Metabase, the Java parser returns a validation error.
the error message is as follows

JSON reader was expecting a name but found '-123'.

The next thing you think of, quite naturally, is to use $subtract, like this:

      $subtract:[ISODate(), 123 ]

I haven't got around and figure it out, but this thing doesn't work on the Cosmos label, so I can't use this. (See the screen shot, both stages do exactly the same thing, and one returns documents and the other doesn't)

(the documents returned in each stage are hidden)

So what would be a good workaround for this, I just want to do

select * from table where column = 'somekeyword' and createdAt > date_sub($$now, interval 1 day) order by createdAt desc

A slightly related one:

Hi @smzk
Have a look here for a possible workaround:

1 Like

the bottom line is, I am thinking I should use something else like Redash for this kind of stuff.
Depending on how recent the user wants the data to be, I actually wanted to change the number (last 24 hours -> last 4 hours and so on)
But since * s are not allowed either, this is going to be difficult as well....

I liked Metabase for its simplicity but I have to say I might have to consider options like Redash.
I'm just using this on-premise, not paying you anything but it's kind of sad.