Aggregation match doesn't seem to work with date value

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

Thanks for reporting. I'll have a look.

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

Any updates on this? TIA

Same here

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 });
}
});
});

Sorry guys been off the grid.

I'll submit a PR sometime this week.

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.

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;
          }
...
}

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

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

Same bug for me.

#4678: Converting strings to Date when schema.type is Date within agg… (

Closed via #4743

#4678: Converting strings to Date when schema.type is Date within agg… (

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: {} } }

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) 😅

parse-community#4678: Converting strings to Date when schema.type is …

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.

@adrianaxente

Thanks for the workaround @adrianaxente

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

Navigation Menu

Repository navigation

Aggregation match doesn't seem to work with date value #4678

Issue Description

Steps to reproduce

Expected Results

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Aggregation match doesn't seem to work with date value #4678

Description

Issue Description

Steps to reproduce

Expected Results

Activity

dplewis commented on Mar 26, 2018

sauravazad commented on Mar 27, 2018

eleynes commented on Apr 6, 2018

fmendoza commented on Apr 16, 2018

eleynes commented on Apr 17, 2018

dplewis commented on Apr 17, 2018

cjbland commented on Apr 30, 2018

cjbland commented on Apr 30, 2018

flovilmart commented on Apr 30, 2018

cjbland commented on Apr 30, 2018

korhanozbek44 commented on May 20, 2018

dplewis commented on Jun 26, 2018

anhnhv commented on Nov 18, 2018

SebC99 commented on Nov 7, 2019

adrianaxente commented on Jul 3, 2020

ilovett commented on Aug 10, 2020

ansu16281 commented on Feb 9, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions