Mongoose聚合查询中实现高效字符串匹配与过滤

const getQuoteAuthorSearchedResult = async (req, res) => {
  try {
    const searchWord = req.params.searchWord;

    // 第一步：聚合获取唯一作者及其计数
    const uniqueQuoteAuthors = await QuoteModel.aggregate().group({
      _id: "$author",
      count: { $sum: 1 },
    });

    // 第二步：在应用层对聚合结果进行过滤
    const filteredData = uniqueQuoteAuthors.filter((value) => {
      return value._id.toLowerCase().includes(searchWord.toLowerCase());
    });

    res.status(200).json({
      results: filteredData
    });
  } catch (error) {
    res.status(401).json({ success: false });
  }
};

const getQuoteAuthorSearchedResultOptimized = async (req, res) => {
  try {
    const searchWord = req.params.searchWord;

    const filteredQuoteAuthors = await QuoteModel.aggregate([
      // 步骤1: 聚合获取唯一作者及其计数
      {
        $group: {
          _id: "$author",
          count: { $sum: 1 },
        },
      },
      // 步骤2: 在聚合管道中对结果进行过滤
      {
        $match: {
          _id: { $regex: searchWord, $options: 'i' }, // 对_id（即作者名）进行大小写不敏感的正则匹配
        },
      },
    ]);

    res.status(200).json({
      results: filteredQuoteAuthors
    });
  } catch (error) {
    res.status(401).json({ success: false });
  }
};

import mongoose from 'mongoose';

// 假设配置信息在config.js中
const config = {
    MONGODB_URI: 'mongodb://localhost:27017/testdb' // 替换为你的MongoDB连接URI
};

// 开启Mongoose调试模式，查看执行的MongoDB命令
mongoose.set('debug', true);

// 定义Quote Schema和Model
const quoteSchema = new mongoose.Schema({
    author: String,
    quote: String, // 添加一个引用字段
});
const QuoteModel = mongoose.model('Quote', quoteSchema); // 注意：Mongoose会自动将模型名复数化并小写作为集合名 (quotes)

(async function main() {
    try {
        await mongoose.connect(config.MONGODB_URI);
        console.log('MongoDB connected successfully.');

        // 清空集合以便重复运行示例
        await QuoteModel.collection.drop().catch(() => console.log('Collection did not exist, skipping drop.'));

        // 填充示例数据
        await QuoteModel.create([
            { author: 'Nick', quote: 'Stay hungry, stay foolish.' },
            { author: 'Nick', quote: 'The only way to do great work is to love what you do.' },
            { author: 'Jack', quote: 'Life is what happens when you are busy making other plans.' },
            { author: 'John', quote: 'The future belongs to those who believe in the beauty of their dreams.' },
            { author: 'Alex', quote: 'Imagination is more important than knowledge.' },
            { author: 'nick', quote: 'The mind is everything. What you think you become.' }, // 小写作者名
        ]);
        console.log('Seed data created.');

        // 定义搜索关键词
        const searchWord = 'CK'; // 尝试搜索 "Nick" 和 "Jack"

        console.log(`\nSearching for authors containing "${searchWord}" (case-insensitive):`);

        // 执行优化后的聚合查询
        const uniqueQuoteAuthors = await QuoteModel.aggregate([
            {
                $group: {
                    _id: '$author', // 按作者名分组
                    count: { $sum: 1 }, // 计算每个作者的引用数量
                },
            },
            {
                $match: {
                    _id: { $regex: searchWord, $options: 'i' }, // 对分组后的_id（作者名）进行大小写不敏感的正则匹配
                },
            },
        ]);

        console.log('Filtered unique authors:', uniqueQuoteAuthors);

        // 另一个搜索示例
        const searchWord2 = 'Ni';
        console.log(`\nSearching for authors containing "${searchWord2}" (case-insensitive):`);
        const uniqueQuoteAuthors2 = await QuoteModel.aggregate([
            {
                $group: {
                    _id: '$author',
                    count: { $sum: 1 },
                },
            },
            {
                $match: {
                    _id: { $regex: searchWord2, $options: 'i' },
                },
            },
        ]);
        console.log('Filtered unique authors:', uniqueQuoteAuthors2);


    } catch (error) {
        console.error('Error during aggregation:', error);
    } finally {
        await mongoose.connection.close();
        console.log('MongoDB connection closed.');
    }
})();

MongoDB connected successfully.
Collection did not exist, skipping drop.
Seed data created.

Searching for authors containing "CK" (case-insensitive):
Mongoose: quotes.aggregate([ { '$group': { '_id': '$author', 'count': { '$sum': 1 } } }, { '$match': { '_id': { '$regex': 'CK', '$options': 'i' } } } ])
Filtered unique authors: [ { _id: 'Jack', count: 1 }, { _id: 'Nick', count: 2 }, { _id: 'nick', count: 1 } ]

Searching for authors containing "Ni" (case-insensitive):
Mongoose: quotes.aggregate([ { '$group': { '_id': '$author', 'count': { '$sum': 1 } } }, { '$match': { '_id': { '$regex': 'Ni', '$options': 'i' } } } ])
Filtered unique authors: [ { _id: 'Nick', count: 2 }, { _id: 'nick', count: 1 } ]