Aggregate functions causing performance issue

Hi, I am new to MongoDB and below is my query and it is working fine when trying to get 50k data performance is very slow, May I know that following query is fine or need any modification.

let news=  NewsRoomCollection.aggregate(
          [
            {$unwind: "$deals"},
            {$unwind: "$users"}, 
            queryParams.filters,
            {$match:{ "users.userId": queryParams.userId }},
            {$group:{
              _id: "$_id",
              companyId : { $first: '$companyId' },
              companyName : { $first: '$companyName' },
              newsId: {$first: "$newsId"},
              newsTitle: {$first: "$newsTitle"},
              newsLink: {$first: "$newsLink"},
              newsPublishedAt: {$first: "$newsPublishedAt"},
              deals: {$push: "$deals"},
              users:{$push: "$users"}
            }},
          {
            $facet: {
              "data": [
                { $sort:  queryParams.sortQuery },
                { $skip: skip },
                { $limit: limit },
              ],
              "pagination": [
                { $count: "total" }
              ]
            }
          },              
        ]
      ).toArray();

Hi @Naveen_hm ,

The best practices of an aggregation is to perform the match stages as early as possible with an index on the matched fields:

In your case is { “users.userId”: 1 } indexed?

Why not first match and only then perfom the unwinds?

[
            {$match:{ "users.userId": queryParams.userId }},
            {$unwind: "$deals"},
            {$unwind: "$users"}, 
            queryParams.filters,
            {$group:{
              _id: "$_id",
              companyId : { $first: '$companyId' },
              companyName : { $first: '$companyName' },
              newsId: {$first: "$newsId"},
              newsTitle: {$first: "$newsTitle"},
              newsLink: {$first: "$newsLink"},
              newsPublishedAt: {$first: "$newsPublishedAt"},
              deals: {$push: "$deals"},
              users:{$push: "$users"}
            }},
          {
            $facet: {
              "data": [
                { $sort:  queryParams.sortQuery },
                { $skip: skip },
                { $limit: limit },
              ],
              "pagination": [
                { $count: "total" }
              ]
            }
          },              
        ]

I also don’t really get why you unwind to then group with push? Is it only to find documents with a user in an array? This will work also when comparing an array with a single value as it works as $in…

Thanks
Pavel

2 Likes

“I also don’t really get why you unwind to then group with push?” this is i used because once i got the result set am looping it other reason.

Is it only to find documents with a user in an array? not only to based on userId but also based some other fields like queryParams.filters is a object which has other fields for match

In your case is { “users.userId”: 1 } indexed? it is not indexed am just saving values in array

below is the example collection data:

{
  "_id": "MhHnLT7frymsjp2Mr",
  "newsId": "CBMibWh0dHBzOi8vd3d3LmtmdHYuY29tL25ld3MvMjAyMi8wNS8yNi9uZW9tcy13YXluZS1ib3JnLXRhbGtzLXByb2R1Y3Rpb24tYW5kLXN1c3RhaW5hYmlsaXR5LWFtYml0aW9ucy1pbi1jYW5uZXPSAXFodHRwczovL3d3dy5rZnR2LmNvbS9hbXAvbmV3cy8yMDIyLzA1LzI2L25lb21zLXdheW5lLWJvcmctdGFsa3MtcHJvZHVjdGlvbi1hbmQtc3VzdGFpbmFiaWxpdHktYW1iaXRpb25zLWluLWNhbm5lcw",
  "newsTitle": "NEOM's Wayne Borg talks production and sustainability ambitions in Cannes - KFTV",
  "newsLink": "https://www.kftv.com/news/2022/05/26/neoms-wayne-borg-talks-production-and-sustainability-ambitions-in-cannes",
  "companyId": "LDMXHxSEYXtNXGFEr",
  "companyName": "NEOM",
  "deals": [
    {
      "dealId": "P9v4RMWRr7zcbnptd",
      "clusterId": "AutonomousAndSustainableMobility"
    }
  ],
  "users": [
    {
      "userId": "iChz62XNeMfA7oB9A",
      "isReadNotification": false
    },
    {
      "userId": "zHruHDSjyWhkvF398",
      "isReadNotification": false
    },
    {
      "userId": "hPS7K5it6nZstj6M3",
      "isReadNotification": false
    },
    {
      "userId": "g3ctNC7rykc8BsoiM",
      "isReadNotification": true
    },
    {
      "userId": "jR9Xjc5Rrcnt3beGe",
      "isReadNotification": false
    },
    {
      "userId": "ZjtNtCHHt3SdPLGYD",
      "isReadNotification": false
    },
    {
      "userId": "xbf6pHLx4CKSjF8rM",
      "isReadNotification": false
    },
    {
      "userId": "yAbR4wmaAwqL9qwsQ",
      "isReadNotification": true
    },
    {
      "userId": "RvfvkH9o3FpEbWKmR",
      "isReadNotification": true
    },
    {
      "userId": "YR9sGETsDyEJAQ9wy",
      "isReadNotification": true
    },
    {
      "userId": "2ehdDcK9QoEzye6jj",
      "isReadNotification": true
    }
  ],
  "createdAt": 1655963402992,
  "newsPublishedAt": 1653523200000
}