MongoDB -使用嵌套数组作为连接键的聚合(连接两个集合)

qyuhtwio  于 2023-05-17  发布在  Go
关注(0)|答案(3)|浏览(161)

我有两个收藏。（更新）
我需要加入它们并只返回几个字段。据我所知，我应该使用聚合和投影特性。我试着找例子，但是找不到对的，在我的例子中外键是放在数组中的嵌套字段中的。
这对我来说并不容易，我是新手，我无法战胜这个MongoDB查询。
在下面的代码块中，我编写了简化的等效模型（doc1，doc2）和预期结果。
有人有什么想法吗？

db.doc1.deleteMany({});
db.doc2.deleteMany({});

// tow related documents

db.doc1.insert( [
    { 
    "version" : 123456,
    "doc" : {
      "code":"A1",
      "name":"some document A1",
      "doc2CodeArray":[
          {"code":"B01"},
          {"code":"B02"},
          {"code":"B03"},
          {"code":"B04"},
          {"code":"B05"},
          {"code":"B06"}
          ]
      }
    },
    { 
    "version" : 123457,
    "doc" : {
      "code":"A2",
      "name":"some document A2",
      "doc2CodeArray":[
          {"code":"B07"},
          {"code":"B08"},
          {"code":"B09"},
          {"code":"B10"},
          {"code":"B11"},
          {"code":"B12"}
          ]
      }
    },
    { 
    "version" : 123457,
    "doc" : {
      "code":"A2",
      "name":"some document A2",
      "doc2CodeArray":null
      }
    }   

]);

db.doc2.insert( [
    {
        "version" : 567890,
        "doc" : { 
          "code":"B01", 
          "valueArray":[{"valueType":"int","valueData":"1"}],
          "doc2Type":{"code":"C1"}
        } 
    },
    {"version" : 567890,"doc" : { "code":"B02", "valueArray":[{"valueType":"int","valueData":"2","isDefault":false}],"doc2Type":{"code":"C2","addInfo":"1234567890"}}},
    {"version" : 567890,"doc" : { "code":"B03", "valueArray":[{"valueType":"int","valueData":"3","isDefault":false}],"doc2Type":{"code":"C3","addInfo":"1234567890"}}},
    {"version" : 567890,"doc" : { "code":"B04", "valueArray":[{"valueType":"int","valueData":"4","isDefault":false}],"doc2Type":{"code":"C4","addInfo":"1234567890"}}},
    {"version" : 567890,"doc" : { "code":"B05", "valueArray":[{"valueType":"int","valueData":"5","isDefault":false}],"doc2Type":{"code":"C5","addInfo":"1234567890"}}},
    {"version" : 567890,"doc" : { "code":"B06", "valueArray":[{"valueType":"int","valueData":"6","isDefault":false},
                                                              {"valueType":"str","valueData":"F","isDefault":false}],"doc2Type":{"code":"C6","addInfo":"1234567890"}}},
    
    {"version" : 567890,"doc" : { "code":"B07", "valueArray":[{"valueType":"int","valueData":"1","isDefault":false}],"doc2Type":{"code":"C1","addInfo":"1234567890"}}},
    {"version" : 567890,"doc" : { "code":"B08", "valueArray":[{"valueType":"int","valueData":"2","isDefault":false}],"doc2Type":{"code":"C2","addInfo":"1234567890"}}},
    {"version" : 567890,"doc" : { "code":"B09", "valueArray":[{"valueType":"int","valueData":"3","isDefault":false}],"doc2Type":{"code":"C3","addInfo":"1234567890"}}},
    {"version" : 567890,"doc" : { "code":"B10", "valueArray":[{"valueType":"int","valueData":"4","isDefault":false}],"doc2Type":{"code":"C4","addInfo":"1234567890"}}},
    {"version" : 567890,"doc" : { "code":"B11", "valueArray":[{"valueType":"int","valueData":"5","isDefault":false}],"doc2Type":{"code":"C5","addInfo":"1234567890"}}},
    {"version" : 567890,"doc" : { "code":"B12", "valueArray":[{"valueType":"int","valueData":"6","isDefault":false}],"doc2Type":{"code":"C6","addInfo":"1234567890"}}},    
    ]);

我想要的结果

I need join collections and select only few props
  {
      "doc":{code:"A1"},
      "doc2Items":[
        {"code":"C2", "value":{"value":"3"}},
        {"code":"C3", "value":{"value":"4"}},
      ]
  },
  {
      "doc":{code:"A2"},
      "doc2Items":[
        {"code":"C2", "value":{"value":"2"}},
        {"code":"C3", "value":{"value":"3"}},
        {"code":"C4", "value":{"value":"4"}},
      ]
  }
mongodb

3条答案

按热度按时间
uurity8g

uurity8g1#

https://mongoplayground.net/p/1-1SU8SgbTQ

db.doc1.aggregate([
  {
    $lookup: {
      from: "doc2",
      localField: "doc.doc2CodeArray.code",
      foreignField: "doc.code",
      as: "doc.doc2Items"
    }
  },
  {
    $replaceRoot: {
      newRoot: "$doc"
    }
  },
  {
    $addFields: {
      "doc2Items": {
        $map: {
          input: "$doc2Items",
          in: {
            doc2TypeCode: "$$this.doc.doc2Type.code",
            doc2ValueArray: "$$this.doc.valueArray"
          }
        }
      }
    }
  },
  {
    $unset: [
      "doc2CodeArray"
    ]
  }
])
赞(0）分享  回复(0）举报  2023-05-17
ghhaqwfi

ghhaqwfi2#

  1. $lookup-将doc1与doc2连接。
    管道：
    1.1. $match-匹配的doc.code（来自doc2）在doc.doc2CodeArray.code数组（来自doc1）中。
    1.2. $project-修饰doc2Items中要返回的输出文档。
  2. $project-修饰输出文档。
db.doc1.aggregate([
  {
    $lookup: {
      from: "doc2",
      let: {
        doc2CodeArray: "$doc.doc2CodeArray.code"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$doc.code",
                "$$doc2CodeArray"
              ]
            }
          }
        },
        {
          $project: {
            _id: 0,
            "doc2TypeCode": "$doc.doc2Type.code",
            "doc2ValueArray": "$doc.valueArray"
          }
        }
      ],
      as: "doc2Items"
    }
  },
  {
    $project: {
      _id: 0,
      "doc1Code": "$doc.code",
      "doc1Name": "$doc.name",
      "doc2Items": "$doc2Items"
    }
  }
])

Sample Mongo Playground

赞(0）分享  回复(0）举报  2023-05-17
osh3o9ms

osh3o9ms3#

最近几天我埋头阅读。我得到了一个可以接受的结果。
我更新了我的问题与更相关的数据的例子，我已经给了我的解决方案在下面的例子。

db.doc1.aggregate([
    {
        $match: {
            // filter by doc.code prefix
            "doc.code": { '$regex': 'A', '$options': 'i' }, "doc.doc2CodeArray": { $ne: null }
            //"doc.code": "A1"
        }
    },
    {
        $lookup: { // join doc1 & doc2
            from: "doc2",
            localField: "doc.doc2CodeArray.code",
            foreignField: "doc.code",
            as: "doc2Items"
        }
    },
    {
        $project: {
            _id: 0,
            "doc.code": 1,
            "doc2Items": { // items from doc2
                $map: {  // I need not all props from doc2 - do $map
                    "input": {
                        $filter: { // I need items only with 3 codes C2, C3, C4 - do $filter
                            "input": "$doc2Items",
                            "as": "d0",
                            "cond": { $or:[
                                { "$eq": ["$$d0.doc.doc2Type.code", "C2"] },
                                { "$eq": ["$$d0.doc.doc2Type.code", "C3"] },
                                { "$eq": ["$$d0.doc.doc2Type.code", "C4"] },
                            ]}
                        },
                    },
                    "as": "d",
                    "in": {
                        "code": "$$d.doc.doc2Type.code",
                        "value": {
                            $arrayElemAt: [ // I need only first record of nested array
                                {
                                    $map: { // I need not all props of first record of nested array
                                        "input": "$$d.doc.valueArray",
                                        "as": "d2",
                                        "in": {
                                            "value": "$$d2.valueData"
                                        }
                                    }
                                }
                                , 0]
                        },
                    }
                }// map
            }
        }// project
    }
])

现在我读取所需数据的速度比以前快了两倍。我想我开始理解mongo db的查询哲学了。
谢谢大家的回答和提示！“永顺”和“qtxo”帮助我找到了正确的解决方案。

赞(0）分享  回复(0）举报  2023-05-17
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com