Aggregation match doesn't seem to work with date value

[SebC99]

Issue Description

When using the new aggregate options with a match pipeline on a Date value, it silently fails with empty result.
By the way, to match a createdAt or a updatedAt field, we have to use the db form _created_at / _updated_at, and I'm not sure it's on purpose. Either way, we can't query them with the standard $lt or $gt operator.

Steps to reproduce

query.aggregate([{match: {_created_at:{$lt:new Date()}}}]) always returns []
whereas
query.aggregate([{match: {_created_at:{$exists:true}}}]) works perfectly.

Expected Results

Both results should be the same

• Server

  • parse-server version (Be specific! Don't say 'latest'.) : 2.7.4
  • Localhost or remote server? Localhost

• Database

  • MongoDB version: 3.4
  • Storage engine: WiredTiger
  • Localhost or remote server? Mongo Atlas

[dplewis]

Thanks for reporting. I'll have a look.

[sauravazad]

I have been facing the same issue and it is related to only Date fields , number works fine.

[eleynes]

Any updates on this? TIA

[fmendoza]

Same here

[eleynes]

extracting databaseController adapter is my workaround.

Parse.Cloud.define("testMongoAggregate", function(request, response) {
// const { val1, val2 } = request.params;
const { AppCache } = require("parse-server/lib/cache"); // this refrenese only in cloud cloud
const app = AppCache.get(process.env.APP_ID);

const schema = {
// you don't have to write all specifics here, but have to have fields object.
fields: {}
};

const start = moment.utc().startOf('day'); // Start of day
const end = moment.utc().endOf('day'); // End of day

const pipeline = [
{
$match: {
_created_at: { $lte: new Date('2018-04-11T00:00:00.000Z'), $gte: new Date('2018-04-01T00:00:00.000Z') },
}
},
{
$sort: {
_created_at: -1
}
},
{
$group: {
_id: "$_p_project"
}
}
];

app.databaseController.adapter
.aggregate("Table", schema, pipeline)
.then(result => {
console.log(result);
if (result) {
response.success({ success: result });
} else {
response.error({ error: true });
}
});
});

[dplewis]

Sorry guys been off the grid.

I'll submit a PR sometime this week.

[cjbland]

FWIW, in my debugging last night (if I remember correctly) MongoStorageAdapter.js was converting a Date object into a string, and the mongodb adapter did not seem to like that. Tonight I'll modify the code to keep it a Date object and see if that makes a difference.

[cjbland]

Yes, this was definitely it. When I converted the strings to new Date() and passed that on then I started getting results back. Within MongoStorageAdapter.js within the aggregate method I added check on schema.fields[field] to see if it was a date. If so, I convert the string into a Date object. That seems to do the trick. I can make a PR if you'd like to review the code.

src/Adapters/Storage/Mongo/MongoStorageAdapter.js

aggregate(className: string, schema: any, pipeline: any, readPreference: ?string) {
  ...
          if (schema.fields[field] && schema.fields[field].type === 'Pointer') {
            const transformMatch = { [`_p_${field}`] : `${schema.fields[field].targetClass}$${stage.$match[field]}` };
            stage.$match = transformMatch;
          }
          else if (schema.fields[field] && schema.fields[field].type === 'Date') {
            const transformMatch = { [`${field}`]: new Date(stage.$match[field]) };
            stage.$match = transformMatch;
          }
...
}

[flovilmart]

Go ahead with the PR! That sound like a good fiz

[cjbland]

PR is GH-4743 let me know if you have any other improvements.

[korhanozbek44]

Same bug for me.

[dplewis]

Closed via #4743

[anhnhv]

It's still have a bug with this code:

query.aggregate([{match: {customDateField:{$exists:false}}}])

I have edited the source code of MongoStorageAdapter.js to debug

if (stage.$match) { console.log(stage.$match); stage.$match = this._parseAggregateArgs(schema, stage.$match); console.log(stage.$match); }

And what I received are:
{ customDateField: { $exists: false } }
{ customDateField: { $exists: {} } }

[pixel2]

I have the same problem as @anhnhv2812. Could you please create a new bug so we can track and try and get this fixed, or do you want me to do it?

[SebC99]

I have the same issue, and with custom fields that are created by the aggregation pipeline, and not in the base Schema, the dates are not detected.
Modifying the _parseAggregateArgs method in the MongoStorageAdapter file to add a

} else if (pipeline instanceof Date) {
      return pipeline;
}

condition before the if (typeof pipeline === 'object') seems to work, but I'm not sure of the repercussion of this.

Anyway, testing the schema in all the pipeline is weird as all the fields can be changed/removed/added at every stage, so the schema is not coherent anymore.
As for the isPointerField condition which is set during any $group stage and used to change the final $_id of the last stage of the pipeline... I just don't understand why, but it results in weird _id deletion (but I'm far from understanding all that code) 😅

[adrianaxente]

There is another work-around :

{
    'match': {
        "$expr": {
            "$lte": [
                "$some_date_field", 
                {
                    "$toDate": "2020-09-09T00:00:00.000Z" 
                }
            ]
        }
    }
}

Where "2020-09-09T00:00:00.000Z" can be used as string.
Hope it helps someone till the fix will be available in the following releases.

[ilovett]

Thanks for the workaround @adrianaxente

[andreisucman]

Here is the new syntax for Parse v6 +
It aggregates rows by date and counts the number of occurences within a date.

const pipeline = [
  {
    $group: {
      _id: { $dateToString: { format: "%Y-%m-%d", date: "$createdAt" } },
      count: {
        $sum: 1,
      },
    },
  },
];

    const query = new Parse.Query("Table");
    const qResult = await query.aggregate(pipeline);

Not that unlike in previous versions you use _id instead of objectId, and you must prefix the stages with $ just like directtly in MongoDB