Query MongoDb aggregate join two collections

Question

I need help for querying mongoDb

So I have two collections like

Collection A:

{someField: "123", anotherField: "456"},
{someField: "1234", anotherField: "4567"}

Collection B

{someField: "123", otherField: "789"}

with Query:

db.A.aggregate([
   {
      $lookup:
         {
           from: "B",
           let: { someField: "$someField", otherField: "$otherField" },
           pipeline: [
              { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$someField",  "$$someField" ] },
                         { $eq: [ "$otherField",  "789" ] }                       
                       ]
                    }
                 }
              },
           ],
           as: "B"
         }
    }
])

I get all collection A, with B empty in {someField: "1234", anotherField: "4567"}

What I want to achieve is like:

{someField: "123", anotherField: "456", b: {someField: "123", otherField: "789"}}

Thank you in advance

You can filter the documents after the lookup, if the B array size is greater than zero (see $size). — prasad_
– prasad_, Commented Mar 2, 2020 at 11:27

Dĵ ΝιΓΞΗΛψΚ · Accepted Answer · 2020-03-02 16:06:39Z

2

you only need to declare $someField in the let section.

db.collectionA.aggregate([
  {
    $lookup: {
      from: 'collectionB',
      let: { some_field: '$someField' },
      pipeline: [
        { $match: {
            $expr: {
              $and: [
                { $eq: [ "$someField", "$$some_field" ] },
                { $eq: [ "$otherField", "789" ] }
              ]
            }
          }
        }
      ],
      as: 'B'
    }
  },
  {
    $match: {
      $expr: {
        $gt: [ { $size: "$B" }, 0 ]
      }
    }
  }
])

https://mongoplayground.net/p/RTiUMWl8QaX

edited Mar 2, 2020 at 16:06

answered Mar 2, 2020 at 11:25

Dĵ ΝιΓΞΗΛψΚ

5,8044 gold badges16 silver badges30 bronze badges

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

2 Comments

Dimas Angga Saputra Over a year ago

It still shows all of collection A rather than only collection A that has collection B ...

Dĵ ΝιΓΞΗΛψΚ Over a year ago

@dImasAnggaSaputra you can either reverse the lookup or add a $match stage. updated my answer above.

prasad_ · Accepted Answer · 2020-03-02 13:41:55Z

1

This is how I removed the empty B array documents:

db.A.aggregate( [
   {
      $lookup: {
           from: "B",
           localField: "someField",
           foreignField: "someField",
           as: "B"
         }
    },
    {
       $addFields: {
            B: {
                 $filter: {
                      input: "$B",
                      cond: {
                          $eq: [ "$$this.otherField", "789" ]
                      }
                 }
            }
      }
    },
    {
       $match: { 
           $expr: {
                $gt: [ { $size: "$B" }, 0 ]
           }
       }
    }
] ).pretty()

edited Mar 2, 2020 at 13:41

answered Mar 2, 2020 at 13:30

prasad_

14.4k2 gold badges27 silver badges44 bronze badges

Collectives™ on Stack Overflow

Query MongoDb aggregate join two collections

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related