CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-queryaggregation-framework
A for android
A for android

Reputation: 168

regex/pipeline in lookup mongo db

I am new to mongodb. I am trying to lookup 2 collections with the search parameter provided.

2 collection has the following structure:

servicetypes

_id:ObjectId(5ede7d9552d21c000436ac52)
TypeId:"1"
ServiceTypeTitle:"Body and Frame"
__v:0

servicesubtypes

_id:ObjectId(5ede85003bdd1c0004f26e71)
TypeId:"1"
SubTypeId:"1"
ServiceSubTypeTitle:"Air dam repaired"
__v:0

I tried to use regex and pipeline but I am not achieving what I want. Following is the code that I wrote.

route.get('/FilterServices/:text', async (req, res) => {
    var text = req.params.text
    var list = await ServiceType.aggregate([
        {
          $lookup: {
            from: "servicesubtypes",
            localField: "TypeId",
            foreignField: "TypeId",
            as: "ServiceSubTypes"
          } 
        },
        { "$match": { "ServiceSubTypes.ServiceSubTypeTitle": {$regex: text, $options:"i"} } }
      ]);
      console.log(list)
      res.send(list)
});

This returns me the data in the following manner:

[
      {
        _id: 5ede7d9552d21c000436ac52,
        TypeId: '1',
        ServiceTypeTitle: 'Body and Frame',
        __v: 0,
        ServiceSubTypes: [
            [Object], [Object], [Object], [Object], [Object], [Object],
            [Object], [Object], [Object], [Object], [Object], [Object],
        ...100 more items
        ]
    },
    {
        _id: 5ede7d9652d21c000436ac53,
        TypeId: '2',
        ServiceTypeTitle: 'Brakes',
        __v: 0,
        ServiceSubTypes: [
              [Object], [Object], [Object], [Object], [Object], [Object],
              [Object], [Object], [Object], [Object], [Object], [Object],
        ...100 more items
        ]
    },

    {I have 5 more items in ServiceType but are not added due to regex}
]

with the code above if it finds the value it returns me the entire ServiceSubTypes array for particular ServiceType. If it doesn't find it then ServiceType is not added in the list.

If the match is found then I only want that particular record in the ServiceSubTypes array. i.e

if I the search paramter is sunvisor then the result should be like 

[
      {
      _id: 5ede7d9652d21c000436ac5a,
      TypeId: '9',
      ServiceTypeTitle: 'Vehicle',
      __v: 0,
      ServiceSubTypes: [
       {
                "_id": "5ede85683bdd1c0004f274d8",
                "TypeId": "9",
                "SubTypeId": "75",
                "ServiceSubTypeTitle": "Right sunvisor replaced",
                "__v": 0
            },
        {
                "_id": "2ede8683dsaddc0004f2we4d8",
                "TypeId": "9",
                "SubTypeId": "75",
                "ServiceSubTypeTitle": "Right sunvisor replaced",
                "__v": 0
            },
    ]
]

Upvotes: 1

Views: 2672

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17925

In MongoDB, when you use $match on an array - you would get entire array if at-least one object in the array satisfies the match criteria (not just the matching object but all objects will be retained) & eventually the document contains that array.

{I have 5 more items in ServiceType but are not added due to regex}

As I've said earlier, for these 5 documents in the ServiceType collection there is no object in ServiceSubTypes array that satisfies the condition in $match.

Since we already know it would get entire array then how do we get only matched objects in an array in aggregation ? It can be done by using aggregation operator $filter. After $match stage you can have $addFields stage with $filter on ServiceSubTypes array to retain only matched objects in array i.e; to get desired result.

But in another way : if we can control the data that is being retrieved into ServiceSubTypes array from servicesubtypes collection, then we don't have to do these additional steps :

You can use specify-multiple-join-conditions-with-lookup instead of original $lookup :

Modified Query :

[
  {
    $lookup: {
      from: "servicesubtypes",
      let: { typeId: "$TypeId" },
      pipeline: [
        { $match: { $expr: { $eq: ["$TypeId", "$$typeId"] } } },
        { $match: { ServiceSubTypeTitle: { $regex: text, $options: "i" } } }
      ],
      as: "ServiceSubTypes",
    }
  },
  { $match: { ServiceSubTypes: { $ne: [] } } }
]

Upvotes: 1

Related Questions