0

I use aggregate to get all users have same name, but whenever I query to this API, the results return are not sorted even thought I add $sort by 'name' field.

const aggregateQuery: any = [
            { $group: { _id: '$name', count: { $sum: 1 }, users: { $push: '$$ROOT' } } },
            { $match: { count: { $gt: 1 } } },
            { $unwind: '$users' },
            { $replaceRoot: { newRoot: '$users' } },
            { $sort: { name: 1 } }
        ];`

        const users = await this.userRepository.getModel().aggregate(aggregateQuery).exec();

The order of the records is not sorted

ruud
  • 743
  • 13
  • 22
John
  • 3
  • 1

1 Answers1

0

you have to sort case insensitively
Let's say you need name and age,

Try this query :

[ { "$project": { "name": 1, "age" : 1, "insensitive": { "$toLower": "$name" } }},{ $group: { _id: '$name', count: { $sum: 1 }, users: { $push: '$$ROOT' } } }, { $match: { count: { $gte: 1 } } }, { $unwind: '$users' }, { $replaceRoot: { newRoot: '$users' } }, { $sort: { insensitive: 1 } } ]

If you have more fields to be added to the output, then instead of the above query, try the below one.

[ { "$project": { "name": 1, createdAt : 1 }},{ $group: { _id: '$name', count: { $sum: 1 }, users: { $push: '$$ROOT' } } }, { $match: { count: { $gte: 1 } } }, {"$addFields":{"users.insensitive": { "$toLower": "$_id" },}}, { $unwind: '$users' }, { $replaceRoot: { newRoot: '$users' } }, { $sort: { insensitive: 1 } } ]

sachin
  • 1,075
  • 1
  • 7
  • 11
  • The sorting is work in this case but It seems that data only returns 1 name field, I want to return other fields too – John May 17 '23 at 07:23
  • So, did we have option to return all field ? – John May 17 '23 at 07:34
  • Yes, you just have to mention the `field` name in the `project` json with value to `1` – sachin May 17 '23 at 07:35
  • So, If we have 50 fields, we must be put all field to option. Do you have any ideal to with another way ? – John May 17 '23 at 07:39
  • And @sachin, the results also return the field not have same same. I meant query above return all users. I just want to return the users have the same name and sort them – John May 17 '23 at 08:06
  • According to the first question ( regarding 50 fields ), there's an updated query in the answer, please look at it. And I did not get your second question clearly. – sachin May 17 '23 at 08:35
  • May be I have a problem when compare ipAddress. the Ip have the format 127.0.0.1 and they just compared 127.0 ( 2 component of IP) . I tried to use substr but it's not work – John May 17 '23 at 08:45
  • I want to return all users have absolute same ip – John May 17 '23 at 08:54