How to search a string for specific numbers in MongoDB?

Question

MongoDB collection_name have some document with key: key_name and value: "1 ff 42".

How I can find this document, if I know only, what all numbers in value equal 142? I can extract all numbers from string and "convert" it in Integer.

var string_with_numbers = "1 ff 42";
number_array = string_with_numbers.match(/[0-9]+/g);

// 142 by Integer
result = parseInt(number_array.join(''));

But, how I can extract all documents from collection, where all numbers in value equal 142?


db.collection_name.find(
    {
        key_name: {...}
    }
);

now you don't have anything with `142` in your document, so no way for that document to befound with 142. My point is you lose the context with these new values (after combining them and converting them to integer as you shown above) — JBone, Dec 19 '19 at 20:47

Ayoub · Answer 1 · 2019-12-19T22:14:32.547

It's a bit tricky, but you can achieve your case with this aggregation:

db.collection_name.aggregate([
    {
        "$addFields": {
            "results": {
                "$regexFindAll": {
                    "input": "$value",
                    "regex": "[0-9]+"
                }
            }
        }
    },
    {
        "$project": {
            "number": {
                "$convert": {
                    "input": {
                        "$reduce": {
                            "input": "$results",
                            "initialValue": "",
                            "in": {
                                "$concat": ["$$value", "$$this.match"]
                            }
                        }
                    },
                    "to": "int",
                    "onError": "no digit matched"
                }
            }
        }
    },
    {
        "$match": {
            "number": 142
        }
    }
])

data:

> db.collection_name.find()
{ "_id" : ObjectId("5dfbf14671f3d8949c44881c"), "value" : "1rt 42" }
{ "_id" : ObjectId("5dfbf14671f3d8949c44881d"), "value" : "1 4sd 2" }
{ "_id" : ObjectId("5dfbf14671f3d8949c44881e"), "value" : "14 e 6" }
{ "_id" : ObjectId("5dfbf47c71f3d8949c44881f"), "value" : "test" }

after stage one $addFileds will have something like this:

{
    "_id" : ObjectId("5dfbf14671f3d8949c44881c"),
    "value" : "1rt 42",
    "results" : [
        {
            "match" : "1",
            "idx" : 0,
            "captures" : [ ]
        },
        {
            "match" : "42",
            "idx" : 4,
            "captures" : [ ]
        }
    ]
}
.
.
{
    "_id" : ObjectId("5dfbf47c71f3d8949c44881f"),
    "value" : "test",
    "results" : [ ]
}

then after stage two $project:

{ "_id" : ObjectId("5dfbf14671f3d8949c44881c"), "number" : 142 }
{ "_id" : ObjectId("5dfbf14671f3d8949c44881d"), "number" : 142 }
{ "_id" : ObjectId("5dfbf14671f3d8949c44881e"), "number" : 146 }
{ "_id" : ObjectId("5dfbf47c71f3d8949c44881f"), "number" : "no digit matched" }

and finally with the $matchyou'll get your expected result:

{ "_id" : ObjectId("5dfbf14671f3d8949c44881c"), "number" : 142 }
{ "_id" : ObjectId("5dfbf14671f3d8949c44881d"), "number" : 142 }

`/\d+/` gives this error: `fail to parse content of query: invalid character '/' looking for beginning of value` — Valijon, Dec 19 '19 at 22:13
@Valijon that's weird, I've tested the aggregation on v4.2.0 and no error occurse — Ayoub, Dec 19 '19 at 22:16
Try here: [https://mongoplayground.net/p/W8npAVII4Nr](https://mongoplayground.net/p/W8npAVII4Nr) — Valijon, Dec 19 '19 at 22:25

score 0 · Answer 2 · answered Dec 20 '19 at 08:59

The following query will find strings with numbers 1, 4 and 2. Each of the digits occur once only and in the same order 1, 4 and 2.

db.test.find( { $and: [
                   { str: { $not: /[0356789]/ } },
                   { str: { $regex: '^[^1]*1[^1]*$' } },
                   { str: { $regex: '^[^4]*4[^4]*$' } },
                   { str: { $regex: '^[^2]*2[^2]*$' } },
                   { str: { $regex: '1[^4]*4[^2]*2' } }
    ]
} )

Regular expressions used:

$not: /[0356789]/ } matches without any of these numbers 0 3 5 6 7 8 9
'^[^1]*1[^1]*$' matches exactly one occurance of 1; same with 4 and 2.
'1[^4]*4[^2]*2' matches the order of the three numbers 1, 4 and 2.

References:

Of the following documents:

{ "str" : "1 ff 42" }
{ "str" : "0 ff 42" }
{ "str" : "142" }
{ "str" : "14 xx2" }
{ "str" : "42" }
{ "str" : "90 a1 b2 4cc" }
{ "str" : "" }
{ "str" : "421" }
{ "str" : "01xf49W2q" }
{ "str" : "1 ff 422" }
{ "str" : "13 8ff 8x9" }
{ "str" : "x4  a1wx12" }

only these three match:

{ "str" : "1 ff 42" }
{ "str" : "142" }
{ "str" : "14 xx2" }

How to search a string for specific numbers in MongoDB?

2 Answers2