NodeJS 如何将三个mongodb查询合并为一个查询

hkmswyz6 于 2023-06-22 发布在 Node.js

关注(0)|答案(1)|浏览(122)

我有以下三个收藏：

db={
  "category": [
    {
      "_id": "64841d0280a7a74a913b7935",
      "name": "Cat A",
      
    },
    {
      "_id": "6492fd9251feab2f2485a581",
      "name": "Cat B",
      
    }
  ],
  "subcategory": [
    {
      "_id": "64841e2380a7a74a913b7943",
      "name": "Sub Cat A",
      "categoryId": "64841d0280a7a74a913b7935",
      
    },
    {
      "_id": "64871d65c82f0e2cb882e4de",
      "name": "Sub Cat B",
      "categoryId": "64841d0280a7a74a913b7935",
      
    },
    {
      "_id": "6492d31581c81e578307c1f1",
      "name": "Sub Cat C",
      "categoryId": "6492fd9251feab2f2485a581",
      
    }
  ],
  "store": [
    {
      "_id": 1,
      "name": "Store A",
      "categoryId": "6492fd9251feab2f2485a581",
      "subCategoryId": "64871d65c82f0e2cb882e4de",
      
    },
    {
      "_id": 2,
      "name": "Store B",
      "categoryId": "64841d0280a7a74a913b7935",
      "subCategoryId": "64871d65c82f0e2cb882e4de",
      
    },
    {
      "_id": 3,
      "name": "Store C",
      "categoryId": "64841d0280a7a74a913b7935",
      "subCategoryId": "64841e2380a7a74a913b7943",
      
    },
    {
      "_id": 4,
      "name": "Store D",
      "categoryId": "6492fd9251feab2f2485a581",
      "subCategoryId": "64841e2380a7a74a913b7943",
      
    },
    {
      "_id": 5,
      "name": "Store E",
      "categoryId": "64841d0280a7a74a913b7935",
      "subCatgeoryId": "6492d31581c81e578307c1f1"
    },
    
  ]
}

目前我正在使用三个不同的mongodb查询来获取数据，我如何合并三个查询来获得下面的响应。

{
  "category": [
    {
      "_id": "64841d0280a7a74a913b7935",
      "name": "Cat A",
      "subcategory" : [
        {
          "_id": "64841e2380a7a74a913b7943",
          "name": "Sub Cat A",
          "categoryId": {
            "_id": "64841d0280a7a74a913b7935",
            "name": "Cat A",
          },
          
        },
        {
          "_id": "64871d65c82f0e2cb882e4de",
          "name": "Sub Cat B",
          "categoryId": {
            "_id": "64841d0280a7a74a913b7935",
            "name": "Cat A",
          }
          
        }
      ],
      "store": [
        {
            "_id": 2,
            "name": "Store B",
            "categoryId": "categoryId": {
              "_id": "64841d0280a7a74a913b7935",
              "name": "Cat A",
            }
            "subCategoryId": {
              "_id": "64871d65c82f0e2cb882e4de",
              "name": "Sub Cat B",
            },
            
          },
          {
            "_id": 3,
            "name": "Store C",
            "categoryId": "categoryId": {
              "_id": "64841d0280a7a74a913b7935",
              "name": "Cat A",
            }
            "subCategoryId": {
              "_id": "64871d65c82f0e2cb882e4de",
              "name": "Sub Cat B",
            }
            
          },
          {
            "_id": 5,
            "name": "Store E",
            "categoryId": "categoryId": {
              "_id": "64841d0280a7a74a913b7935",
              "name": "Cat A",
            }
            "subCategoryId": {
              "_id": "64871d65c82f0e2cb882e4de",
              "name": "Sub Cat B",
            }
          },
        ]
    },
    {
      "_id": "6492fd9251feab2f2485a581",
      "name": "Cat B",
      "subcategory": [
        {
          "_id": "6492d31581c81e578307c1f1",
          "name": "Sub Cat C",
          "categoryId": {
            "_id": "6492fd9251feab2f2485a581",
            "name": "Cat B",
          },
        }
      ],
      "store": [
        {
          "_id": 1,
          "name": "Store A",
          "categoryId": {
            "_id": "6492fd9251feab2f2485a581",
            "name": "Cat B",
          },
          "subCategoryId": {
            "_id": "64841e2380a7a74a913b7943",
            "name": "Sub Cat A",
          },
          
        },
        {
          "_id": 4,
          "name": "Store D",
          "categoryId": {
            "_id": "6492fd9251feab2f2485a581",
            "name": "Cat B",
          },
          "subCategoryId": {
            "_id": "64841e2380a7a74a913b7943",
            "name": "Sub Cat A",
          },
        },
      ]
    }
  ]
}

到目前为止，我将第一个查询的响应保存在一个变量中，然后使用Map迭代器从第二个集合中查询数据，依此类推。

当前查询：

data_1 = await schema_1.find()
data_1.map((doc) => {
  data_2 = await schema_2.find({ categoryId: doc._id })
})
data_2.map((doc) => {
  data_3 = await schema_2.find({ subCategoryId: doc._id })
})

node.js

来源：https://stackoverflow.com/questions/76525905/how-can-i-combine-three-mongodb-query-into-single-query

1条答案

按热度按时间

g6ll5ycj1#

一个双$lookup将产生您想要的多个文档，每个category一个。结果还避免了在所需的目标输出中复制父（包含）结构数据：

db.fooCat.aggregate([
    {$lookup: {"from": "fooSubCat",
               let: { cid: "$_id" },
               pipeline: [
                   {$match: {$expr: {$eq: [ "$categoryId", "$$cid" ]} }},
               ],
               as: "subcategory"
              }},

    {$lookup: {"from": "fooStore",
               let: { cid: "$_id" },
               pipeline: [
                   {$match: {$expr: {$eq: [ "$categoryId", "$$cid" ]} }},
               ],
               as: "store"
              }}
]);

{
  _id: '64841d0280a7a74a913b7935',
  name: 'Cat A',
  subcategory: [
    {
      _id: '64841e2380a7a74a913b7943',
      name: 'Sub Cat A',
      categoryId: '64841d0280a7a74a913b7935'
    },
    {
      _id: '64871d65c82f0e2cb882e4de',
      name: 'Sub Cat B',
      categoryId: '64841d0280a7a74a913b7935'
    }
  ],
  store: [
    {
      _id: 2,
      name: 'Store B',
      categoryId: '64841d0280a7a74a913b7935',
      subCategoryId: '64871d65c82f0e2cb882e4de'
    },
    {
      _id: 3,
      name: 'Store C',
      categoryId: '64841d0280a7a74a913b7935',
      subCategoryId: '64841e2380a7a74a913b7943'
    },
    {
      _id: 5,
      name: 'Store E',
      categoryId: '64841d0280a7a74a913b7935',
      subCatgeoryId: '6492d31581c81e578307c1f1'
    }
  ]
}
{
  _id: '6492fd9251feab2f2485a581',
  name: 'Cat B',
  subcategory: [
    {
      _id: '6492d31581c81e578307c1f1',
      name: 'Sub Cat C',
      categoryId: '6492fd9251feab2f2485a581'
    }
  ],
  store: [
    {
      _id: 1,
      name: 'Store A',
      categoryId: '6492fd9251feab2f2485a581',
      subCategoryId: '64871d65c82f0e2cb882e4de'
    },
    {
      _id: 4,
      name: 'Store D',
      categoryId: '6492fd9251feab2f2485a581',
      subCategoryId: '64841e2380a7a74a913b7943'
    }
  ]
}

如果你真的想要一个带有category数组的文档，那么在第二个$lookup之后添加这个：

{$group: {'_id':null, category: {$push: '$$ROOT'}}}

替代方法

由于store集合似乎包含了所有category和subcategory的信息，如果你可以“信任它”并且不需要其他集合的其他信息，并且可以接受分配给_id的类别值，那么你可以完全绕过lookup，简单地使用双重$group-and-push技巧“改革”存储数据：

c = db.fooStore.aggregate([
    {$group: {_id: {cid:'$categoryId',scid:'$subCategoryId'}, store: {$push: {_id:'$_id',name:'$name'}} }}

    ,{$group: {_id: '$_id.cid', subcat: {$push: '$_id.scid'}, store: {$push: '$store'}}}

    /*                                                                                                      
    At this point 'store' is an array of arrays, e.g.                                                       
                                                                                                            
  store: [                                                                                                  
    [ { _id: 5, name: 'Store E'},                                                                           
      { _id: 6, name: 'Store F'}                                                                            
    ],                                                                                                      
    [ { _id: 3, name: 'Store C'} ],                                                                         
    [ { _id: 2, name: 'Store B'} ]                                                                          
  ]                                                                                                         
                                                                                                            
  We wish to simplify this and have just a single array of store info e.g.                                  
  store: [                                                                                                  
    { _id: 5, name: 'Store E'},                                                                             
    { _id: 6, name: 'Store F'},                                                                             
    { _id: 3, name: 'Store C'}                                                                              
    { _id: 2, name: 'Store B'}                                                                              
  ]                                                                                                         
                                                                                                            
  We use the $reduce trick below to do so:                                                                  
    */
    ,{$addFields: {
        store: {$reduce: {
                     input: "$store",
                     initialValue:[],
                         in:{$concatArrays: [ "$$value", "$$this"]}
                 }}
                }}

]);

这产生：

{
  _id: '64841d0280a7a74a913b7935',
  subcat: [
    '64871d65c82f0e2cb882e4de',
    '64841e2380a7a74a913b7943',
    '6492d31581c81e578307c1f1'
  ],
  store: [
    {
      _id: 2,
      name: 'Store B'
    },
    {
      _id: 3,
      name: 'Store C'
    },
    {
      _id: 5,
      name: 'Store E'
    },
    {
      _id: 6,
      name: 'Store F'
    }
  ]
}
{
  _id: '6492fd9251feab2f2485a581',
  subcat: [
    '64841e2380a7a74a913b7943',
    '64871d65c82f0e2cb882e4de'
  ],
  store: [
    {
      _id: 4,
      name: 'Store D'
    },
    {
      _id: 1,
      name: 'Store A'
    }
  ]
}

赞(0）回复(0）举报 2023-06-22

我来回答

NodeJS 如何将三个mongodb查询合并为一个查询

1条答案

替代方法

相关问题

热门标签

最新问答