mgo / mongodb：聚合-查找所有成员并按membercount排序，但是membercount是成员userid的数组

Question

The representation of 1 record (a community):

{
    "_id" : ObjectId("538a4734d6194c0e98000001"),
    "name" : "Darko",
    "description" : "Darko",
    "subdomain" : "darko",
    "domain" : "forum.dev",
    "created" : ISODate("2014-05-31T21:18:44.764Z"),
    "category" : "Art and Culture",
    "owner" : "53887456d6194c0f5b000001",
    "members" : [ 
        "53887456d6194c0f5b000001"
    ]
}

and the Go type

Community struct {
    Id          bson.ObjectId `bson:"_id,omitempty" json:"id"`
    Name        string        `json:"name"`
    Description string        `bson:",omitempty" json:"description"`
    Subdomain   string        `bson:",omitempty" json:"subdomain"`
    Domain      string        `json:"domain"`
    Created     time.Time     `json:"created"`
    Category    string        `json:"category"`
    Owner       string        `json:"owner"`
    Banned      []string      `bson:",omitempty" json:"banned"`
    Members     []string      `json:"members"`
    Moderators  []string      `bson:",omitempty" json:"moderators"`
    Admins      []string      `bson:",omitempty" json:"admins"`
    Logo        string        `bson:",omitempty" json:"logo"`
    Stylesheets []string      `bson:",omitempty" json:"stylesheets"`
    Javascripts []string      `bson:",omitempty" json:"javascripts"`
}

Ok now I'd like to retrieve a list of all communities of Category Art and Culture and order by the number of members aka members.length in js or len(Community.Members) in Go.

something like SELECT * FROM communities ORDER BY COUNT(members) WHERE category = 'Art and Culture'

I have a custom type to be filled or unmarshalled into

CommunityDirectory struct {
    Id          bson.ObjectId `bson:"_id,omitempty" json:"id"`
    Name        string        `json:"name"`
    Description string        `bson:",omitempty" json:"description"`
    Subdomain   string        `bson:",omitempty" json:"subdomain"`
    Domain      string        `json:"domain"`
    Created     time.Time     `json:"created"`
    Category    string        `json:"category"`
    Logo        string        `bson:",omitempty" json:"logo"`
    Membercount int64         `bson:"membercount" json:"membercount"`
}

---

What I have so far

func (ctx *CommunityContext) Directory() {
    pipe := ccommunity.Pipe([]bson.M{bson.M{"membercount": bson.M{"$size": "members"}}})
    iter := pipe.Iter()
    result := CommunityDirectory{}
    results := []CommunityDirectory{}
    for {
        if iter.Next(&result) {
            results = append(results, result)
            fmt.Println(result)
        } else {
            break
        }
    }
    ctx.JSON(results)
}

but this doesn't work because

db.communities.aggregate(
[
{"membercount": {$size:"members"}}
]
)

Error("Printing Stack Trace")@:0
()@src/mongo/shell/utils.js:37
([object Array])@src/mongo/shell/collection.js:866
@(shell):3

uncaught exception: aggregate failed: {
    "errmsg" : "exception: Unrecognized pipeline stage name: 'membercount'",
    "code" : 16436,
    "ok" : 0
}

So, it should find all, order by membercount and assign a new "virtual" field membercount but only of category 'Art and Culture'.

I find MongoDB quite complicated in this regard.

1. What does the mongodb query look like?

2. What does that look like in Go/mgo?

Answer 1

There are a few concepts to get used to when you are new to the aggregation framework

The correct form of the pipeline as it would work in the shell should be this:

db.communties.aggregate([

    // Match the documents first to filter and possibly make use of an index
    { "$match": {
        "category": "Art and Culture"
    }},

    // You include all fields when adding another and you want all
    { "$project": {
        "name": 1,
        "description": 1,
        "subdomain": 1,
        "domain": 1,
        "created": 1,
        "category": 1,
        "owner": 1,
        "members": 1,
        "memberCount": { "$size": "$members" }
    }},

    // $sort means "ORDER BY" in this case the ascending
    { "$sort": { "memberCount": 1 } },

    // Optionally project just the fields you need in the result
    { "$project": {
        "name": 1,
        "description": 1,
        "subdomain": 1,
        "domain": 1,
        "created": 1,
        "category": 1,
        "owner": 1,
        "members": 1
    }}
])

So there really is no direct equivalent of "SELECT *" unless you don't want to alter the structure at all. Here you need to add a field "memberCount" so you need to specify all of the fields. You can possibly use $$ROOT which copies all the fields in the document but you would need to assign that to another field/property in your $project, such as:

{ "$project": {
    "_id": "$$ROOT",
    "memberCount": 1
 }}

But now of course all your "fields" are not exactly the same as what they were and are all prefixed with _id.. But that is a matter of personal taste.

The next thing to get used to is always try to use $match first. Not only does this help reduce the documents operated on over the rest of the aggregation pipeline, it is also the only chance you get to use an index to optimize your query. Once you modify the documents with other stages, it's all over for using an index as this is no longer the original source that was indexed. Not that different from SQL really, but the semantics differ a somewhat in how you specify. Remember, "pipeline" just like a Unix "pipe" | operator, so do the "matching" first.

Sorting has it's own pipeline stage. So use the $sort operator for the pipeline stage to do this.

The final $project is optional. Here we are just discarding the "memberCount" field that was used to "sort".

---

The usage with mGo should turn out like this:

pipeline := [].bson.D{
    bson.M{"$match": bson.M{ "category": "Art and Culture" } },

    bson.M{"$project": bson.M{
        "name": 1,
        "description": 1,
        "subdomain": 1,
        "domain": 1,
        "created": 1,
        "category": 1,
        "owner": 1,
        "members": 1,
        "memberCount": bson.M{ "$size": "$members" }
    }},

    bson.M{ "$sort": bson.M{ "memberCount": 1 } },

    bson.M{ "$project": bson.M{
        "name": 1,
        "description": 1,
        "subdomain": 1,
        "domain": 1,
        "created": 1,
        "category": 1,
        "owner": 1,
        "members": 1
    }}
}

pipe := ccommunity.Pipe( pipeline )

So really not that different to the form of most examples out there you will find.

Possibly look at the SQL to aggregation Mapping Chart provided in the core documentation for other examples of common SQL queries as they apply to the aggregation pipeline.