Forward $match answer from one collection to another $match in Mongo

user_3312595 0
Viewed 88

Background

I have a mongo collection tags:

...
{ "_id" : 901, "tagkey" : "color", "tagvalue" : "black" }
{ "_id" : 902, "tagkey" : "color", "tagvalue" : "white" }
{ "_id" : 903, "tagkey" : "store", "tagvalue" : "supercenter" }
{ "_id" : 904, "tagkey" : "store", "tagvalue" : "gas station" }
...

And another collection items: 

...
{ "_id" : 12, "itemname" : "truck", "tags" : [901] }
{ "_id" : 13, "itemname" : "ink", "tags" : [901, 903] }
{ "_id" : 14, "itemname" : "paper", "tags" : [902, 903] }
{ "_id" : 14, "itemname" : "gas", "tags" : [904] }
...

I am trying to ask for all the objects that are in a store. This means I want a list of all items that have a tag with a keyname: "store". So I am outputting all the items containing 903 or 904 in their tags list. So, I should get back a list containing ink, paper and gas.

Question

How do I use the output from one table's $match as the value for my next query?

My closest guess

db.tags.aggregate ( 
    {$match: {tagkey: "store"}}, # now I have a list of all the tag items
    {$match: {tags: {$elemMatch: {$in :[****]} } }} ) #Here I need to run the match query on a different collection and match the tags element to one of the _ids from the first match
)

More specific questions

  1. How do I get the second $match to refer to a different collection
  2. How do I get that array of elements to be the _ids of the output of the first $match
aggregation-framework mongodb
2 Answers

In order to join multiple collections, you can use $lookup, but the $lookup aggregation pipeline stage will not work directly with an array.

Try the following query: 

db.items.aggregate([
  {$unwind : "$tags"},
  {$lookup : {
        "from" : "tags",
        "localField" : "tags",
        "foreignField" : "_id",
        "as" : "tagsObject"
      }
  },
  {$unwind : "$tagsObject"},
  {$match : {"tagsObject.tagkey" : "store"}} // To Filter out only the tag items. 
]);

Hope this helps!

user_32153580

You can use $lookup with uncorrelated subqueries syntax to define your matching criteria and then check if there are any tags assigned to each item using $ne operator

db.items.aggregate([
    {
        $lookup: {
            from: "tags",
            let: { tags: "$tags" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                                $and: [
                                { $in: [ "$_id", "$$tags" ] },
                                { $eq: [ "$tagkey", "store" ] }
                            ]
                        }
                    }
                }
            ],
            as: "tagDetails"
        }
    },
    {
        $match: {
            tagDetails: {
                $ne: []
            }
        }
    }
])
user_62389770
Related