CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
mmu36478
mmu36478

Reputation: 1345

$project and $in in mongodb query

Assume I have structure as;

{
    "class" : "TESTCLASS",
    "id" : "HyvF1sdZl",
    "depends" : [
        "S11T5ce",
        "BytQLN6ml",
        "Byzz8Ea7l",
        "r1TUNX58x"
    ],
    "list" : [
        {
            "class" : "DATA",
            "id" : "H113d5Pwx"
        },
        {
            "class" : "DATA",
            "id" : "H113d5Pwx"
        },
        {
            "class" : "TEST",
            "id" : "r1TUNX58x"
        },
        {
            "class" : "DATA",
            "id" : "H113d5Pwx"
        },
        {
            "class" : "TEST",
            "id" : "r1TUNX58x"
        },
        {
            "class" : "TEST",
            "id" : "r1TUNX58x"
        },
        {
            "class" : "FDSAFAS",
            "id" : "S11T5ce"
        },
        {
            "class" : "YENITEST",
            "id" : "r1TUNX58x"
        },
        {
            "class" : "FDSAFAS",
            "id" : "S11T5ce"
        },
        {
            "class" : "CALENDAR",
            "id" : "S11T5ce"
        },
        {
            "class" : "EN",
            "id" : "BytQLN6ml"
        },
        {
            "class" : "CALENDAR",
            "id" : "S11T5ce"
        },
        {
            "class" : "EN_",
            "id" : "Byzz8Ea7l"
        },
        {
            "class" : "TEST",
            "id" : "r1TUNX58x"
        },
        {
            "class" : "EN",
            "id" : "BytQLN6ml"
        },
        {
            "class" : "FDSAFAS",
            "id" : "S11T5ce"
        }
    ]
}

I want to project list items whose id's are included in depends array.

I mean, I only want to get;

"depends" : [
   "S11T5ce",
   "BytQLN6ml",
   "Byzz8Ea7l",
   "r1TUNX58x"
],
"list" : [
    {
        "class" : "FDSAFAS",
        "id" : "S11T5ce"
    },
    {
        "class" : "EN",
        "id" : "BytQLN6ml"
    },
    {
        "class" : "EN_",
        "id" : "Byzz8Ea7l"
    },
    {
        "class" : "TEST",
        "id" : "r1TUNX58x"
    }

]

To be able to do this I have written;

db.definition.aggregate([
    {
        $project: {
            _id: 0,
            depends :1,
            depends: {$in: ["$list.id"]},
            "list.defid": 1,
            "list.class" :1,
        }

    }
]).pretty()

But this throws an error as "errmsg" : "Expression $in takes exactly 2 arguments. 1 were passed in."

What am I missing?

Upvotes: 2

Views: 2010

Answers (2)

chridam
chridam

Reputation: 103305

With MongoDB 3.4 you could use the $addFields pipeline to project other fields without explicitly naming them. As a single pipeline, use a combination of the $setUnion, $filter and $in operators to return the desired array. The $setUnion operator helps with eliminating duplicates before applying the $filter operator.

To follow the intuition, lets follow this example pipeline that just runs with the $filter operator:

db.collection.aggregate([
    {
        "$addFields": {
            "list": {
                "$filter": {
                    "input": "$list",
                    "as": "item",
                    "cond": { "$in": ["$$item.id", "$depends"] }                       
                }
            }
        }
    }
])

Sample Output

{
    "_id" : ObjectId("58d0e0d97a3871921504bb69"),
    "class" : "TESTCLASS",
    "id" : "HyvF1sdZl",
    "depends" : [
        "S11T5ce",
        "BytQLN6ml",
        "Byzz8Ea7l",
        "r1TUNX58x"
    ],  
    "list" : [ 
        {
            "class" : "TEST",
            "id" : "r1TUNX58x"
        }, 
        {
            "class" : "TEST",
            "id" : "r1TUNX58x"
        }, 
        {
            "class" : "TEST",
            "id" : "r1TUNX58x"
        }, 
        {
            "class" : "FDSAFAS",
            "id" : "S11T5ce"
        }, 
        {
            "class" : "YENITEST",
            "id" : "r1TUNX58x"
        }, 
        {
            "class" : "FDSAFAS",
            "id" : "S11T5ce"
        }, 
        {
            "class" : "CALENDAR",
            "id" : "S11T5ce"
        }, 
        {
            "class" : "EN",
            "id" : "BytQLN6ml"
        }, 
        {
            "class" : "CALENDAR",
            "id" : "S11T5ce"
        }, 
        {
            "class" : "EN_",
            "id" : "Byzz8Ea7l"
        }, 
        {
            "class" : "TEST",
            "id" : "r1TUNX58x"
        }, 
        {
            "class" : "EN",
            "id" : "BytQLN6ml"
        }, 
        {
            "class" : "FDSAFAS",
            "id" : "S11T5ce"
        }
    ]
}

With the $setUnion operator, duplicates are eliminated first before applying $filter as:

db.collection.aggregate([
    {
        "$addFields": { 

            "list": {
                "$filter": {
                    "input": { "$setUnion": ["$list", []] },
                    "as": "item",
                    "cond": { "$in": ["$$item.id", "$depends"] }                       
                }                
            }            
        }
    }
])

Sample Output

{
    "class" : "TESTCLASS",
    "id" : "HyvF1sdZl",
    "depends" : [ 
        "S11T5ce", 
        "BytQLN6ml", 
        "Byzz8Ea7l", 
        "r1TUNX58x"
    ],
    "list" : [ 
        {
            "class" : "FDSAFAS",
            "id" : "S11T5ce"
        }, 
        {
            "class" : "TEST",
            "id" : "r1TUNX58x"
        }, 
        {
            "class" : "YENITEST",
            "id" : "r1TUNX58x"
        }, 
        {
            "class" : "EN_",
            "id" : "Byzz8Ea7l"
        }, 
        {
            "class" : "CALENDAR",
            "id" : "S11T5ce"
        }, 
        {
            "class" : "EN",
            "id" : "BytQLN6ml"
        }
    ]
}

Upvotes: 2

Nenad Vracar
Nenad Vracar

Reputation: 122027

You can first use $project and $filter to filter out objects where id is not in depends array but then to return unique objects by id and also include class is a bit of problem because if you use $addToSet you can't specify fields that you want to make unique but ignore other fields in object or in this case class in list. 

db.col.aggregate([
    {$project: {
        depends: 1,
        class: 1,
        list: {
            $filter: {
                input: '$list',
                as: 'item',
                cond: {
                    $setIsSubset: [['$$item.id'], '$depends']
                }
            }
        }
    }},
    {$unwind: '$list'},
    {$group: {
        _id: '$_id',
        depends: {$first: '$depends'},
        class: {$first: '$class'},
        list: {
            $addToSet: {
                id: '$list.id',
                class: '$list.class'
            }
        },

    }}
])

The final result is this

[{
  "_id": "58d0cea6aecf3102684e4f0e",
  "depends": ["S11T5ce", "BytQLN6ml", "Byzz8Ea7l", "r1TUNX58x"],
  "class": "TESTCLASS",
  "list": [{
    "id": "Byzz8Ea7l",
    "class": "EN_"
  }, {
    "id": "BytQLN6ml",
    "class": "EN"
  }, {
    "id": "S11T5ce",
    "class": "FDSAFAS"
  }, {
    "id": "r1TUNX58x",
    "class": "YENITEST"
  }, {
    "id": "S11T5ce",
    "class": "CALENDAR"
  }, {
    "id": "r1TUNX58x",
    "class": "TEST"
  }]
}]

Upvotes: 1

Related Questions