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 用matlab写代码
  • ¥30 motoradmin系统的多对多配置
  • ¥15 求组态王串口自定义通信配置方法或代码?
  • ¥15 实验 :UML2.0 结构建模
  • ¥20 用vivado写数字逻辑实验报告撰写,FPGA实验
  • ¥15 为什么shp文件会有这种小方块?
  • ¥15 ecplise在连接数据库时显示加载驱动成功但是数据库连接失败
  • ¥15 visionmaster启动失败,提示为“机器不满足授权而被禁用”
  • ¥15 IDEA中圈复杂度如何具体设置
  • ¥50 labview采集不了数据