droc60607 2014-06-01 19:58
浏览 141
已采纳

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

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?

  • 写回答

1条回答 默认 最新

  • doz22551 2014-06-02 00:54
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 我这模型写的不对吗?为什么lingo解出来的下面影子价格这一溜少一个变量
  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波