CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-queryaggregation-framework
Naveen
Naveen

Reputation: 777

How to merge two queries into a single query in MongoDB

Here I have two query for one table. Second query's $match contain result from first query.

db.histories.aggregate([    
    {$match: {
        from: userid,
        connectTime: {"$gte":yesterday},
        status:'completed'
      }},
    {$group: {
        _id: '$groupId',
        groupId: { $last: '$groupId' },
        callId:{ $last: '$callId' },
      }},
    { $sort: {
        connectTime:-1
      } }
  ])

The result of first query contain "callId", using that "callId" I'm aggregating second query.

db.histories.aggregate([    
    {$match: {
        $or: [{ callId: {"$in": groupCallIdArray} }, { connectedCallId: {"$in": groupCallIdArray} }],
        status:'completed'
      }},
    {$group: {
        _id: {'groupId':'$groupId'},
        from: { $last: '$from' },
        to: { $last: '$to' },
        minimumTime: { $last: '$minimumTime' },
        noOfcalls: {$sum:1},
        duration:{ $sum: '$duration' },
      }}
  ])

Do we have any way to merge two queries into a single query.

Input JSON

[{
 "_id" : ObjectId("60fe4bf5c0fe3d0017059776"),
 "callId" : "CAbfadc16eed3f493f742b208e283848af",
 "connectedCallId" : "CA0c61d90d1694ef219b42412246570c63",
 "from" : ObjectId("6062f39c9ccebd00178bf302"),
 "to" : ObjectId("606d59547db42d00178234a6"),
 "callConnectTime" : 2021-07-26 05:44:19.573Z,
 "status" : "completed",
 "minimumTime" : 15,
 "duration" : 24,
 "groupId" : ObjectId("60c8908e55242c00170e3e00"),
}]

First QUERY OUTPUT

[
  {
    _id:60fe4bf5c0fe3d0017059776,
    gigsId: 60fe4bf5c0fe3d0017059776,
    callSid: 'CAbfadc16eed3f493f742b208e283848af'
  }
]

using first queries output I'm creating match query for second query. groupCallIdArray = ["CAbfadc16eed3f493f742b208e283848af"]

Second Query Input

[{
    _id:{groupId:ObjectId("60fe4bf5c0fe3d0017059776")},
    from:ObjectId("6062f39c9ccebd00178bf302"),
    to:ObjectId("606d59547db42d00178234a6"),
    minimumTime:15,
    noOfcalls:1,
    duration:24,
}...
]

Upvotes: 0

Views: 1214

Answers (1)

s7vr
s7vr

Reputation: 75914

You could use $lookup to pull in the matches.

Something like

db.histories.aggregate([    
    {$match: {
        from: userid,
        connectTime: {"$gte":yesterday},
        status:'completed'
      }},
    { $sort: {
        connectTime:-1
      } }
    {$group: {
        _id: '$groupId',
        groupId: { $last: '$groupId' },
        callId:{ $last: '$callId' },
      }},
   {$group: {
       _id: null,
       groupCallIdArray: { $push: '$callId'}
   }},
   {$lookup: {
       from: 'histories',
       let: { groupCallIdArray: '$groupCallIdArray'},
       pipeline: [
         {$match:{
            status:'completed',
            {$expr: {
               $or: [{ callId: {"$in": '$$groupCallIdArray'} }, { 
                     connectedCallId: {"$in": '$$groupCallIdArray'} }]}}
            }
         },
         {$group: {
           _id: {'groupId':'$groupId'},
           from: { $last: '$from' },
           to: { $last: '$to' },
           minimumTime: { $last: '$minimumTime' },
           noOfcalls: {$sum:1},
          duration:{ $sum: '$duration' },
         }}
       ],
      as: 'results'
   }}
  ])

Upvotes: 1

Related Questions