All geek questions in one place

Mongodb self-defined filter comparison

My data is as follows:

{max:3.4.6, min: 1.10.2}
{max:10.9.12, min:6.90.1}

the number 3.5.6is stored as string, but 0.10.0should be more 0.9.0. I want to give a different number curand return all the results inside cur between them.

find all document where min < cur < max

Is it possible to define a comparison function that can recognize 1.10.2 < 2.1.3 < 3.4.6? And use it to execute the request?

+4
source share
2 answers

There is also a solution with an aggregation base that you can use, but this requires the current version of mongoDB, since it uses the $splitand operator $strLenBytes. I created it on version 3.3.10.

, , ( 000 999, ) min max, .
, ,

/* 1 */
{
    "_id" : ObjectId("57ac0c264ae6fbd5fb5b6e97"),
    "max" : "3.4.6",
    "min" : "1.10.2"
}

/* 2 */
{
    "_id" : ObjectId("57ac0c264ae6fbd5fb5b6e98"),
    "max" : "10.9.12",
    "min" : "6.90.1"
}

/* 3 */
{
    "_id" : ObjectId("57ac0c264ae6fbd5fb5b6e99"),
    "max" : "3.5.9",
    "min" : "0.5.7"
}

/* 4 */
{
    "_id" : ObjectId("57ac0c264ae6fbd5fb5b6e9a"),
    "max" : "2.0.0",
    "min" : "1.5.0"
}


var cur = "1.11.1";
var curParts = cur.split('.');
var curPadded = ("00" + curParts[0]).slice(-3) + "." +
                ("00" + curParts[1]).slice(-3) + "." +
                ("00" + curParts[2]).slice(-3);

db.getCollection('minmax').aggregate([
    {
        $project: {
            min: 1, max: 1,
            maxTmp: {
                $let: {
                    vars: {
                        maxParts: { $split: ["$max", "."] }
                    },
                    in: {
                       major: { $arrayElemAt: ["$$maxParts", 0] },
                       majorLen: { $strLenBytes: { $arrayElemAt: ["$$maxParts", 0] } },
                       minor: { $arrayElemAt: ["$$maxParts", 1] },
                       minorLen: { $strLenBytes: { $arrayElemAt: ["$$maxParts", 1] } },
                       patch: { $arrayElemAt: ["$$maxParts", 2] },
                       patchLen: { $strLenBytes: { $arrayElemAt: ["$$maxParts", 2] } }
                    }
                }
            },
            minTmp: {
                $let: {
                    vars: {
                        minParts: { $split: ["$min", "."] }
                    },
                    in: {
                       major: { $arrayElemAt: ["$$minParts", 0] },
                       majorLen: { $strLenBytes: { $arrayElemAt: ["$$minParts", 0] } },
                       minor: { $arrayElemAt: ["$$minParts", 1] },
                       minorLen: { $strLenBytes: { $arrayElemAt: ["$$minParts", 1] } },
                       patch: { $arrayElemAt: ["$$minParts", 2] },
                       patchLen: { $strLenBytes: { $arrayElemAt: ["$$minParts", 2] } }
                    }
                }
            }            
        }
    },
    {
        $project: {
            min: 1, max: 1,
            maxMajor: { $substr: [{ $concat: ["_00", "$maxTmp.major"] }, "$maxTmp.majorLen", 3] },
            maxMinor: { $substr: [{ $concat: ["_00", "$maxTmp.minor"] }, "$maxTmp.minorLen", 3] },
            maxPatch: { $substr: [{ $concat: ["_00", "$maxTmp.patch"] }, "$maxTmp.patchLen", 3] },
            minMajor: { $substr: [{ $concat: ["_00", "$minTmp.major"] }, "$minTmp.majorLen", 3] },
            minMinor: { $substr: [{ $concat: ["_00", "$minTmp.minor"] }, "$minTmp.minorLen", 3] },
            minPatch: { $substr: [{ $concat: ["_00", "$minTmp.patch"] }, "$minTmp.patchLen", 3] },            
        }
    },
    {
        $project: {
            min: 1, max: 1,
            maxPadded: { $concat: ["$maxMajor", ".", "$maxMinor", ".", "$maxPatch"] },
            minPadded: { $concat: ["$minMajor", ".", "$minMinor", ".", "$minPatch"] }
        }
    },
    {
        $match: {
            maxPadded: { $gt: curPadded },
            minPadded: { $lt: curPadded }
        }
    },
    {
        $project: {
           min: 1,
           max: 1
        }
    }
])  


/* 1 */
{
    "_id" : ObjectId("57ac0c264ae6fbd5fb5b6e97"),
    "max" : "3.4.6",
    "min" : "1.10.2"
}

/* 2 */
{
    "_id" : ObjectId("57ac0c264ae6fbd5fb5b6e99"),
    "max" : "3.5.9",
    "min" : "0.5.7"
}

/* 3 */
{
    "_id" : ObjectId("57ac0c264ae6fbd5fb5b6e9a"),
    "max" : "2.0.0",
    "min" : "1.5.0"
}

. , .

$project: {
    min: 1, max: 1,
    maxTmp: {
        $let: {
            vars: {
                maxParts: { $split: ["$max", "."] }
            },
            in: {
                major: { $arrayElemAt: ["$$maxParts", 0] },
                majorLen: { $strLenBytes: { $arrayElemAt: ["$$maxParts", 0] } },
                minor: { $arrayElemAt: ["$$maxParts", 1] },
                minorLen: { $strLenBytes: { $arrayElemAt: ["$$maxParts", 1] } },
                patch: { $arrayElemAt: ["$$maxParts", 2] },
                patchLen: { $strLenBytes: { $arrayElemAt: ["$$maxParts", 2] } }
            }
        }
    },
    minTmp: ... // same idea
}


/* 1 */
{
    "_id" : ObjectId("57ac0c264ae6fbd5fb5b6e97"),
    "max" : "3.4.6",
    "min" : "1.10.2",
    "maxTmp" : {
        "major" : "3",
        "majorLen" : 1,
        "minor" : "4",
        "minorLen" : 1,
        "patch" : "6",
        "patchLen" : 1
    }
}
... // others

,

$project: {
    min: 1, max: 1,
    maxMajor: { $substr: [{ $concat: ["_00", "$maxTmp.major"] }, "$maxTmp.majorLen", 3] },
    maxMinor: { $substr: [{ $concat: ["_00", "$maxTmp.minor"] }, "$maxTmp.minorLen", 3] },
    maxPatch: { $substr: [{ $concat: ["_00", "$maxTmp.patch"] }, "$maxTmp.patchLen", 3] },
    minMajor: ... // same idea
}

:

/* 1 */
{
    "_id" : ObjectId("57ac0c264ae6fbd5fb5b6e97"),
    "max" : "3.4.6",
    "min" : "1.10.2",
    "maxMajor" : "003",
    "maxMinor" : "004",
    "maxPatch" : "006",
    "minMajor" : "001",
    "minMinor" : "010",
    "minPatch" : "002"
}
... // others

,

$project: {
    min: 1, max: 1,
    maxPadded: { $concat: ["$maxMajor", ".", "$maxMinor", ".", "$maxPatch"] },
    minPadded: ... // same idea
}


/* 1 */
{
    "_id" : ObjectId("57ac0c264ae6fbd5fb5b6e97"),
    "max" : "3.4.6",
    "min" : "1.10.2",
    "maxPadded" : "003.004.006",
    "minPadded" : "001.010.002"
}
... // others

.

$match: {
    maxPadded: { $gt: curPadded },
    minPadded: { $lt: curPadded }
},
$project: {
    min: 1,
    max: 1
}

: , , , "" $project , , , $project .

+3

$where , , :

db.test.find({
    $where: function(){
        var cur = '7.1.3';
        return compare(this.max, cur) > 0 &&compare(this.min, cur) < 0 ;   

        function compare(base, cur) {
            var baseArr = base.split('.');
            var curArr = cur.split('.');

            //Compare from head to tail
            for(var i in baseArr) {
                if(i <= baseArr.length - 1) {
                    if(parseInt(baseArr[i]) < parseInt(cur[i])) return -1;
                    else if(parseInt(baseArr[i]) > parseInt(cur[i])) return 1;
                    else {
                        continue;
                    }
                }
                else {
                    return 0;
                }
            }
        }
    }

});

, PLS

+1

Source: https://habr.com/ru/post/1650863/

More articles:


All Articles