文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >从Mysql转换到MangoDb

问从Mysql转换到MangoDb
EN

Stack Overflow用户
提问于 2022-04-29 20:38:04
回答 2查看 35关注 0票数 1

我有两张桌子要参加。表：users，rooms。

代码语言：javascript
复制
use myDB;
create table users(
userId char(12),
pseudo varchar(24)
);

create table rooms (
    roomId char(24),
    userIdFrom char(12),
    userIdTo char(12)
);

insert into users values('000000000001','A');
insert into users values('000000000002','B');
insert into users values('000000000003','C');
insert into users values('000000000004','D');
insert into users values('000000000005','E');
insert into users values('000000000006','F');
insert into users values('000000000007','G');
insert into users values('000000000008','H');

insert into rooms values('000000000001000000000002','000000000001','000000000002');
insert into rooms values('000000000001000000000003','000000000001','000000000003');
insert into rooms values('000000000008000000000001','000000000008','000000000001');

我的问题是：

代码语言：javascript
复制
use myDB;
select u.userId, u.pseudo, r.roomId, r.userIdFrom, r.userIdTo from users u 
inner join rooms r on (r.userIdFrom = u.userId or r.userIdTo = u.userId)
where userId <> '000000000001';

结果如下：

代码语言：javascript
复制
# userId, pseudo, roomId, userIdFrom, userIdTo
'000000000002', 'B', '000000000001000000000002', '000000000001', '000000000002'
'000000000003', 'C', '000000000001000000000003', '000000000001', '000000000003'
'000000000008', 'H', '000000000008000000000001', '000000000008', '000000000001'

现在，我希望这个查询在mongoDB中，

我试过：userId in users = "000000000001"

代码语言：javascript
复制
    db.users.aggregate([
{
    $match: {
      "userId": { $nin: ["000000000001"] }
    }
  },
       {
          $lookup: {
             from: "rooms",
             let: {
                "userId": "000000000001",
                "userIdFrom": "$userId",
                "userIdTo": "$userId"
             },
             pipeline: [
                {
                   $match: {
                      $expr: {
                         $or: [
                            {
                               $eq: [
                                  "$userIdFrom", "$$userId"
                               ]
                            },
                            {
                               $eq: [
                                  "$userIdTo", "$$userId" 
                               ]
                            }
                         ]
                      }
                   }
                }
             ],
             as: "result"
          },
       },
    ]
).pretty()

但结果并不像预期的那样，

代码语言：javascript
复制
{
        "_id" : ObjectId("626af4de5e41275250542c79"),
        "pseudo" : "B",
        "userId" : "000000000002",
        "password" : "$2b$10$iv1aEVd424yVqo5kXEbFnOBPe5FYKIKlbI5N1EEIvhiRL43b5fYku",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c7b"),
        "pseudo" : "E",
        "userId" : "000000000005",
        "password" : "$2b$10$BC3pfhkKKTRqPnU4X7hsjOWlMBpmaojTS62pGeDwuh5nFc/l5z8Gy",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c7d"),
        "pseudo" : "G",
        "userId" : "000000000007",
        "password" : "$2b$10$0G5sqXrLtp0f1wjZuqkI8O7WNAfu2K.FE.dUPsBP6OpEBvgrZd1u2",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c7f"),
        "pseudo" : "F",
        "userId" : "000000000006",
        "password" : "$2b$10$9.vBd3k1wFYfopDKzuwQuuWCBCuTPMLEd0aMuZpI6K3e37RbUPkDW",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c81"),
        "pseudo" : "H",
        "userId" : "000000000008",
        "password" : "$2b$10$8oNiWJi9y8j6UV5/sf6yvenXdvqii0VPaauL/2Y7QfMbDs9ffEHhC",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c77"),
        "pseudo" : "C",
        "userId" : "000000000003",
        "password" : "$2b$10$MCIFV5ATx3DdkLotL6Mht.o22Cc13G6Ad7QkTQKaZuAZpKA5MXwky",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c83"),
        "pseudo" : "I",
        "userId" : "000000000009",
        "password" : "$2b$10$TtScWbrfV30K/poipAuYGu4pwPvRkzCSkzghqJWNDRfYW9XnQPtbO",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}
{
        "_id" : ObjectId("626af4de5e41275250542c75"),
        "pseudo" : "D",
        "userId" : "000000000004",
        "password" : "$2b$10$8ct5anRaSIG5FzqQZTlVM./pQV66GQ3w.kUBVRMcMAD76Hy2B55B2",
        "__v" : 0,
        "result" : [
                {
                        "_id" : ObjectId("626b04022bfebc8808114911"),
                        "roomId" : "000000000001000000000003",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000003",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b04022bfebc8808114913"),
                        "roomId" : "000000000001000000000002",
                        "userIdFrom" : "000000000001",
                        "userIdTo" : "000000000002",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("626b35562bfebc880811491e"),
                        "roomId" : "000000000008000000000001",
                        "userIdFrom" : "000000000008",
                        "userIdTo" : "000000000001",
                        "__v" : 0
                }
        ]
}

有人能帮我吗？

mongodb
mongodb-query
EN

回答 2

Stack Overflow用户

发布于 2022-04-29 23:58:47

编辑--我没有注意到原始<>中的<>。修好了！

我不太清楚您希望如何格式化输出，因此您可能希望修改我的后期阶段，但是有一种方法可以获得您想要的"$lookup"，等等。

注:这假设每个user的user只有一个结果，因为它只需要"$first" "$match"。如果可能或需要更多的rooms，则需要根据需要修改后期阶段和输出格式。

代码语言：javascript
复制
db.users.aggregate([
  { "$match": { "userId": { "$ne": "000000000001" } } },
  {
    "$lookup": {
      "from": "rooms",
      "let": { "userId": "$userId" },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$or": [
                { "$eq": [ "$userIdFrom", "$$userId" ] },
                { "$eq": [ "$userIdTo", "$$userId" ] }
              ]
            }
          }
        }
      ],
      "as": "roomData"
    }
  },
  { "$match": { "$expr": { "$gt": [ { "$size": "$roomData" }, 0 ] } } },
  {
    "$replaceWith": {
      "$mergeObjects": [
        "$$ROOT",
        { "$first": "$roomData" }
      ]
    }
  },
  { "$unset": [ "_id", "roomData" ] }
])

示例输出：

代码语言：javascript
复制
[
  {
    "pseudo": "B",
    "roomId": "000000000001000000000002",
    "userId": "000000000002",
    "userIdFrom": "000000000001",
    "userIdTo": "000000000002"
  },
  {
    "pseudo": "C",
    "roomId": "000000000001000000000003",
    "userId": "000000000003",
    "userIdFrom": "000000000001",
    "userIdTo": "000000000003"
  },
  {
    "pseudo": "H",
    "roomId": "000000000008000000000001",
    "userId": "000000000008",
    "userIdFrom": "000000000008",
    "userIdTo": "000000000001"
  }
]

在mongoplayground.net上试一试。

票数 1
EN

Stack Overflow用户

发布于 2022-04-30 02:42:47

解决方案如下：(对于node.js，将用户id替换为使用req.body.userId进行检索)

代码语言：javascript
复制
    db.rooms.aggregate(
{$match:
{$or:[
{userIdFrom: "000000000001"},
{userIdTo:"000000000001"},
]}
},
{
    $lookup: {
        from: "users",
        let: {
            "userId": "000000000001",
            "userIdFrom": "$userIdFrom",
            "userIdTo": "$userIdTo"
        },
        pipeline: [
        {
            $match: {
                $expr: {
                    $and: [
                    { $ne: [ "$userId", "$$userId"]},
                    { $or:  [ { $eq: [ "$userId", "$$userIdTo" ] }, { $eq: [ "$userId", "$$userIdFrom" ] } ] }
                ]

            }
        }
    }
    ],
    as: "user"
},
},

).pretty()
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/72063451

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档