MongoDB Union All?

Question

I am trying to union 3 collections in one but can't make it work (same as union all). As an example, I have 3 collections:

1:active users

[{_id:'', client_id:'',created:''}]

2:not active users

[{_id:'', visitor_id:'',created:''}]

3:blocked users

[{_id:'', blocked_id:'',created:''}]

what i am trying to do is to combine all 3 collections in one, sort them by date and make sure i get the first 10 results, something like that:

[{_id:'', client_id:'',created:''},
 {_id:'', blocked_id:'',created:''},
 {_id:'', visitor_id:'',created:''}]

I came up with this code:

collection('active).aggregate([        
       {  
          "$facet":{  
             "active":[  
                {"$match":{  "client_id": 5cfe83820c19ee3c50c8f323 }}
             ],
             "not_active":[  
                {"$match":{"cust_id":"5cfe83820c19ee3c50c8f323" }}
             ],
             "blocked":[  
                {"$match":{"blocked_id":"5cfe83820c19ee3c50c8f323" }}
             ]
          }
       },
       {  
          "$skip":0
       },
       {  
          "$limit":10
       },
       {  
          "$sort":{  
             "created":-1
          }
       }
])

but this code return empty array:

[ { active: [], not_active: [], blocked : []} ]

What i am doing wrong? Thanks

score 1 · Answer 1 · answered Jun 11 '19 at 01:03

Finally i came up with this code that does the job:

mongo.clients.aggregate([
         {$limit:1},{$project:{_id:1}},{$project:{_id:0}},
         {$lookup : {from:'active',as:'active',pipeline:[{$match:{}}]}},
         {$lookup : {from:'not_active',as:'not_active',pipeline:[{$match:{}}]}},
         {$lookup : {from:'blocked',as:'blocked',pipeline:[{$match:{}}]}},
         {$project:{Union:{$concatArrays:['$active','$not_active','$blocked']}}},
         {$unwind:"$Union"},
         {$replaceRoot:{newRoot:"$Union"}},
         {$skip:0},
         {$limit:10},
         {$sort:{'created': -1}}
]).then(x);

Here is more detailed answer: enter link description here

MongoDB Union All?

1 Answers1