MongoDB aggregation multiple partial matches

Question

I am in the process of moving pagination / filtering away from the client and onto the server.

Data is presented in a table, each column header has a text input where you can type and filter the dataset by what is typed in. This uses a simple indexOf check on the inputted text and the dataset to allow partial matches.

Example table column

    { name: "test 1" }, { name: "test 2" }

The image / data above shows a column in the table. If I were to type in "tes" both results would appear.

    let filteredResults = data.filter(row => row.name.toLowerCase().indexOf(filterValue) > -1)

I have now moved this filtering onto the server but I am struggling to work out how to do a similar partial match when quering my data. Below is my query:

    aggregate([
                {
                    $facet: {
                        results: [
                            {
                                $match: {
                                    "name": req.body.name
                                }
                            },
                            {
                                $skip: pageOptions?.pageNo ? (pageOptions.pageNo - 1) * 10 : 0
                            },
                            {
                                $limit: 10
                            }
                        ],
                        totalCount: [
                            {
                                $match: {
                                    "name": req.body.name
                                }
                            },
                            { $count: 'totalCount' }
                        ]
                    }
                },
                {
                    $addFields:
                    {
                        "total": { $arrayElemAt: ["$totalCount.totalCount", 0] }
                    }
                },
                {
                    $project: {
                        "totalCount": 0
                    }
                }
            ]

Each of the fields in the $match stage are possible columns from the table, in this example just the name field. You could filter by more then 1. The above works with exact matches so if we were to search the name column with "test 1" then that record would be returned but if we search for "tes" nothing would be returned.

Any help with this would be great!

Answer 1

You can use a $regex match to perform your case-insensitive, partial string match.

db.collection.aggregate([
  {
    $match: {
      "name": {
        // put your query in $regex option
        $regex: "tes",
        $options: "i"
      }
    }
  },
  {
    $facet: {
      results: [
        {
          $skip: 0
        },
        {
          $limit: 10
        }
      ],
      totalCount: [
        {
          $count: "totalCount"
        }
      ]
    }
  },
  {
    $addFields: {
      "total": {
        $arrayElemAt: [
          "$totalCount.totalCount",
          0
        ]
      }
    }
  },
  {
    $project: {
      "totalCount": 0
    }
  }
])

Here is the Mongo playground for your reference.