如何在Express中用Mongoose实现MongoDB的OR及大小写不敏感模糊查询

我需要通过自动补全框查询机场列表，刚从PHP CodeIgniter & MySQL技术栈切换到Node Express和MongoDB，不清楚如何在Express和Mongoose中编写对应的查找查询语句。

基础代码参考 #

MongoDB集合文档结构示例 #

{
    "aptid": "2",
    "code": "BJY",
    "name": "Batajnica Airport",
    "cityCode": "BEG",
    "cityName": "Belgrade",
    "countryName": "SERBIA",
    "countryCode": "RS",
    "continent_id": "1",
    "timezone": "1",
    "lat": "44.9236",
    "lon": "20.279",
    "city": "false"
},
{
    "aptid": "3",
    "code": "BJZ",
    "name": "Talaveral La Real Airport",
    "cityCode": "BJZ",
    "cityName": "Badajoz",
    "countryName": "SPAIN",
    "countryCode": "ES",
    "continent_id": "1",
    "timezone": "1",
    "lat": "38.89125",
    "lon": "-6.821333",
    "city": "true"
},
{
    "aptid": "4",
    "code": "BKB",
    "name": "Bikaner Airport",
    "cityCode": "BKB",
    "cityName": "Bikaner",
    "countryName": "INDIA",
    "countryCode": "IN",
    "continent_id": null,
    "timezone": "5",
    "lat": "0",
    "lon": "0",
    "city": "true"
},
// 共8000+条数据

模型文件 airports.model.js #

const mongoose = require('mongoose');
const { toJSON, paginate } = require('./plugins');
const airportsSchema = mongoose.Schema(
    {
        aptid: {type: String},
        code: {type: String},
        name: {type: String},
        cityCode: {type: String},
        cityName: {type: String},
        countryName: {type: String},
        countryCode: {type: String},
        continent_id: {type: String},
        timezone: {type: String},
        lat: {type: String},
        lon: {type: String},
        city: {type: String}
    },
    {
        timestamps: true,
    }
); 

// 添加插件将mongoose返回结果转为JSON格式
airportsSchema.plugin(toJSON);
airportsSchema.plugin(paginate);


/**
 * @typedef Airports
 */
const Airports = mongoose.model('Airports', airportsSchema);

module.exports = Airports;

路由文件 airports.route.js #

const express = require('express');
const airportcontroller = require('../../controllers/airport.controller');

const router = express.Router();

router.get('/find', airportcontroller.findAirport);

module.exports = router;

初始控制器代码 airport.controller.js #

const catchAsync = require('../utils/catchAsync');
const axios = require('axios');
const { Airports } = require('../models');

const findAirport = catchAsync(async (req, res) => {
  try{
       const listairports = await Airports.find(req.body.aptsearchkey).exec();
       // 此处需要编写查询逻辑
       res.json(listairports);
    }catch(err){
       return res.status(500).send({
        message: err.message
      })
    }   
});

module.exports ={
    addAirport,
    findAirport
}

原PHP实现逻辑参考 #

需要实现和下述CodeIgniter代码一致的模糊匹配效果：对机场三字码、机场名称、城市三字码、城市名称、国家名称五个字段做模糊匹配，返回匹配结果。

function get_airports($querystring){
     $this->db->select('*');
     $this->db->like('code', $querystring);
     $this->db->or_like('name', $querystring);
     $this->db->or_like('cityCode', $querystring);
     $this->db->or_like('cityName', $querystring);
     $this->db->or_like('countryName', $querystring);
     $result = $this->db->get('pt_flights_airports')->result_array();
     return $result;
}

问题：需要支持大小写不敏感匹配 #

初始编写的Mongoose查询只能做到大小写敏感匹配，无法适配用户输入大小写不固定的场景，初始代码如下：

const findAirport = catchAsync(async (req, res) => {
  try{
       const aptsearchkey = req.body.aptsearchkey
       const listairports = await Airports.find({ 
         $or: [ 
            { code: { $regex: '.*' + aptsearchkey + '.*' } },
            { price: { $regex: '.*' + aptsearchkey + '.*' } },
            { cityCode: { $regex: '.*' + aptsearchkey + '.*' } },
            { cityName: { $regex: '.*' + aptsearchkey + '.*' } },
            { countryName: { $regex: '.*' + aptsearchkey + '.*' } },
          ] 
        }).exec();
       res.json(listairports);
    }catch(err){
       return res.status(500).send({
        message: err.message
      })
    }   
});

最终解决方案 #

在MongoDB正则查询参数中添加$options:'i'标识，即可实现大小写不敏感匹配，最终代码如下：

const findAirport = catchAsync(async (req, res) => {
  try{
       const aptsearchkey = req.body.aptsearchkey
       const listairports = await Airports.find({ 
         $or: [ 
            { code: { '$regex': '.*' + aptsearchkey + '.*' ,$options:'i' } },
            { price: { '$regex': '.*' + aptsearchkey + '.*' ,$options:'i'} },
            { cityCode: { '$regex': '.*' + aptsearchkey + '.*' ,$options:'i'} },
            { cityName: { '$regex': '.*' + aptsearchkey + '.*' ,$options:'i'} },
            { countryName: { '$regex': '.*' + aptsearchkey + '.*' ,$options:'i'} },
          ] 
        }).exec();
       res.json(listairports);
    }catch(err){
       return res.status(500).send({
        message: err.message
      })
    }   
});

注：代码中price字段为笔误，实际可根据业务需要删除或替换为正确字段。

内容的提问来源于stack exchange，提问作者Vinay Thakur