时间戳

首页 > 解决方案 > 如何在 MongoDB 中合并结果并重新格式化

mongodb - 如何在 MongoDB 中合并结果并重新格式化

问题描述

我有这样的文件：

{
    "si" : "10.131.6.79",
    "sp" : 36158,
    "n" : "snat",
    "tsi" : "194.230.159.203",
    "tsp" : 36158,
    "di" : "205.185.208.165",
    "dp" : 443
}
{
    "si" : "10.128.103.115",
    "sp" : 56261,
    "n" : "dnat",
    "tdi" : "194.230.159.107",
    "tdp" : 56261,
    "di" : "8.253.207.243",
    "dp" : 443
}

我喜欢查询哪个连接了几个匹配的结果。期望的结果是

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

原则上我用这个命令管理它：

{ $concatArrays: [ 
db.sessions.aggregate([
    {$match:{n:"snat"}},
    {$project : {
      _id : 0,sourceIp : "$si",sourcePort : "$sp",sourceNatIp : "$tsi",sourceNatPort : "$tsp", destIp : "$di",destPort : "$dp", trandisp: "$n"
    }}
  ]),
db.sessions.aggregate([
    {$match:{n:"dnat"}},
    {$project : {
      _id : 0,sourceIp : "$si",sourcePort : "$sp",destinationNatIp : "$tdi",destinationNatPort : "$tdp", destIp : "$di",destPort : "$dp" ,trandisp: "$n"
    }}
  ])
 ] }

但我需要它作为查询结果，即我喜欢创建一个视图。

在 SQL 中我会写

select 
    si as SourceIp, sp as SourcePort, n as transdisp, 
    tsi as SourceNatIp, tsp as SouceNatPort, 
    null as DestinationNatIp, null as DestinationNatPort
from sessions
where n = 'snat'
union all
select 
    si as SourceIp, sp as SourcePort, n as transdisp, 
    null as SourceNatIp, null as SouceNatPort, 
    tdi as DestinationNatIp, tdp as DestinationNatPort
from sessions
where n = 'dnat';

标签： mongodbaggregation-framework

解决方案

您可以尝试查看$facet运算符，它允许您运行多个单独的查询并将结果作为单个文档获取。然后你可以运行$concatArrays、$unwind和$replaceRoot来组合和提升你的查询结果。

db.sessions.aggregate([
    {
        $facet: {
            q1: [
                { $match:{n:"snat"} },
                { $project : {
                _id : 0,sourceIp : "$si",sourcePort : "$sp",sourceNatIp : "$tsi",sourceNatPort : "$tsp", destIp : "$di",destPort : "$dp", trandisp: "$n"
                }}
            ],
            q2: [
                { $match:{n:"dnat"}},
                { $project : {
                _id : 0,sourceIp : "$si",sourcePort : "$sp",destinationNatIp : "$tdi",destinationNatPort : "$tdp", destIp : "$di",destPort : "$dp" ,trandisp: "$n"
                }}
            ]
        }
    },
    {
        $project: {
            all: {
                $concatArrays: [ "$q1", "$q2" ]
            }
        }
    },
    {
        $unwind: "$all"
    },
    {
        $replaceRoot: {
            newRoot: "$all"
        }
    }
])

蒙戈游乐场

推荐阅读