Nested MongoDB query with Golang mgo.v2

Question

I'm querying a json structure with a nested array of items. I'd like to return the entire structure but only include the items of the nest which match the query criteria.

So - sample structure is

{
     parentId:1,
     items: [
            {
               field1: 1
               field2: 2
            },
            {
               field1: 3
               field2: 4
            }
            ]
}

I would like to query with something like this ->

db.getCollection('mycollection').find({"items.field1":1, "items.field2":2})

this works but it also brings back the second child item, what I want it to actually return is this ->

{
     parentId:1,
     items: [
            {
               field1: 1
               field2: 2
            }
            ]
}

I have been able to create a query in MongoDB itself which achieves what I want ->

db.getCollection('mycollection').aggregate(
  { $unwind : "$items" },
  { $match : {
     "items.field1": 1,
     "items.field2": 2,
  }}
)

but when trying to set this up using mgo.v2 it's proving a bit of a mare. The collection.Find method doesn't seem to like the $unwind command and can't seem to get the right syntax to get it working with the Pipe method.

Does anyone have any suggestions how this can be created? Can I just create the string and pass it for execution?

chridam · Accepted Answer · 2016-09-15 15:28:37Z

2

The actual pipeline you need involves the use of the $filter operator, which selects a subset of the array to return based on the specified condition. It returns an array with only those elements that match the condition.

In your case, you need to run the following aggregation operation

db.mycollection.aggregate([
   {
        "$project": {
            "parentId": 1,          
            "items": {
                "$filter": {
                    "input": "$items",
                    "as": "item",
                    "cond": { 
                        "$and": [
                            { "$eq": ["$$item.field1", 1] },
                            { "$eq": ["$$item.field2", 2] }
                        ]
                    }
                }
            }
        }
    }
])

Testing

For MongoDB versions which do not support the $filter operator, you can use a combination of set operators as:

db.mycollection.aggregate([
   {
        "$project": {
            "parentId": 1,          
            "items": {
                "$setDifference": [
                    { "$map": {
                        "input": "$items",
                        "as": "item",
                        "in": {
                            "$cond": [
                               { 
                                    "$and": [
                                        { "$eq": ["$$item.field1", 1] },
                                        { "$eq": ["$$item.field2", 2] }
                                    ]
                                },
                                "$$item",
                                false
                            ]
                        }
                    }},
                    [false]
                ]
            }
        }
    }
])

As a last resort, you can use the $unwind operator as the operator produces a copy of each document per array entry which uses more memory (possible memory cap on aggregation pipelines of 10% total memory) and thus also takes time to produce as well as "time" to process. You can run as:

db.mycollection.aggregate([
    { "$unwind" : "$items" },
    { "$match" : {
        "items.field1": 1,
        "items.field2": 2,
    }},
    {
        "$group": {
            "_id": "$_id",
            "parentId": { "$first": "$parentId" },
            "items": { "$push": "$items" }
        }
    }
])

which you can run in mgo as a pipeline:

pipeline := []bson.M{   
    bson.M{ "$unwind": "$items" },
    bson.M{
        "$match": bson.M{ 
            "items.field1": 1,
            "items.field2": 2
        }
    },
    bson.M{
        "$group": bson.M{
            "_id": "$_id",
            "parentId": bson.M{ "$first": "$parentId" },
            "items": bson.M{ "$push": "$items" }
        }
    }   
}
pipe := mycollection.Pipe(pipeline)
iter := pipe.Iter()

Testing in Robomongo

edited Sep 15, 2016 at 15:28

answered Sep 15, 2016 at 13:40

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

P456678 Over a year ago

Thanks for the feedback but that doesnt work, I get the following error -> cannot use bson.M literal (type bson.M) as type bson.D in array or slice literal

P456678 Over a year ago

I changed the bson.D to bson.M which compiles, but the result isn't correct. It has no nested child items at all.

P456678 Over a year ago

I've also tried this - pipeline := []bson.M{ bson.M{"$unwind": "$items"}, bson.M{"$match": bson.M{ "items.field1": bson.M{"$eq": 1}, "items.field2": bson.M{"$eq": 2}, }, }, }

P456678 Over a year ago

Done, all that was missing was the desired output, the sample doc was already there

chridam Over a year ago

@P456678 I've updated my answer to include the optimized solution which does not use the $unwind operator.

|

Community · Accepted Answer · 2017-05-23 10:29:56Z

Referring to chridam's answer, I believe the one with $filter can be simplified a bit:

c := session.DB("test").C("mycollection")

pipe := c.Pipe([]bson.M{{
    "$project": bson.M{
        "parentId": 1,
        "items": bson.M{
            "$filter": bson.M{
                "input": "$items",
                "as":    "item",
                "cond": bson.M{
                    "$and": []bson.M{
                        {"$eq": []interface{}{"$$item.field1", 1}},
                        {"$eq": []interface{}{"$$item.field2", 2}},
                    },
                },
            },
        },
    },
}})

resp := []bson.M{}
err = pipe.All(&resp)

if err != nil {
    panic(err)
}

fmt.Println(resp)

The other with $unwind:

c := session.DB("test").C("mycollection")

pipe := c.Pipe([]bson.M{
    {"$unwind": "$items"},
    {"$match": bson.M{
        "items.field1": 1,
        "items.field2": 2,
    }},
    {"$group": bson.M{
        "_id":      "$_id",
        "parentId": bson.M{"$first": "$parentId"},
        "items":    bson.M{"$push": "$items"},
    }},
})

resp := []bson.M{}
err = pipe.All(&resp)

if err != nil {
    panic(err)
}

fmt.Println(resp)

Both compile without error and return

[map[
    _id:ObjectIdHex(".....") 
    parentId:1 
    items:[map[
        field2:2 
        field1:1
    ]]
]]

go 1.6, mongo 3.2

Collectives™ on Stack Overflow

Nested MongoDB query with Golang mgo.v2

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related