Mongodb: Search in array with $aggregate

Question

I have 2 collections. The documents looks like as follows. I have removed other properties for easy understanding:

Collection_A

{
    "ref_id" : ObjectId("5e9561edf8beb57100dded8f"),
    "features" : [
        {
            "_id" : ObjectId("5e9561edf8beb57100dded91"),
            "k" : "foo",
            "v" : "bar"
        },
        {
            "_id" : ObjectId("5e9561edf8beb57100dded92"),
            "k" : "foo2",
            "v" : "bar2"
        }
    ]
}

Collection_B

{
    "ref_id" : ObjectId("5e9561edf8beb57100dded8f")
}

Using aggregate I am trying to find all documents in Collection_B where Collection_B.ref_id == Collection_A.ref_id and Collection_A.features == [{k:foo,v:bar},{k:foo2,v:bar2}]

Basically match supplied features array with $Collection_A.features. Aggregate should return document when all supplied features is present in $Collection_A.features.

After trying, this is the closest I have:

let aggregation_queries = [];

aggregation_queries.push({
     $lookup: {
        from: "Collection_A",
        localField: "ref_id",
        foreignField: "ref_id",
        as: "Collection_A"
       }
});

 for(let i = 0; i< features.length; i++)
 {
   aggregation_queries.push({$match: { $expr: { $in : [features[i].k, "$Collection_A.features.k" ]}}});
 }

let aggregateResult = Collection_BSchema.aggregate(aggregation_queries);

This only matches features.k but not features.v. I am trying to find a way to match both fetaures.k and features.v, something like $and: [{features[i].k, "$Collection_A.features.k"}, {features[i].v, "$Collection_A.features.v"}]

I have searched and tried a lot of approaches like $match with $all but doesn't seem to work because match doesn't support $all for ex: "$match":{"$expr":{"$all":["$Collection_A.features",features]} which throws an error" Error: Unrecognized expression '$all'MongoError: Unrecognized expression".

Can someone please help with this or provide some guidance?

Baiscally what you are trying is to match all "CollectionA.features" with the features array you have. am I right? ex:- ``` [ {a:1,b:2},{a:3,b:4} ]=[ {a:1,b:2},{a:3,b:4} ] ``` — Nikhil Ponduri
– Nikhil Ponduri, Commented Apr 17, 2020 at 4:22
Does this answer your question? Aggregate documents where objects in array matches multiple conditions , Try this :: (mongoplayground.net/p/nF7atV1mCtw) — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Apr 17, 2020 at 4:43
Yes @Joe, I have tried. If you do $match : { $Collection_A.fetaures: {$elemMatch: fetaures[i]}}} mongo throws an error: unknown top level operator: $Collection_A.fetaures — anon
– anon, Commented Apr 17, 2020 at 5:22

anon · Accepted Answer · 2020-04-17 06:16:12Z

1

@whoami. This worked:

db.Collection_B.aggregate([
  {
    $lookup: {
      from: "Collection_A",
      let: {
        refId: "$ref_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$ref_id",
                "$$refId"
              ]
            }
          }
        },
        {
          "$match": {
            "features": {
              $all: [
                {
                  "$elemMatch": {
                    "k": "foo",
                    "v": "bar"
                  }
                },
                {
                  "$elemMatch": {
                    "k": "foo2",
                    "v": "bar2"
                  }
                }
              ]
            }
          }
        }
      ],
      as: "Collection_A"
    }
  }
])

answered Apr 17, 2020 at 6:16

anon

3771 gold badge5 silver badges19 bronze badges

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

Comments

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-04-17 05:42:08Z

0

There are couple of changes in your query, Since you're aggregating on Collection_B & wants to join Collection_B & Collection_A then in $lookup change this from: "Collection_B" to from: "Collection_A". Now you can use aggregation pipeline in $lookup which can accept multiple conditions before getting matched document from Collection_A to lookup result field Collection_A of Collection_B document :

Collection_BSchema.aggregate([
  {
    $lookup: {
      from: "Collection_A",
      let: { refId: "$ref_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$ref_id", "$$refId"] } } },
        { $match: { features: { $elemMatch: { k: "foo", v: "bar" } } } },
      ],
      as: "Collection_A",
    },
  },
]);

Test : MongoDB-Playground

answered Apr 17, 2020 at 5:42

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

11 Comments

anon Over a year ago

but how does it work with features array. I wanna match not just {k:foo, v:bar} but [{k:foo, v:bar},{k:foo2,v:bar2}]. Wouldn't I need something around the terms of { "$match": { features: {$all: [{$elemMatch:{k:foo, v:bar}},{$elemMatch:{k:foo2, v:bar2}}]} } } This I tried doesn't work.

whoami - fakeFaceTrueSoul Over a year ago

@anon : So each object in features look like this { "_id" : ObjectId("5e9561edf8beb57100dded91"), "k" : "foo", "v" : "bar" } maybe even more fields, Do you only have k's & v's in request or entire object of features ?

anon Over a year ago

And sorry for the typo in $lookup from:Collection_B, changed it to from:Collection_A

whoami - fakeFaceTrueSoul Over a year ago

@anon : You can first fetch docs into Collection_A field & do some operations to achieve that but I would rather say to add multiple $elemMatch's cause it will be easy + clean & also by the step as in lookup you'll have only needed/less data..

anon Over a year ago

I really appreciate all the help :)

|

Collectives™ on Stack Overflow

Mongodb: Search in array with $aggregate

2 Answers 2

Comments

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related