MongoDB $lookup函数实现两个表的关联查询+筛选+取特定值

最新推荐文章于 2024-06-11 22:33:48 发布

十方天士

最新推荐文章于 2024-06-11 22:33:48 发布

阅读量1.8k

点赞数 1

分类专栏： MongoDB 文章标签： mongodb

本文链接：https://blog.csdn.net/hunagzheng123456/article/details/113940941

版权

MongoDB 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

实现Mysql里inner join的效果

1、两张表如下

db.getCollection('book')；

db.getCollection("book").insert( {
    _id: ObjectId("5ec624b405ce835be78f258e"),
    channel: "fwpt",
    channelGoodsId: "30586",
    websiteId: NumberInt("1"),
    bookKinds: [
        "jd"
    ],
    bookState: NumberInt("1"),
    createTime: NumberLong("1590043829524"),
    goods: {
        channel: "fwpt",
        channelGoodsId: "30586",
        bookClass: "1",
        bookSubclass: "11",
        brandId: NumberInt("0"),
        brandName: "",
        choose: false,
        circulate: NumberInt("0"),
        courseType: "2",
        createStoreId: NumberInt("0"),
        createTime: "2020-05-21 11:53:09",
        electronBookId: "",
        goodsDetail: "",
        goodsKind: NumberInt("151"),
        goodsKindLevel: NumberInt("0"),
        goodsKindName: "",
        goodsMainPicUrl: "",
        goodsName: "四维阅读·茶馆",
        goodsPicUrls: "",
        goodsStates: NumberInt("1"),
        goodsSubject: "001",
        goodsTag: "",
        goodsTinyPicUrl: "",
        goodsType: NumberInt("1"),
        goodsTypeName: "",
        grades: [
            NumberInt("10")
        ],
        id: NumberInt("30586"),
        irreversible: false,
        marketPrice: 0,
        postFee: 0,
        pressName: "",
        publishType: NumberInt("0"),
        publisherId: NumberInt("127"),
        publisherName: "岳麓书社",
        qrCode: "",
        salePrice: 97.67,
        serviceTag: "",
        source: NumberInt("1"),
        status: "activity",
        stockHandle: "4",
        stockNumber: NumberInt("0"),
        storeDiscountRate: NumberInt("0"),
        storeGoodsStatus: NumberInt("0"),
        storeId: NumberInt("0"),
        storeOriginalPrice: NumberInt("0"),
        storePresent: false,
        storeSalePrice: NumberInt("0"),
        studyStage: "3;",
        subscriptionCode: "20200521060",
        supplierId: NumberInt("2"),
        supplierName: ""
    },
    wxMaQrCodeUrl: "https://res.xh-er.com/readingamuse/wxma_qrcode/20201126/1331884478359977984.jpg",
    book: {
        characterIntro: "",
        authorName: "老舍",
        authorIntro: "老舍（1899—1966），满族，原名舒庆春，字舍予。一生著作颇丰，在小说、诗歌、话剧创作及文艺理论方面均有建树。代表作有小说《老张的哲学》《骆驼祥子》《四世同堂》《离婚》《月牙儿》和剧本《茶馆》《龙须沟》等。\n",
        bookMainPicUrl: "https://res.xh-er.com/yuedou/book/bookMainPicUrl/2021/0106/160990221003008.jpg",
        briefIntro: "《茶馆》与《龙须沟》是当代中国话剧经典。《茶馆》以老北京一家裕泰茶馆的兴衰变迁为背景，向人们展现了近半个世纪的中国社会风云，昭示了中华民族在通往现代社会过程中所承受的坎坷而痛苦的命运。《龙须沟》以对比鲜明、真实质朴的手法，通过对居住于北京一条臭水沟旁的下层劳苦者新旧时代生活巨变的成功描写，为新社会高唱了一曲热情洋溢的颂歌。",
        authorPhoto: "",
        detailIntro: "<p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><strong><span style=\"font-family: 宋体;font-size: 14px\">1.ISBN：</span></strong><span style=\";font-family:宋体;font-size:14px\">978-7-5538-1</span><span style=\";font-family:宋体;font-size:14px\">445</span><span style=\";font-family:宋体;font-size:14px\">-</span><span style=\";font-family:宋体;font-size:14px\">2</span></p><p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><span style=\";font-family:宋体;font-size:14px\">&nbsp;</span></p><p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><strong><span style=\"font-family: 宋体;font-size: 14px\">2.书名：</span></strong><span style=\";font-family:宋体;font-size:14px\"><span style=\"font-family:宋体\">《</span></span><span style=\";font-family:宋体;font-size:14px\"><span style=\"font-family:宋体\">茶馆</span></span><span style=\";font-family:宋体;font-size:14px\"><span style=\"font-family:宋体\">》</span></span></p><p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><span style=\";font-family:宋体;font-size:14px\">&nbsp;</span></p><p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><strong><span style=\"font-family: 宋体;font-size: 14px\">3.定价：</span></strong><span style=\";font-family:宋体;font-size:14px\">27.00元</span></p><p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><span style=\";font-family:宋体;font-size:14px\">&nbsp;</span></p><p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><strong><span style=\"font-family: 宋体;font-size: 14px\">4.版次:</span></strong><span style=\";font-family:宋体;font-size:14px\"><span style=\"font-family:宋体\">第</span>1版</span></p><p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><span style=\";font-family:宋体;font-size:14px\">&nbsp;</span></p><p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><strong><span style=\"font-family: 宋体;font-size: 14px\">5.出版时间：</span></strong><span style=\";font-family:宋体;font-size:14px\">202</span><span style=\";font-family:宋体;font-size:14px\">1</span><span style=\";font-family:宋体;font-size:14px\"><span style=\"font-family:宋体\">年</span></span><span style=\";font-family:宋体;font-size:14px\">1</span><span style=\";font-family:宋体;font-size:14px\"><span style=\"font-family:宋体\">月</span></span></p><p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><span style=\";font-family:宋体;font-size:14px\">&nbsp;</span></p><p style=\"text-autospace:ideograph-numeric;text-align:justify;text-justify:inter-ideograph;line-height:16px\"><strong><span style=\"font-family: 宋体;font-size: 14px\">6.出版社：</span></strong><span style=\";font-family:宋体;font-size:14px\"><span style=\"font-family:宋体\">岳麓书社</span></span></p><p><br/></p>",
        bookTags: [
            "wx"
        ]
    },
    resource: {
        learningPlan: "",
        mindGuide: ""
    },
    readItems: [
        "VideoStudy",
        "AudioStudy"
    ]
} );

db.getCollection('book_course');

db.getCollection("book_course").insert( {
    _id: ObjectId("6010b973f69d7d1891215913"),
    bookId: "5ec624b405ce835be78f258e",
    cover: "https://res.xh-er.com/yuedou/book/courseVideo/2021/0127/161170875964084.jpg",
    video: "https://res.xh-er.com/yuedou/book/courseVideo/2021/0127/161170870092283.mp4",
    playTime: NumberInt("2728"),
    title: "第一课时 导读课：走进《茶馆》",
    briefIntro: "1.走进作者，了解作者创作的社会背景。 2.阅读舞台说明，获取相关的信息。 3.对比鉴赏，体会戏剧文化的博大精深。",
    grades: [
        NumberInt("10")
    ],
    studyStages: [
        NumberInt("3")
    ],
    link: "",
    mainLecturer: "黄金萍",
    lecturerIntro: "黄金萍，明德中学语文学科中心主任，长沙市蒋雁鸣语文名师工作室名师，长沙市卓越教师，湖南省中小学教师发展中心送培专家，2020年被评为湖南省特级教师。",
    fileSize: NumberInt("398440429"),
    orderNum: NumberInt("1")
} );
db.getCollection("book_course").insert( {
    _id: ObjectId("6010bab0ca27ab37be8d1a51"),
    bookId: "5ec624b405ce835be78f258e",
    cover: "https://res.xh-er.com/yuedou/book/courseVideo/2021/0127/161170910232390.jpg",
    video: "https://res.xh-er.com/yuedou/book/courseVideo/2021/0127/161170904600889.mp4",
    playTime: NumberInt("2621"),
    title: "第二课时 精读课：满纸寻常言，试解其中味 ——《茶馆》《龙须沟》的语言艺术",
    briefIntro: "1.阅读戏剧作品《茶馆》《龙须沟》，品味语言，建构言语经验，体会精彩语句的表现力，提高对戏剧文学语言的感受力。 2.感受作品中的思想和艺术魅力，发展想象力和创造力。 3.通过阅读和鉴赏戏剧作品，体会戏剧文化的博大精深、源远流长。",
    grades: [
        NumberInt("10")
    ],
    studyStages: [
        NumberInt("3")
    ],
    link: "",
    mainLecturer: "伍月娥",
    lecturerIntro: "伍月娥:长沙市明德中学教师，蒋雁鸣中学语文名师工作室优秀学员。获2019年湖南省中小学教师信息技术与学科深度融合在线集体备课大赛二等奖。\n",
    fileSize: NumberInt("383164066"),
    orderNum: NumberInt("2")
} );
db.getCollection("book_course").insert( {
    _id: ObjectId("6010bc90f69d7d1891215917"),
    bookId: "5ec624b405ce835be78f258e",
    cover: "https://res.xh-er.com/yuedou/book/courseVideo/2021/0127/161170955107499.jpg",
    video: "https://res.xh-er.com/yuedou/book/courseVideo/2021/0127/161170954999298.mp4",
    playTime: NumberInt("571"),
    title: "微课1：《茶馆》的结构艺术",
    briefIntro: "暂无",
    grades: [
        NumberInt("10")
    ],
    studyStages: [
        NumberInt("3")
    ],
    link: null,
    mainLecturer: "伍月娥",
    lecturerIntro: "伍月娥:长沙市明德中学教师，蒋雁鸣中学语文名师工作室优秀学员。获2019年湖南省中小学教师信息技术与学科深度融合在线集体备课大赛二等奖。",
    fileSize: NumberInt("11304239"),
    orderNum: NumberInt("3")
} );
db.getCollection("book_course").insert( {
    _id: ObjectId("6010bcb3ca27ab37be8d1a5a"),
    bookId: "5ec624b405ce835be78f258e",
    cover: "https://res.xh-er.com/yuedou/book/courseVideo/2021/0127/161170959843322.jpg",
    video: "https://res.xh-er.com/yuedou/book/courseVideo/2021/0127/161170959753421.mp4",
    playTime: NumberInt("505"),
    title: "微课2：《龙须沟》人物变化",
    briefIntro: "暂无",
    grades: [
        NumberInt("10")
    ],
    studyStages: [
        NumberInt("3")
    ],
    link: null,
    mainLecturer: "伍月娥",
    lecturerIntro: "伍月娥:长沙市明德中学教师，蒋雁鸣中学语文名师工作室优秀学员。获2019年湖南省中小学教师信息技术与学科深度融合在线集体备课大赛二等奖。",
    fileSize: NumberInt("9854681"),
    orderNum: NumberInt("4")
} );
db.getCollection("book_course").insert( {
    _id: ObjectId("6010bcd8f69d7d1891215918"),
    bookId: "5ec624b405ce835be78f258e",
    cover: "https://res.xh-er.com/yuedou/book/courseVideo/2021/0127/161170964212401.jpg",
    video: "https://res.xh-er.com/yuedou/book/courseVideo/2021/0127/161170963687200.mp4",
    playTime: NumberInt("532"),
    title: "微课3：《龙须沟》戏剧冲突",
    briefIntro: "暂无",
    grades: [
        NumberInt("10")
    ],
    studyStages: [
        NumberInt("3")
    ],
    link: null,
    mainLecturer: "伍月娥",
    lecturerIntro: "伍月娥:长沙市明德中学教师，蒋雁鸣中学语文名师工作室优秀学员。获2019年湖南省中小学教师信息技术与学科深度融合在线集体备课大赛二等奖。",
    fileSize: NumberInt("39752851"),
    orderNum: NumberInt("5")
} );

2、需求：查询书本所对应的视频标题

3、执行的sql语句

db.getCollection('book').aggregate([
{$match: {"goods.goodsName": {
        $in: ["四维阅读一年级下册","四维阅读·读读童谣和儿歌（一）","四维阅读·读读童谣和儿歌（二）","四维阅读二年级下册","四维阅读·七色花","四维阅读·愿望的实现","四维阅读三年级下册","四维阅读·中国古代寓言","四维阅读·伊索寓言","四维阅读四年级下册","四维阅读·十万个为什么","四维阅读·灰尘的旅行","四维阅读五年级下册","四维阅读·西游记(青少年版)","四维阅读·三国演义(青少年版)","四维阅读六年级下册","四维阅读·鲁滨逊漂流记","四维阅读·爱丽丝漫游奇境","四维阅读七年级下册","四维阅读·海底两万里","四维阅读·骆驼祥子","四维阅读八年级下册","四维阅读·傅雷家书","四维阅读·钢铁是怎样炼成的","四维阅读九年级下册","四维阅读·儒林外史","四维阅读·简·爱","四维阅读高一年级下册","四维阅读·红楼梦","四维阅读·茶馆","四维阅读高二年级下册","四维阅读·谈美书简","四维阅读·朱自清散文","四维阅读·莫泊桑中短篇小说选","四维阅读·欧·亨利短篇小说选"]
    }}},
  { "$project": {
      "_id": {
        "$toString": "$_id"
      },
			"name":"$goods.goodsName"
    }},
  { "$lookup": {
    "from": "book_course",
    "localField": "_id",
    "foreignField": "bookId",
    "as": "comments"
  }},
	{
		"$unwind":"$comments"
	},{ "$project": {
			"bookName":"$name",
			"bookId":"$_id",
			"book_course_name":"$comments.title"
    }}
])

其中 .aggregate管道类似java中的stream(),先聚合

{$match: {"goods.goodsName": {$in: ["四维阅读一年级下册","四维阅读·读读童谣和儿歌（一）"]}}}

先在book中过滤；

```
{ "$project": { "_id": {"$toString": "$_id"},"name":"$goods.goodsName"}}
```
在book表中_id 是objectId而book_course中booksId是字符串，在$lookup中不起作用所以需要转成一致的类型在这里就用$toString，并且获取书本名goodsName

{ "$lookup": {
    "from": "book_course",
    "localField": "_id",
    "foreignField": "bookId",
    "as": "comments"
  }}

两张表联合的关键；

```
{"$unwind":"$comments"}
```
作用是将查询的数组剥离出来，如下；

继续用$project过滤出需要的字段;

{ "$project": {
			"bookName":"$name",
			"bookId":"$_id",
			"book_course_name":"$comments.title"
    }}

4、效果

十方天士

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
MongoDB $lookup函数实现两个表的关联查询+筛选+取特定值

实现Mysql里inner join的效果1、两张表如下db.getCollection('book')；db.getCollection("book").insert( { _id: ObjectId("5ec624b405ce835be78f258e"), channel: "fwpt", channelGoodsId: "30586", websiteId: NumberInt("1"), bookKinds: [ "jd.
复制链接

扫一扫