CODEWITHSUNDEEP
arraysnode.jsmongodbsortingaggregate
Anitta Paul
Anitta Paul

Reputation: 465

$lookup result sorted using a key from another array

I have the following data structures:

playlist collection:

{
    _id:123,
    artistId:959789,
    title:'playlist1',
    tracks:[{trackId:123456,createdDate:03.02.2017},
        {trackId:213556,createdDate:04.02.2017},
        {trackId:956125,createdDate:05.02.2017}]
},
{
    _id:456,
    artistId:456456,
    title:'playlist2',
    tracks:[{trackId:956336,createdDate:03.02.2017},
        {trackId:213556,createdDate:09.02.2017},
        {trackId:785556,createdDate:011.02.2017}]
},
{
    _id:456,
    artistId:456456,
    title:'playlist3',
    tracks:[{trackId:636985,createdDate:01.02.2017},
        {trackId:456585,createdDate:06.02.2017},
        {trackId:785556,createdDate:09.02.2017}]
}

The trackId in the tracks array of a playlist is the _id of a track in track collection

tracks collection:

{_id:956336,title:'abc'},
{_id:785556,title:'cdf'},
{_id:456585,title:'ghi'},
{_id:213556,title:'xyz'},
{_id:636985,title:'lmn'}

What i did was an aggregate $lookup using the trackId in the tracks array and i got the result. But the playlistTracks was sorted in some other order not in the order of the tracks array order.

{
        $match: {artistId: 456}
},
{
    $lookup: {
            from: 'tracks',
            localField: 'tracks.trackId',
            foreignField: '_id',
            as: 'playlistTracks'
        }
},

Now what I need is to get the list of playlists by a particular artist having the following structure : The playlistTracks should be sorted in the order on the createdDate in the tracks array.

{
    _id:456,
    title:'playlist2',
    tracks:[{trackId:636985,createdDate:01.02.2017},
        {trackId:456585,createdDate:06.02.2017},
        {trackId:785556,createdDate:09.02.2017}]
    playlistTracks:[{_id:956336,title:'abc'},
            {_id:213556,title:'xyz'},
            {_id:785556,title:'cdf'}]
},
{
    _id:456,
    title:'playlist2',
    tracks:[{trackId:636985,createdDate:01.02.2017},
        {trackId:456585,createdDate:06.02.2017},
        {trackId:785556,createdDate:09.02.2017}]
    playlistTracks:[{_id:636985,title:'lmn'},
            {_id:456585,title:'ghi'},
            {_id:785556,title:'cdf'}]
}

Upvotes: 3

Views: 461

Answers (2)

M14
M14

Reputation: 1810

Follow below steps

1 unwind the tracks array in playlist collection
2 $lookup match with tracks collection
3 add createddate of tracks array to lookup result as a new key
4 sort based on new key
5 group the results for your requirements

Upvotes: 1

Alex P.
Alex P.

Reputation: 3171

So these are the documents I added, to reproduce your use case:

Playlist collection

{ 
    "_id" : NumberInt(123), 
    "artistId" : NumberInt(959789), 
    "title" : "playlist1", 
    "tracks" : [
        {
            "trackId" : NumberInt(123456), 
            "createdDate" : "03.02.2017"
        }, 
        {
            "trackId" : NumberInt(213556), 
            "createdDate" : "04.02.2017"
        }, 
        {
            "trackId" : NumberInt(956125), 
            "createdDate" : "05.02.2017"
        }
    ]
}
{ 
    "_id" : NumberInt(456), 
    "artistId" : NumberInt(456456), 
    "title" : "playlist2", 
    "tracks" : [
        {
            "trackId" : NumberInt(956336), 
            "createdDate" : "03.02.2017"
        }, 
        {
            "trackId" : NumberInt(213556), 
            "createdDate" : "09.02.2017"
        }, 
        {
            "trackId" : NumberInt(785556), 
            "createdDate" : "11.02.2017"
        }
    ]
}
{ 
    "_id" : NumberInt(457), 
    "artistId" : NumberInt(456456), 
    "title" : "playlist3", 
    "tracks" : [
        {
            "trackId" : NumberInt(636985), 
            "createdDate" : "01.02.2017"
        }, 
        {
            "trackId" : NumberInt(456585), 
            "createdDate" : "06.02.2017"
        }, 
        {
            "trackId" : NumberInt(785556), 
            "createdDate" : "09.02.2017"
        }
    ]
}

I changed the last duplicate _id on the playlist collection with _id: 457. I don't know how you could have two documents with same _id. _id field has to be unique. And I'm not sure I understand correct your desired result, because in your $match query your write the following: $match: {artistId: 456} but in your data there is no artiseId with 456.

and this date

{trackId:785556,createdDate:011.02.2017}

from document id_ 456 I changed to 

{trackId:785556,createdDate:"11.02.2017"}

cause the date looked weird. It also looks like your date fields are strings, cause it certainly doesn't look like a date field. Either way the $sort works for both usecases.

The tracks collection I left as in your example.

So this seems to be what you need?

db.playlist.aggregate([
{
        $match: {_id: {$in: [456]}}
},
{ $unwind: "$tracks"},
{$sort: {"tracks.createdDate": 1}},
{
    $lookup: {
            from: 'tracks',
            localField: 'tracks.trackId',
            foreignField: '_id',
            as: 'playlistTracks'
        }
},
{
    $group:{
      _id: "$_id",
      artistId: {$first: "$artistId"},
      title: {$first: "$title"},
      tracks: { $push:  { item: "$tracks.trackId", quantity: "$tracks.createdDate" } },
      playlistTracks: { $push: "$playlistTracks" }
    }
}
])

This puts both arrays into same order. You can specify here {$sort: {"tracks.createdDate": 1}} if you want ascending or descending -1 order

So before looking up the fields you can unwind and sort you playlist array. Hope this works

Upvotes: 1

Related Questions