Filtering/Search not working at subdocument level for $or

Working with Data Node.js Frameworks
1 
[
    {
        “_id”: “5f830078a046f42267ffe5c3",
        “categoryName”: “Eggs, Meat & Fish”,
        “subcategory”: [
            {
                “_id”: “5f860e3797c86845eab73f53”,
                “subcategoryName”: “Country Eggs”,
                “productCode”: “EC01”,
                “productName”: “Kadaknath Country Eggs”,
                “description”: “Mpm Eggs - Kadaknath Chicken, 6 pcs Carton”,
                “price”: 123,
                “quantity”: 13,
                “updated”: “2020-10-13T22:51:49.389Z”,
                “created”: “2020-10-13T20:29:43.581Z”
            },
            {
                “_id”: “5f860e3797c86845eab73f54”,
                “subcategoryName”: “Country Eggs”,
                “productCode”: “EC02”,
                “productName”: “Country Chicken Eggs”,
                “description”: “Mpm Eggs - Country Chicken, 6 pcs Carton”,
                “price”: 60,
                “quantity”: 10,
                “updated”: “2020-10-13T20:29:43.589Z”,
                “created”: “2020-10-13T20:29:43.589Z”
            },
            {
                “_id”: “5f860e3797c86845eab73f55”,
                “subcategoryName”: “Country Eggs”,
                “productCode”: “EC03”,
                “productName”: “Country Duck Eggs”,
                “description”: “Mpm Eggs - Country Duck, 6 pcs Carton”,
                “price”: 90,
                “quantity”: 10,
                “updated”: “2020-10-13T20:29:43.591Z”,
                “created”: “2020-10-13T20:29:43.591Z”
            },
            {
                “_id”: “5f860e3797c86845eab73f56”,
                “subcategoryName”: “Farm Eggs”,
                “productCode”: “EF01”,
                “productName”: “Speciality Eggs”,
                “description”: “Suguna Speciality Eggs with Omega 3 - Heart, 6 pcs Carton”,
                “price”: 70,
                “quantity”: 10,
                “updated”: “2020-10-13T20:29:43.592Z”,
                “created”: “2020-10-13T20:29:43.592Z”
            },
            {
                “_id”: “5f860e3797c86845eab73f57”,
                “subcategoryName”: “Farm Eggs”,
                “productCode”: “EF02”,
                “productName”: “Farm Made Eggs”,
                “description”: “Farm Made Eggs - Free Range, 6 pcs”,
                “price”: 120,
                “quantity”: 10,
                “updated”: “2020-10-13T20:29:43.594Z”,
                “created”: “2020-10-13T20:29:43.594Z”
            },
            {
                “_id”: “5f860e3797c86845eab73f58”,
                “subcategoryName”: “Protein Eggs”,
                “productCode”: “EP01”,
                “productName”: “Suguna Specialty Eggs with Vitamin D”,
                “description”: “Suguna Specialty Eggs - With Vitamin D, 6 pcs”,
                “price”: 80,
                “quantity”: 10,
                “updated”: “2020-10-13T20:29:43.596Z”,
                “created”: “2020-10-13T20:29:43.596Z”
            },
            {
                “_id”: “5f860e3797c86845eab73f59”,
                “subcategoryName”: “Protein Eggs”,
                “productCode”: “EP02”,
                “productName”: “Best Diabetes Eggs”,
                “description”: “Best Diaabet Egg - Selenium Enriched Good for Diabetes, 6 pcs Pouch”,
                “price”: 50,
                “quantity”: 10,
                “updated”: “2020-10-13T20:29:43.597Z”,
                “created”: “2020-10-13T20:29:43.597Z”
            },
            {
                “_id”: “5f860e3797c86845eab73f5a”,
                “subcategoryName”: “Protein Eggs”,
                “productCode”: “EP03”,
                “productName”: “Double Yolk”,
                “description”: “Mpm Eggs - Double Yolk, 6 nos”,
                “price”: 50,
                “quantity”: 10,
                “updated”: “2020-10-13T20:29:43.599Z”,
                “created”: “2020-10-13T20:29:43.599Z”
            },
            {
                “_id”: “5f860e3797c86845eab73f5b”,
                “subcategoryName”: “Other Eggs”,
                “productCode”: “EO01”,
                “productName”: “Quail Eggs”,
                “description”: “Mpm Quail Eggs, 12 pcs”,
                “price”: 65,
                “quantity”: 10,
                “updated”: “2020-10-13T20:29:43.608Z”,
                “created”: “2020-10-13T20:29:43.608Z”
            },
            {
                “_id”: “5f860e3797c86845eab73f5c”,
                “subcategoryName”: “Other Eggs”,
                “productCode”: “EO02”,
                “productName”: “Panchakavya Eggs”,
                “description”: “Mpm Eggs - Panchakavya, 300 g”,
                “price”: 70,
                “quantity”: 10,
                “updated”: “2020-10-13T20:29:43.609Z”,
                “created”: “2020-10-13T20:29:43.609Z”
            }
        ],
        “__v”: 0
    }
]

this is the sample data i’m testing.

{“subcategory.$.productName”:{ $regex: req.params.id, $options: ‘i’ }}]},

When i removed the $ sign from field it retrieves records but the filter is not working

Even if i select the subcategory name as farm or other or country or protein instead of fetching that particular subdocument it is fetching the entire document. i’m getting all subdocuments instead of getting subdocuments matching the name farm or country or protein…all records are getting retrieved. only in the parent level filter works

why? any fix required from mongoose side or mongoDB side??

exports.getAllProduct = (req, res, next) => {
categories.find({$or:[{“categoryName”:{ $regex: req.params.id, $options: ‘i’ }},
{“subcategory.$.productName”:{ $regex: req.params.id, $options: ‘i’ }}]},
(err, products) => {
   console.log(“Products:“+JSON.stringify(products));
  if (err) {
         return res.status(400).json({
           error: “No Products found”+err
         });
       }
       res.status(200).json(products)
     });
};
const categorySchema = new mongoose.Schema({
        categoryName: {
            type: String,
            trim: true
        },
        subcategory: [ {
            subcategoryName: {
                type: String,
                trim:true
            },
            productCode: {
                type: String,
                trim: true
            }]});

http://localhost:3000/category/getAllProduct/fruits

2

The positional $ is a projection operator used with arrays. The usage is within a query’s projection (not in a filter as you had tried).

Note that the array projection operators $ and $elemMatch return only the first matching element. You can use an aggregation query with $filter array operator to filter all the matching sub-documents.

3 
$filter: {
     input: req.params.id
     as: "input",
     cond: { $or: [
       {“categoryName”:{ $regex: input, $options: ‘i’ }},
      {“subcategory.productName”:{ $regex: input, $options: ‘i’ }}
      ] }
  }
}

Is the above code $filter with $or will work??? Is it right??

4

Yes, the filter’s condition can have aggregation $or operator. Also, note that you may have to use the aggregation operator $regexMatch.

Also, note that the variable defined as: 'input' should be referred as "$$input" as used within the $cond.

5

Thanks a lot, will try this and let you know if any doubts.