$lookup result sorted using a key from another array

Question

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'}]
}

Show source
| arrays   | node.js   | sorting   | mongodb   | aggregate   2017-10-17 12:10 2 Answers

Answers to $lookup result sorted using a key from another array ( 2 )

  1. 2017-10-17 13:10

    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

  2. 2017-10-18 07:10

    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
    

Leave a reply to - $lookup result sorted using a key from another array

◀ Go back