How to implement backend search within a table whose data is being paginated using mongoDB?

Issue

I want to implement backend search within same API (if possible) from a list of systemIDs assigned to the user.

Collection where system (ID)s are assigned to a user:

{ 
    "_id" : ObjectId("62093266db5c0830fe3397ea"), 
    "email" : "naved@gmail.com",
    "role" : "owner", 
    "systemID" : [
        "12345678", 
        "20200302"
    ], 
    "mobile" : "9876543210"
}

System Ids (bPID) are stored individually inside another collection with current details of the system.

{ 
    "_id" : ObjectId("6212d42aab85a3b9f7559e02"), 
    "bPID" : "12345678", // primary key
    "bPVoltage" : 45.9,
    "capacity" : "5", 
    "alerts" : [ "201", "342" ],
    "state": 2,
    "stateOfCharge": 98
},
{ 
    "_id" : ObjectId("6212d42bab8ba3d9f7599ea2"), 
    "bPID" : "20200302", 
    "bPVoltage" : 45.9,
    "capacity" : "2", 
    "alerts" : [ "200" ],
    "state": 0,
    "stateOfCharge": 48
},
{ 
    "_id": ObjectId("6212d42aab85a3b9f7559e02"), 
    "bPID": "20200302", 
    "bPVoltage": 45.9,
    "capacity": "5", 
    "alerts": [ "315" ],
    "state": 1,
    "stateOfCharge": 78
}

Now I have a table in frontend where paginated data is being populated inside a table and there is a search box above that.

System Table

How to implement search for this if I enter an ID it returns me list of that matching ID assigned only to that user, for example if I enter 12, bPID with 12348 or 12469 is returned but only if it is assigned to the user. My API code to populate the result where only assigned bPIDs are populated in the table is as follows:

router.get('/getActiveSystems', async (req, res) => {
  const systemList = req.systemList;
  const { page, limit } = req.query;

  try {
    if (!page || !limit) {
      return res.status(400).json({
        message: 'Page and limit are required!',
        error: true
      });
    }

    const lastTenMinutes = moment().subtract(10, 'm').toDate();

    const options = {
      page: parseInt(page),
      limit: parseInt(limit),
      sort: {
        date: -1.0
      }
    };

    const query = [
      {
        $match: {
          bPID: {
            $in: systemList
          },
          date: {
            $gte: new Date(lastTenMinutes)
          }
        }
      },
      {
        $project: {
          bPID: 1.0,
          capacity: 1.0,
          alerts: 1.0,
          state: 1.0,
          stateOfCharge: 1.0
        }
      }
    ];

    const aggregate = BatteryPacks.aggregate(query);
    const systemDetails = await BatteryPacks.aggregatePaginate(aggregate, options);
    return res.status(200).json(systemDetails);
  } catch (error) {
    return res.status(500).json({
      message: 'Internal server error'
    });
  }
});

Solution

If I understand correctly, you can do something like:

db.users.aggregate([
  {$match: {"_id": user_id}},
  {
    $project: {
      systemIDs: {
        $filter: {
          input: "$systemID",
          as: "item",
          cond: {$regexMatch: {input: "$$item",  regex: searchInput}}
        }
      }
    }
  },
  { $unwind: "$systemIDs"},
  {
    $lookup: {
      from: "systemIds",
      localField: "systemIDs",
      foreignField: "bPID",
      as: "systemIds"
    }
  },
  { $sort: { systemIDs: 1}},
  { $skip: 0},
  { $limit: 10}
])

Where the $match finds the specific user, the $project filters only systemID that are matching the searchInput, and the $lookup gets the systemID data. The other steps are there to allow the pagination (of 10 results in this example).

You can see it works on the playground

Answered By – nimrod serok

Answer Checked By – Pedro (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.