Insert result of a Query using aggregation subquery as filter into new collection

Question

How can I implement this query in MongoDB

select * into NewTable 
from SalesInvoice where SalesID in 
   (select SalesID from SalesInvoice 
        group by CustomerID 
          having count(CustomerID)>1
    )

I could build the aggregate query, but couldn't find a way to make a new query and use the aggregate query as a filter and then insert the result into new collection.

Below is aggregate query:

db.SalesInvoice.aggregate([ {$group: {_id:"$request.CustomerID", count: {$sum:1}}},{$match: {count: {$gt:1}}}])

Answer 1

Think there are a few approaches to achieve:

Approach 1

1. $lookup - Left join with the self-collection by CustomerID and return a salesInvoices` array.

2. $match - Filter the document with the size of the salesInvoices array with more than 1.

3. $unset - Remove the salesInvoices array.

4. out - Export the result into the collection.

db.SalesInvoice.aggregate([
  {
    $lookup: {
      from: "SalesInvoice",
      let: {
        custID: "$CustomerID"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$CustomerID",
                "$$custID"
              ]
            }
          }
        }
      ],
      as: "salesInvoices"
    }
  },
  {
    $match: {
      $expr: {
        $gt: [
          {
            $size: "$salesInvoices"
          },
          1
        ]
      }
    }
  },
  {
    $unset: "salesInvoices"
  },
  {
    $out: "<Your new collection>"
  }
])

Demo Approach 1 @ Mongo Playground

---

Approach 2

1. $facet - Allow multiple aggregation pipelines to be run in a single query.

   1.1. salesInvoices - Return all documents from the salesInvoices collection.

   1.2. filterCustomers - Get the CustomerID that appeared in more than 1 document in the salesInvoices collection.

2. $unwind - Deconstruct the salesInvoices array.

3. $match - Filter the document by matching SalesInvoices.CustomerID.

4. $replaceWith - Replace the input document with SalesInvoices field.

5. out - Export the result into the collection.

db.SalesInvoice.aggregate([
  {
    $facet: {
      salesInvoices: [],
      filteredCustomers: [
        {
          $group: {
            _id: "$CustomerID",
            count: {
              $sum: 1
            }
          }
        },
        {
          $match: {
            count: {
              $gt: 1
            }
          }
        },
        {
          $project: {
            _id: 1
          }
        }
      ]
    }
  },
  {
    $unwind: "$salesInvoices"
  },
  {
    $match: {
      $expr: {
        $in: [
          "$salesInvoices.CustomerID",
          "$filteredCustomers._id"
        ]
      }
    }
  },
  {
    $replaceWith: "$salesInvoices"
  },
  {
    $out: "<Your new collection>"
  }
])

Demo Approach 2 @ Mongo Playground