Filter sub-document array using substring as criteria

Question

My collection:

{
  title: 'Computers',
  maincategories:[
    {
       title: 'Monitors',
       subcategories:[
         {
            title: '24 inch',
            code: 'AFG'
         }
       ]
    }
  ]
}

I want query the code. The code is just the first part so I want to have all subcategories that contains the given search. So AFG101 would return this subcategories.

My query:

module.exports = (req, res) => {
  var q = {
    'maincategories.subcategories': {
      $elemMatch: {
        code: 'AFG101'
      }
    }
  };

  var query = mongoose.model('TypeCategory').find(q, {'maincategories.$': 1, 'title': 1});

  query.exec((err, docs) => {
    res.status(200).send(docs);
  });
};

My problem:

How do I search for a part of a string? AFG101 should return all subcategories with property code containing any part of the string. So in this case, AFG would be a hit. Same as in this sql question: MySQL: What is a reverse version of LIKE?
How do I project the subcategories. Current query returns all subcategories. I only want to returns those hitting.

I guess it would be better if you could parse the code `AFG101` into the corresponding code before querying Mongo. — hyades, Nov 12 '16 at 16:08
I think I understand what you want in the first ask. You want, e.g.: to have multiple docs with "code:AFG1,", "code; AFG2", "code:AFG3, "code:ABC1". So, when the user (of whatever) type something like "AFG" mongo will retrieve you all the docs that contain "AFG" in ther CODE field. If so, check "regex" in mongodb. https://docs.mongodb.com/manual/reference/operator/query/regex/#examples — Alberto Rubio, Nov 12 '16 at 23:03
@AlbertoRubio, No, other way around. If the user type AFG101 and the property code in my document has code: AFG, this should be returned. — Joe, Nov 12 '16 at 23:36
Well... it's the same, isn't it? $regex would search the string you type and look for that string into your docs. It's not exactly what you want, but you could start there. Or maybe, if you know all your codes begin with 3 same characters, make and split and get only the "AFG" from the string. First 3 characters of the string. It's all I have, sry. — Alberto Rubio, Nov 13 '16 at 00:13
I can't see its the same. My input is AFG101. I need AFG returned. I don't see how $regex solves this. — Joe, Nov 13 '16 at 11:08

score 2 · Accepted Answer · edited May 23 '17 at 10:29

The best way to do this is in MongoDB 3.4 using the $indexOfCP string aggregation operator.

let code = "afg101";

db.collection.aggregate([
    { "$project": { 
        "title": 1, 
        "maincategories": { 
            "$map": { 
                "input": "$maincategories", 
                "as": "mc", 
                "in": { 
                    "$filter": { 
                        "input": "$$mc.subcategories", 
                        "as": "subcat", 
                        "cond": { 
                            "$gt": [ 
                                { 
                                    "$indexOfCP": [ 
                                        code, 
                                        { "$toLower": "$$subcat.code" }
                                     ] 
                                }, 
                               -1 
                            ] 
                        } 
                    } 
                } 
            } 
        } 
    }} 
])

which returns:

{
    "_id" : ObjectId("582cba57e6f570d40d77b3a8"),
    "title" : "Computers",
    "maincategories" : [
        [
            {
                "title" : "24 inch",
                "code" : "AFG"
            }
        ]
    ]
}

You can read my other answers to similar question 1, 2 and 3.

From 3.2 backward, the only way to do this is with mapReduce.

db.collection.mapReduce(
    function() { 
        var code = 'AFG101'; 
        var maincategories = this.maincategories.map(function(sdoc) {
            return { 
                "title": sdoc.title, 
                "subcategories": sdoc.subcategories.filter(function(scat) { 
                    return code.indexOf(scat.code) != -1; 
                }
            )};
        }); 
        emit(this._id, maincategories); 
   }, 
   function(key, value) {}, 
   { "out": { "inline": 1 } 
})

which yields something like this:

{

    "results" : [
        {
            "_id" : ObjectId("582c9a1aa358615b6352c45a"),
            "value" : [
                {
                    "title" : "Monitors",
                    "subcategories" : [
                        {
                            "title" : "24 inch",
                            "code" : "AFG"
                        }
                    ]
                }
            ]
        }
    ],
    "timeMillis" : 15,
        "counts" : {
            "input" : 1,
            "emit" : 1,
            "reduce" : 0,
            "output" : 1
        },
    "ok" : 1
}

score 0 · Answer 2 · answered Nov 15 '16 at 01:37

Well, just like your question has two parts, I could think of two separate solution, however I don't see a way to join them together.

For first part $where can be used to do a reverse regex, but it's dirty, it's an overkill and it can't use any indexes, since $where runs on each documents.

db.TypeCategory.find({$where:function(){for(var i  in this.maincategories)
{for(var j in this.maincategories[i].subcategories)
 {if("AFG101".indexOf(this.maincategories[i].subcategories[j].code)>=0)
  {return true}}}}},{"maincategories.subcategories.code":1})

Even if you use this option, it would need couple of boundary check and it cannot project two level of nested array. MongoDB doesn't support such projection (yet).

For that purpose we might go for aggregation

db.TypeCategory.aggregate([{$unwind:"$maincategories"},
 {$unwind:"$maincategories.subcategories"},
 {$match:{"maincategories.subcategories.code":"AFG"}},
 {$group:{_id:"$_id","maincategories":{$push:"$maincategories"}}}
])

However I don't think there is a way to do reverse regex check in aggregation, but I might be wrong too. Also this aggregation is costly since there are two unwinds which can lead to overflow the memory limit for aggregation for a really large collection.

Parshuram Kalvikatte · Answer 3 · 2016-11-16T14:39:28.217

0

You can use $substr and do it

db.getCollection('cat').aggregate([
          {"$unwind" : "$maincategories"},
          {"$unwind" : "$maincategories.subcategories"},
          {"$project" :
                {"maincategories" : 1,
                "title":1,"sub" : {"$substr" :["$maincategories.subcategories.code",0,3]}}},
         {"$match" : {"sub" : "AFG"}},
         {"$project" : 
                 {"maincategories" : 1,
                  "title":1}
          }
    ])

edited Nov 16 '16 at 14:39

answered Nov 15 '16 at 18:25

Parshuram Kalvikatte

1,616
4
20
40

You can use $group : {"_id" : "$_id"}stage and $push to make array – Parshuram Kalvikatte Nov 16 '16 at 14:40

Filter sub-document array using substring as criteria

3 Answers3