Mongodb 通过 aggregate 对子文档进行筛选

最新推荐文章于 2024-05-14 12:00:00 发布

从未、淡定

最新推荐文章于 2024-05-14 12:00:00 发布

阅读量599

点赞数 1

分类专栏：前端领域 javascript 文章标签： mongodb 数据库 nosql

本文链接：https://blog.csdn.net/swe_ling/article/details/129114313

版权

前端领域同时被 2 个专栏收录

31 篇文章 0 订阅

订阅专栏

javascript

13 篇文章 0 订阅

订阅专栏

accounts 表

用户基本信息表

const mongoose = require("mongoose");

var account = mongoose.Schema({
  userID: {
    unique: true,
    type: String,
  },
  username: String,
  balance: Number,
  robloxCookie:String,
  transactions: Array,
  totalBetMade: Number,
  gameDetails: Array,
  ... ...
});

var accountSchema = mongoose.model("Account", account, "accounts");

module.exports = accountSchema;

clans 部落表

部落表中包含有成员列表

var mongoose = require("mongoose");
const Schema = mongoose.Schema;

const userinfoVirtual = {
  ref: "Account", // The model to use
  localField: "user", // Find people where `localField`
  foreignField: "userID",
  justOne: true,
};


var clansMembersSchema = new Schema(
  {
    state: {
      type: Number, // unknow = 0, applyed = 1, joined = 2 , deleted = 3
      default: 0,
    },
    user: String,  // 这个是用户ID ，对应 Accouct 表内的 userID
    applyTime: {
      type: Date,
      default: new Date(),
    },
    joinedTime: Date,
    rejectTime: Date,
    earnisngs: {
      type: Number,
      default: 0,
    },
    points: {
      type: Number,
      default: 0,
    },
  },
  {
    toJSON: {
      virtuals: true,
    },
  }
);

clansMembersSchema.virtual("userinfo", userinfoVirtual);

const clansSchema = new Schema({
  clansId: {
    unique: true,
    default: mongoose.Types.ObjectId,
    type: Schema.Types.ObjectId,
  },
  avatar: String,
  name: String,
  description: String,
  joinType: Number, // Unlimited = 0, Apply = 1
  members: [clansMembersSchema],
  earnisngs: {
    type: Number,
    default: 0,
  },
  points: {
    type: Number,
    default: 0,
  },
  createTime: {
    type: Date,
    default: new Date(),
  },
  createAt: String,
  updateTime: {
    type: Date,
    default: new Date(),
  },
  updateAt: String,
  isDeleted: {
    type: Boolean,
    default: false,
  },
});

const ClansModel = mongoose.model("Clans", clansSchema, "clans");
module.exports = ClansModel;

重点

部落列表中的用户 state， unknow = 0, applyed = 1, joined = 2 , deleted = 3

期望：筛选出不同状态的的用户

错误示范

ClansModel.find({
	clansId: ObjectId('sssssssss')
	'members.state' : 2,
})

这里 members.state 它是做标记，没有达到预期效果

正解


db.clans.aggregate([
    { $match: { clansId: ObjectId("63f034d9be776a24a0dae150") } },
    { $unwind: '$members' },
    {
        $lookup: {
            from: 'accounts',
            foreignField: 'userID', 
            localField: 'members.user',
            as: 'members.userinfo'
        }
    },
     { $unwind: '$members.userinfo' },
     {
        $group: {
            _id: '$_id',
            root: { $mergeObjects: '$$ROOT' },
            members: { $push: '$members' }
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $mergeObjects: ['$root', '$$ROOT']
            }
        }
    },
    {
        $project: {
            root: 0
        }
    },
    {
      $project: {
        _id: 0,
        avatar: 1,
        description: 1,
        members: {
          $filter: {
            input: "$members",
            as: "item",
            cond: { $eq: ["$$item.state", 2] },
          },
        },
      },
    },

  ])

展示结果

{
    "avatar" : "avatar",
    "description" : "clans description",
    "members" : [
        {
            "state" : NumberInt(1),
            "user" : "63eccd1cc7f11028dd0f0bfe",
            "applyTime" : ISODate("2023-02-18T02:19:09.564+0000"),
            "joinedTime" : null,
            "_id" : ObjectId("63f0359d0d11c7efc292256e"),
            "userinfo" : {
                "_id" : ObjectId("63eccd1cc7f11028dd0f0bfe"),
                "userID" : "63eccd1cc7f11028dd0f0bfe",
                "username" : "shangwenhe1-5",
                "balance" : NumberInt(10),
                "robloxCookie" : "robloxCookie",
                "transactions" : [

                ],
                "totalBetMade" : NumberInt(10),
                "gameDetails" : [

                ],
                "profilePicture" : "profilePicture",
                "affiliateCode" : "affiliateCode",
                "affiliateUsed" : "affiliateUsed",
                "isAffiliateSet" : false,
                "isPremium" : false,
                "affiliateUsers" : [

                ],
                "claimables" : [

                ],
                "createdAt" : ISODate("2023-02-15T12:16:28.206+0000"),
                "isWithdrawalLocked" : false,
                "isTippingLocked" : false,
                "isYouTuBeLocked" : false,
                "lastProxy" : "lastProxy",
                "cryptoTransactions" : [

                ],
                "pendingCryptoTransactions" : [

                ],
                "__v" : NumberInt(0)
            }
        },
        {
            "state" : NumberInt(1),
            "user" : "63eccd1cc7f11028dd0f0bfe",
            "applyTime" : ISODate("2023-02-18T02:19:09.557+0000"),
            "joinedTime" : null,
            "_id" : ObjectId("63f0359d0d11c7efc292256c"),
            "userinfo" : {
                "_id" : ObjectId("63eccd1cc7f11028dd0f0bfe"),
                "userID" : "63eccd1cc7f11028dd0f0bfe",
                "username" : "shangwenhe1-5",
                "balance" : NumberInt(10),
                "robloxCookie" : "robloxCookie",
                "transactions" : [

                ],
                "totalBetMade" : NumberInt(10),
                "gameDetails" : [

                ],
                "profilePicture" : "profilePicture",
                "affiliateCode" : "affiliateCode",
                "affiliateUsed" : "affiliateUsed",
                "isAffiliateSet" : false,
                "isPremium" : false,
                "affiliateUsers" : [

                ],
                "claimables" : [

                ],
                "createdAt" : ISODate("2023-02-15T12:16:28.206+0000"),
                "isWithdrawalLocked" : false,
                "isTippingLocked" : false,
                "isYouTuBeLocked" : false,
                "lastProxy" : "lastProxy",
                "cryptoTransactions" : [

                ],
                "pendingCryptoTransactions" : [

                ],
                "__v" : NumberInt(0)
            }
        },
        {
            "state" : NumberInt(1),
            "user" : "63eccd1cc7f11028dd0f0bfe",
            "applyTime" : ISODate("2023-02-18T02:19:09.555+0000"),
            "joinedTime" : null,
            "_id" : ObjectId("63f0359d0d11c7efc292256a"),
            "userinfo" : {
                "_id" : ObjectId("63eccd1cc7f11028dd0f0bfe"),
                "userID" : "63eccd1cc7f11028dd0f0bfe",
                "username" : "shangwenhe1-5",
                "balance" : NumberInt(10),
                "robloxCookie" : "robloxCookie",
                "transactions" : [

                ],
                "totalBetMade" : NumberInt(10),
                "gameDetails" : [

                ],
                "profilePicture" : "profilePicture",
                "affiliateCode" : "affiliateCode",
                "affiliateUsed" : "affiliateUsed",
                "isAffiliateSet" : false,
                "isPremium" : false,
                "affiliateUsers" : [

                ],
                "claimables" : [

                ],
                "createdAt" : ISODate("2023-02-15T12:16:28.206+0000"),
                "isWithdrawalLocked" : false,
                "isTippingLocked" : false,
                "isYouTuBeLocked" : false,
                "lastProxy" : "lastProxy",
                "cryptoTransactions" : [

                ],
                "pendingCryptoTransactions" : [

                ],
                "__v" : NumberInt(0)
            }
        },
        {
            "state" : NumberInt(1),
            "user" : "1676462351467",
            "applyTime" : ISODate("2023-02-18T02:19:55.758+0000"),
            "joinedTime" : null,
            "_id" : ObjectId("63f035cb8c4d4065f8dd5e3f"),
            "userinfo" : {
                "_id" : ObjectId("63ecc90f9a43459e1c8d000a"),
                "userID" : "1676462351467",
                "username" : "shangwenhe2-1",
                "balance" : NumberInt(10),
                "robloxCookie" : "robloxCookie",
                "transactions" : [

                ],
                "totalBetMade" : NumberInt(10),
                "gameDetails" : [

                ],
                "profilePicture" : "profilePicture",
                "affiliateCode" : "affiliateCode",
                "affiliateUsed" : "affiliateUsed",
                "isAffiliateSet" : false,
                "isPremium" : false,
                "affiliateUsers" : [

                ],
                "claimables" : [

                ],
                "createdAt" : ISODate("2023-02-15T11:59:11.467+0000"),
                "isWithdrawalLocked" : false,
                "isTippingLocked" : false,
                "isYouTuBeLocked" : false,
                "lastProxy" : "lastProxy",
                "cryptoTransactions" : [

                ],
                "pendingCryptoTransactions" : [

                ],
                "__v" : NumberInt(0)
            }
        },
        {
            "state" : NumberInt(1),
            "user" : "63ecc905b294541cc3644d27",
            "applyTime" : ISODate("2023-02-18T02:19:55.761+0000"),
            "joinedTime" : null,
            "_id" : ObjectId("63f035cb8c4d4065f8dd5e41"),
            "userinfo" : {
                "_id" : ObjectId("63eccabc6bbfac0f21c8ffca"),
                "userID" : "63ecc905b294541cc3644d27",
                "username" : "shangwenhe1-2",
                "balance" : NumberInt(10),
                "robloxCookie" : "robloxCookie",
                "transactions" : [

                ],
                "totalBetMade" : NumberInt(10),
                "gameDetails" : [

                ],
                "profilePicture" : "profilePicture",
                "affiliateCode" : "affiliateCode",
                "affiliateUsed" : "affiliateUsed",
                "isAffiliateSet" : false,
                "isPremium" : false,
                "affiliateUsers" : [

                ],
                "claimables" : [

                ],
                "createdAt" : ISODate("2023-02-15T12:06:20.351+0000"),
                "isWithdrawalLocked" : false,
                "isTippingLocked" : false,
                "isYouTuBeLocked" : false,
                "lastProxy" : "lastProxy",
                "cryptoTransactions" : [

                ],
                "pendingCryptoTransactions" : [

                ],
                "__v" : NumberInt(0)
            }
        }
    ]
}

相关文档： http://www.petecorey.com/blog/2020/01/29/mongodb-object-array-lookup-aggregation/