Aggregate cannot correctly aggregate date fields

[nothinggift]

Issue Description

I need to count the new data every day

group: {
    objectId: { day: { $dayOfMonth: "$createdAt" }, month: { $month: "$createdAt" }, year: { $year: "$createdAt" } },
    count: { $sum: 1 },
},

Result:

Expected Results

Count the same date together

Environment Setup

• Server

  • parse-server version (Be specific! Don't say 'latest'.) : 3.0.8&4.2.0
  • Operating System: ubuntu

• Database
  Postgresql 10

[nothinggift]

Sorry, I didn't find detailed documentation to explain the usage of aggregation, it really behaves like a bug

[mtrezza]

@nothinggift Sorry, I just realized your question was for Postgres, not MongoDB.

My Postgres knowledge is limited, but what you can try is to query the database directly without Parse Server to verify that your syntax is correct. If you don't get the results you expect directly from the database, then you may want to open a question on Stack Overflow to get help with formulating your query. You can then compare the query that works with the query that Parse Server does against the database. If you cannot get the same query via Parse Server, you can reopen the issue here for someone to look into that.

In the Postgres Storage Adapter you can see how your group stage is transformed into a Postgres query, maybe that will give you some insight:

parse-server/src/Adapters/Storage/Postgres/PostgresStorageAdapter.js

Lines 2232 to 2315 in d0a9c70

	if (stage.$group) {
	for (const field in stage.$group) {
	const value = stage.$group[field];
	if (value === null || value === undefined) {
	continue;
	}
	if (field === '_id' && typeof value === 'string' && value !== '') {
	columns.push(`$${index}:name AS "objectId"`);
	groupPattern = `GROUP BY $${index}:name`;
	values.push(transformAggregateField(value));
	index += 1;
	continue;
	}
	if (
	field === '_id' &&
	typeof value === 'object' &&
	Object.keys(value).length !== 0
	) {
	groupValues = value;
	const groupByFields = [];
	for (const alias in value) {
	if (typeof value[alias] === 'string' && value[alias]) {
	const source = transformAggregateField(value[alias]);
	if (!groupByFields.includes(`"${source}"`)) {
	groupByFields.push(`"${source}"`);
	}
	values.push(source, alias);
	columns.push(`$${index}:name AS $${index + 1}:name`);
	index += 2;
	} else {
	const operation = Object.keys(value[alias])[0];
	const source = transformAggregateField(value[alias][operation]);
	if (mongoAggregateToPostgres[operation]) {
	if (!groupByFields.includes(`"${source}"`)) {
	groupByFields.push(`"${source}"`);
	}
	columns.push(
	`EXTRACT(${
	mongoAggregateToPostgres[operation]
	} FROM $${index}:name AT TIME ZONE 'UTC') AS $${
	index + 1
	}:name`
	);
	values.push(source, alias);
	index += 2;
	}
	}
	}
	groupPattern = `GROUP BY $${index}:raw`;
	values.push(groupByFields.join());
	index += 1;
	continue;
	}
	if (typeof value === 'object') {
	if (value.$sum) {
	if (typeof value.$sum === 'string') {
	columns.push(`SUM($${index}:name) AS $${index + 1}:name`);
	values.push(transformAggregateField(value.$sum), field);
	index += 2;
	} else {
	countField = field;
	columns.push(`COUNT(*) AS $${index}:name`);
	values.push(field);
	index += 1;
	}
	}
	if (value.$max) {
	columns.push(`MAX($${index}:name) AS $${index + 1}:name`);
	values.push(transformAggregateField(value.$max), field);
	index += 2;
	}
	if (value.$min) {
	columns.push(`MIN($${index}:name) AS $${index + 1}:name`);
	values.push(transformAggregateField(value.$min), field);
	index += 2;
	}
	if (value.$avg) {
	columns.push(`AVG($${index}:name) AS $${index + 1}:name`);
	values.push(transformAggregateField(value.$avg), field);
	index += 2;
	}
	}
	}
	} else {

For reference, in MongoDB you would do it like this, but that is specific to MongoDB:

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

I didn't find detailed documentation to explain the usage of aggregation

I agree that the docs for aggregate lack an example for date aggregation, which may be a frequent use case. Maybe you want to add an example there after you got the query working and open a PR.

[nothinggift]

I checked the source code, this is indeed a bug, no matter how you write "objectId", the groupBy key is a combination of the original fields, which means that in this example it will always be "group by createdAt"

[mtrezza]

Thanks for pointing this out. To clarify, can you write what the expected vs. the currently generated Postgres query is? Do you think you can open a PR to fix this?

[nothinggift]

Ok i will try.