如何在MongoDB中合并查询结果、格式化并创建视图

Got it, let's tackle how to build that MongoDB view that matches your SQL UNION ALL logic. We've got two approaches depending on your MongoDB version, both tailored to produce the unified document format you need.

Solution for MongoDB 4.4+ (Recommended) #

MongoDB 4.4 added the $unionWith operator, which directly mirrors SQL's UNION ALL behavior by merging results from separate pipelines. This is the cleanest way to build your view:

db.createView(
  "sessionNatView", // Name of your new view
  "sessions",       // Source collection to query
  [
    // Handle SNAT records, set unused DNAT fields to null
    {
      $unionWith: {
        coll: "sessions",
        pipeline: [
          { $match: { n: "snat" } },
          {
            $project: {
              _id: 0,
              sourceIp: "$si",
              sourcePort: "$sp",
              trandisp: "$n",
              sourceNatIp: "$tsi",
              sourceNatPort: "$tsp",
              destinationNatIp: null,
              destinationNatPort: null,
              destIp: "$di",
              destPort: "$dp"
            }
          }
        ]
      }
    },
    // Handle DNAT records, set unused SNAT fields to null
    {
      $unionWith: {
        coll: "sessions",
        pipeline: [
          { $match: { n: "dnat" } },
          {
            $project: {
              _id: 0,
              sourceIp: "$si",
              sourcePort: "$sp",
              trandisp: "$n",
              sourceNatIp: null,
              sourceNatPort: null,
              destinationNatIp: "$tdi",
              destinationNatPort: "$tdp",
              destIp: "$di",
              destPort: "$dp"
            }
          }
        ]
      }
    }
  ]
)

Breakdown: #

• db.createView() creates a persistent view that runs the defined pipeline every time you query it.
• Each $unionWith block targets either snat or dnat records, maps the original fields to your desired names, and explicitly sets unused NAT fields to null (just like your SQL query).
• The final view returns all records in the unified format you specified.

Solution for MongoDB <4.4 #

If you're on an older MongoDB version, use $facet to run both queries in parallel, then combine and flatten the results:

db.createView(
  "sessionNatView",
  "sessions",
  [
    // Run SNAT and DNAT queries side-by-side
    {
      $facet: {
        snatDocs: [
          { $match: { n: "snat" } },
          {
            $project: {
              _id: 0,
              sourceIp: "$si",
              sourcePort: "$sp",
              trandisp: "$n",
              sourceNatIp: "$tsi",
              sourceNatPort: "$tsp",
              destinationNatIp: null,
              destinationNatPort: null,
              destIp: "$di",
              destPort: "$dp"
            }
          }
        ],
        dnatDocs: [
          { $match: { n: "dnat" } },
          {
            $project: {
              _id: 0,
              sourceIp: "$si",
              sourcePort: "$sp",
              trandisp: "$n",
              sourceNatIp: null,
              sourceNatPort: null,
              destinationNatIp: "$tdi",
              destinationNatPort: "$tdp",
              destIp: "$di",
              destPort: "$dp"
            }
          }
        ]
      }
    },
    // Merge the two result arrays into one
    { $project: { combined: { $concatArrays: ["$snatDocs", "$dnatDocs"] } } },
    // Convert the array into individual documents
    { $unwind: "$combined" },
    // Promote each sub-document to the root level
    { $replaceRoot: { newRoot: "$combined" } }
  ]
)

Test the View #

Once the view is created, query it like a regular collection:

db.sessionNatView.find()

You'll get exactly the output you want:

{ "sourceIp" : "10.131.6.79", "sourcePort" : 36158, "trandisp" : "snat", "sourceNatIp" : "194.230.159.203", "sourceNatPort" : 36158, "destinationNatIp" : null, "destinationNatPort" : null, "destIp" : "205.185.208.165", "destPort" : 443 }
{ "sourceIp" : "10.128.103.115", "sourcePort" : 56261, "trandisp" : "dnat", "sourceNatIp" : null, "sourceNatPort" : null, "destinationNatIp" : "194.230.159.107", "destinationNatPort" : 56261, "destIp" : "8.253.207.243", "destPort" : 443 }

内容的提问来源于stack exchange，提问作者Wernfried Domscheit