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, Apr 17 '20 at 04:22
Does this answer your question? [Aggregate documents where objects in array matches multiple conditions](https://stackoverflow.com/questions/47825790/aggregate-documents-where-objects-in-array-matches-multiple-conditions) , Try this :: (https://mongoplayground.net/p/nF7atV1mCtw) — whoami - fakeFaceTrueSoul, Apr 17 '20 at 04: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, Apr 17 '20 at 05:22
@whoami this doesn't help because in your examaple you are calling Collection_A.aggregate. The problem comes when you doo $Collection_A.features beacuse $Collection_A.features is returned after lookip — anon, Apr 17 '20 at 05:25
So basically to answer both of the above question. $match doesn't take $Collection_A.features as operator for $elemMatch to work upon. — anon, Apr 17 '20 at 05:27
@anon : To be said this question is so confusing !! So you're aggregating on `Collection_B` & also looking upon `Collection_B`(lookup on same collection) & creating a field called `Collection_A`(which is result of lookup) ? Then what is this actual `Collection_A` doing in your question ? Did you mistakenly do this :: `from: "Collection_B"` instead of `Collection_A` ? — whoami - fakeFaceTrueSoul, Apr 17 '20 at 05:30
@whoami Think of it as a join on ref_id. So, aggregate is done on Collection_B. It calls lookup on Collection_A (basically join). So what lookup does is, all the properties of Collection_A can be referenced now as $Collection_A. You can give it some other name by changeing "as: "Collection_A"" in $lookup. Features is present in Collection_A and hence $Collection_A.Features. The point is there are other properties in Collection_B which I haven't specified just to explain what I am trying to do. Hope this helps. — anon, Apr 17 '20 at 05:36

score 1 · Answer 1 · answered Apr 17 '20 at 06:16

@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"
    }
  }
])

score 0 · Answer 2 · answered Apr 17 '20 at 05:42

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 '20 at 05:42

whoami - fakeFaceTrueSoul

17,086
6
32
46

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. – anon Apr 17 '20 at 05:51
@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 ? – whoami - fakeFaceTrueSoul Apr 17 '20 at 05:55
And sorry for the typo in $lookup from:Collection_B, changed it to from:Collection_A – anon Apr 17 '20 at 05:55
Yes, I have k & v in request. This is the array I am trying to match as an input : [{k:foo,v:bar},{k:foo2,v:bar2}]. Both of these should be presnt in $Collections_A.features, then only document should be returned. – anon Apr 17 '20 at 05:56
Actually this worked: https://mongoplayground.net/p/3xBPkQcECkH – anon Apr 17 '20 at 06:13
@anon : Since you're passing in subset of object(only few fields of each object) in `features` array you can't use direct `$all` or `$in`, You can do it either by adding multiple `$match` with `$elemMatch` for multiple input objects or by doing something like below... – whoami - fakeFaceTrueSoul Apr 17 '20 at 06:15
@anon : Cont'd.. `{ "$match": { $and: [ { $and: [ { "features.k": "foo" }, { "features.v": "bar" } ] }, { $and: [ { "features.k": "foo2" }, { "features.v": "bar2" } ] } ] } }` – whoami - fakeFaceTrueSoul Apr 17 '20 at 06:16
1

@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.. – whoami - fakeFaceTrueSoul Apr 17 '20 at 06:18
if I get what you are saying then what I shared is correct way to approach? – anon Apr 17 '20 at 06:19
@anon : Uhh yes correct, I've missed your comment in these many comments..!! – whoami - fakeFaceTrueSoul Apr 17 '20 at 06:20
1

I really appreciate all the help :) – anon Apr 17 '20 at 06:22

Mongodb: Search in array with $aggregate

2 Answers2