I need only one array of object

Question

I am new to MongoDB and trying to get data from two tables in a single query all should be in a single array, so I can sum their price

Here I'm getting two stores' product id's arrays from that id I need to fetch data from respective stores and add their price then I need to send the total price to client-side
my query

db.store.aggregate([
{
"$lookup": {
  "from": "store1",
  pipeline: [
    {
      $match: {
        $expr: {
          "$in": [
            "$id",
            {
              "$map": {
                // your payload from client here
                "input": [
                  "63da2f1f7662144569f78dd6",
                  "63da2f1f7662144569f78dd7",
                  
                ],
                "as": "id",
                "in": {
                  "$toObjectId": "$$id"
                }
              }
            }
          ]
        }
      }
    },
    
  ],
  as: "store1"
}
},
{
$match: {
  $expr: {
    "$in": [
      "$id",
      {
        "$map": {
          // your payload from client here
          "input": [
            "63da2f1f7662144569f78ddd",
            "63da2f1f7662144569f78ddb",
            "63da2f1f7662144569f78dda"
          ],
          "as": "id",
          "in": {
            "$toObjectId": "$$id"
          }
        }
      }
    ]
  }
 }
},
{
$project: {
  _id: 0,
  
}
}
])

expected out put

[
{
"id": ObjectId("63da2f1f7662144569f78ddd"),
"name": "bat",
"price": 56,
 },
{
"id": ObjectId("63da2f1f7662144569f78ddb"),
"name": "cap",
"price": 100,
 },
{
"id": ObjectId("63da2f1f7662144569f78dda"),
"name": "red",
"price": 50,
  },
{
    "id": ObjectId("63da2f1f7662144569f78dd6"),
    "name": "bat",
    "price": 56
  },
  {
    "id": ObjectId("63da2f1f7662144569f78dd7"),
    "name": "ball",
    "price": 58
  }
 ]

Here is the Mongodb playground link https://mongoplayground.net/p/sMOizT0s2sC. Where I mentioned the demo data format.

Answer 1

you can use $unionWith operator (mongo version 4.4 onwards) to union collections. The pipelines are essentially the same as you used

db.store.aggregate([
  {
    $match: {
      $expr: {
        $in: [
          "$id",
          {
            $map: {
              input: [ "63da2f1f7662144569f78ddd", "63da2f1f7662144569f78ddb", "63da2f1f7662144569f78dda" ],
              as: "id",
              in: { $toObjectId: "$$id" }
            }
          }
        ]
      }
    }
  },
  {
    $unionWith: {
      coll: "store1",
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$id",
                {
                  $map: {
                    input: [ "63da2f1f7662144569f78dd6", "63da2f1f7662144569f78dd7" ],
                    as: "id",
                    in: { $toObjectId: "$$id" }
                  }
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    $project: { _id: 0 }
  }
])

playground