Finding count of unused series in a collection of books via aggregation and grouping

Working with Data
,
#1

I’m working on an aggregation to locate unused series in a collection of books. The series are it’s own collection and the books reference the series _id. I can get close to what I’m looking for but can’t get a set of series and counts that are not used in books. I’m getting the opposite, actually. I feel like it’s a small thing I’m missing… but I just can’t see it. Still learning aggs and the ins & outs so I appreciate the help.

QUERY:
db.books.aggregate([{
        $lookup: {
            from: "series",
            localField: "series",
            foreignField: "name",
            as: "series_lookup"
        }
    },
    {
        $group: {
            '_id': '$series',
            'count': { '$sum': 1 }
        }
    }, {
        $match: { 
            series: { $exists: false } 
        }
    }
]).toArray()

DESIRED RESULTS:
[{
    "_id":"orphan_series",
    "count": 2
},{
    "_id":"another_orphan",
    "count": 3
}]

ACTUAL RESULTS:
[{
        "_id" : "currie_odyssey_one",
        "count" : 7
},
{
        "_id" : "currie_archangel_one",
        "count" : 3
},
{
        "_id" : "kloos_frontlines",
        "count" : 10
},
{
        "_id" : "currie_holy_ground",
        "count" : 1
},
{
        "_id" : "currie_star_rogue",
        "count" : 1
},
{
        "_id" : "king_legend_of_zero",
        "count" : 7
}]

SERIES COLLECTION SAMPLE:
{
    "_id": "orphan_series",
    "name": "Not used in collections",
}

BOOKS COLLECTION SAMPLE:
{
    "_id": "kloostermsofenlistment",
    "order": 1,
    "title": "Terms of Enlistment",
    "author": {
        "name": "Marko Kloos",
        "_id": "marko_kloos"
    },
    "series": "kloos_frontlines"
}
#2

Hi @Mike_E ,

So the simplified lookup syntax is design to only match documents between collections.

If I understood correctly you want the opesite to get all the series documents that do not match…

This can be achieved by running on series collection and looking up on the books:


db.series.aggregate(([{
        $lookup: {
            from: "books",
            localField: "name",
            foreignField: "series",
            as: "book_lookup"
        }
    },
{
        $match: { 
            $eq: [0, {$size: "$book_lookup"} ]
        }
    },

 {
        $group: {
            '_id': '$series',
            'count': { '$sum': 1 }
        }
    }
])

I haven’t run the aggregation but the concept is to lookup the series with books. A series with an empty “books_lookup” is an orphan one (size = 0).

Only then count them :slight_smile:

Hopefully my aggregation is not buggy .

Thanks
Pavel

1 Like
#3

Thank you, Pavel. That helps greatly. I didn’t think of the lookup having a zero count in that way. That solved my issue.

1 Like