$match in aggregate don't return data in mongodb

Question

I have three tables below is the structure like below

I'm looking to get a result like below

"type1": [ -- type from Accounts collection
            {
                "_id": "5e97e9a224f62f93d5x3zz46", -- _id from Accounts collection
                "locs": "sampleLocks 1", -- field from Accounts collection
                "solutions": "sample solutions 1", -- field from Accounts collection
                "Clause": "clause 1" -- field from AccountsDesc collection
            },
            {
                "_id": "5e97e9a884f62f93d5x3zz46", 
                "locs": "sampleLocks2",
                "solutions": "sample solutions2", 
                "Clause": "clause2" 
            }
        ],

        "type2": [
         // same data construction as of type1 above
        ]

_id, locks, solution to be coming from Accounts collection
Clause field to be coming from AccountsDesc collection
accounts_id is kind of a foreign key in AccountsDesc coming from Account
competitor_id is kind of a foreign key in AccountsDesc coming from Competitor

Below is what my query looks like

db.accountDesc.aggregate([
  { 
        $match : {accounts_Id : "123456"}, active: true}
  },
  {
    $lookup: {
        from: 'accounts',
        pipeline: [{ $match: { type: { $in: ["type1, type2, type3"] } } }],
        as: 'accountsData'
    }
  },
  {
    $group: {

      _id: "$accountsData.type",
      data: {
           $push:  {_id: "$accountsData._id", clause: "$clause", locs: "$type.locs",  solutions: "$type.solutions"}
       }
    }
  },
   {
    $group: {
        _id: null,
            data: {
                $push: {
                    k: {
                        $toString: '$_id'
                    },
                    v: '$data'
                }
            }
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $arrayToObject: '$data'
            }
        }
    }
])

Issues related with the query -

$match : {accountId : "123456"}, active: true} -- No data is returned if i use match on AccountsDesc collection
cant set localField, foriegnField if im using pipeline, then how the mapping will happen like a LEFT join.
clause: "$clause" don't get the value of this field in the response

Your data field doesn't match with table structure (like "clause", "accountId"), so please, provide us MongoDB sample data for all tables — Valijon
– Valijon, Commented Apr 16, 2020 at 7:50
@Valijon - i have updated the data object in the question. Also i have given a comment as to which field will come from which collection. accountId is kind of a foreignKey in AccountDesc, which comes from Accounts collection. — The JOKER
– The JOKER, Commented Apr 16, 2020 at 8:00
I see. Is it possible sample data? You can share it in mongoplayground.net — Valijon
– Valijon, Commented Apr 16, 2020 at 8:03
@Valijon - not sure if this is what you are expecting, mongoplayground.net/p/L7YWYF8bBvR because my structure is the same. — The JOKER
– The JOKER, Commented Apr 16, 2020 at 8:17
Fill please collections with real sample data, where accountDesc should have accountId field in order to reproduce your query mongoplayground.net/p/8LG3_K9vIal — Valijon
– Valijon, Commented Apr 16, 2020 at 8:25

Valijon · Accepted Answer · 2020-04-16 10:43:37Z

As we discussed in chat, you want RIGHT OUTER JOIN for your aggregation.

Try the query below:

db.User_Promo_Map.aggregate([
  {
    $match: {
      user_Id: ObjectId("5e8c1180d59de1704ce68112")
    }
  },
  {
    $lookup: {
      from: "promo",
      pipeline: [
        {
          $match: {
            active: true,
            platform: {
              $in: [
                "twitch",
                "youtube",
                "facebook"
              ]
            }
          }
        }
      ],
      as: "accountsData"
    }
  },
  {
    $unwind: "$accountsData"
  },
  {
    $group: {
      _id: "$accountsData.platform",
      data2: {
        $addToSet: {
          amount: "$amount",
          promo_Id: "$promo_Id"
        }
      },
      data: {
        $addToSet: {
          _id: "$accountsData._id",
          format: "$accountsData.format",
          description: "$accountsData.description"
        }
      }
    }
  },
  {
    $addFields: {
      data: {
        $map: {
          input: "$data",
          as: "data",
          in: {
            "_id": "$$data._id",
            "description": "$$data.description",
            "format": "$$data.format",
            amount: {
              $reduce: {
                input: "$data2",
                initialValue: "$$REMOVE",
                in: {
                  $cond: [
                    {
                      $eq: [
                        "$$this.promo_Id",
                        "$$data._id"
                      ]
                    },
                    "$$this.amount",
                    "$$value"
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      data: {
        $push: {
          k: {
            $toString: "$_id"
          },
          v: "$data"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $arrayToObject: "$data"
      }
    }
  }
])

MongoPlayground

Collectives™ on Stack Overflow

$match in aggregate don't return data in mongodb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related