正则表达式在mongoDB中的数组内 [英] Regex inside array in mongoDB

问题描述

我想用正则表达式在mongodb中的数组内进行查询，这些集合具有如下文档:

i want to do a query inside a array in mongodb with regex, the collections have documents like this:

{
"_id" : ObjectId("53340d07d6429d27e1284c77"),
"company" : "New Company",
"worktypes" : [ 
    {
        "name" : "Pompas",
        "works" : [ 
            {
                "name" : "name 2",
                "code" : "A00011",
                "price" : "22,22"
            }, 
            {
                "name" : "name 3",
                "code" : "A00011",
                "price" : "22,22"
            }, 
            {
                "name" : "name 4",
                "code" : "A00011",
                "price" : "22,22"
            }, 
            {
                "code" : "asdasd",
                "name" : "asdads",
                "price" : "22"
            }, 
            {
                "code" : "yy",
                "name" : "yy",
                "price" : "11"
            }
        ]
    }, 
    {
        "name" : "name 4",
        "works" : [ 
            {
                "code" : "A112",
                "name" : "Nombre",
                "price" : "11,2"
            }
        ]
    },          
    {
        "name" : "ee",
        works":[

            {
                "code" : "aa",
                "name" : "aa",
                "price" : "11"
            }, 
            {
                "code" : "A00112",
                "name" : "Nombre",
                "price" : "12,22"
            }
              ]
    }
]

}

然后，我需要按公司名称查找文档，并且其中的任何工作都在代码或名称工作中匹配正则表达式. 我有这个:

Then i need to find a document by the company name and any work inside it have match a regex in code or name work. I have this:

var companyquery = { "company": "New Company"};
var regQuery = new RegExp('^A0011.*$', 'i');

db.categories.find({$and: [companyquery,
            {$or: [
                {"worktypes.works.$.name": regQuery},
                {"worktypes.works.$.code": regQuery}
            ]}]})

但是不要返回任何结果..我认为错误是尝试使用de dot和$在数组内部进行搜索. 有什么主意吗?

But dont return any result..I think the error is try to search inside array with de dot and $.. Any idea?

与此:

db.categories.find({$and: [{"company":"New Company"},
            {$or: [
                {"worktypes.works.name": {"$regex": "^A00011$|^a00011$"}},
                {"worktypes.works.code": {"$regex": "^A00011$|^a00011$"}}
            ]}]})

这是结果:

{
    "_id" : ObjectId("53340d07d6429d27e1284c77"),
    "company" : "New Company",
    "worktypes" : [ 
        {
            "name" : "Pompas",
            "works" : [ 
                {
                    "name" : "name 2",
                    "code" : "A00011",
                    "price" : "22,22"
                }, 
                {
                    "code" : "aa",
                    "name" : "aa",
                    "price" : "11"
                }, 
                {
                    "code" : "A00112",
                    "name" : "Nombre",
                    "price" : "12,22"
                }, 
                {
                    "code" : "asdasd",
                    "name" : "asdads",
                    "price" : "22"
                }, 
                {
                    "code" : "yy",
                    "name" : "yy",
                    "price" : "11"
                }
            ]
        }, 
        {
            "name" : "name 4",
            "works" : [ 
                {
                    "code" : "A112",
                    "name" : "Nombre",
                    "price" : "11,2"
                }
            ]
        }, 
        {
            "name" : "Bombillos"
        }, 
        {
            "name" : "Pompas"
        }, 
        {
            "name" : "Bombillos 2"
        }, 
        {
            "name" : "Other type"
        }, 
        {
            "name" : "Other new type"
        }
    ]
}

正则表达式不会将结果显示出来..

The regex dont field the results ok..

推荐答案

您正在使用JavaScript本机RegExp对象作为正则表达式，但是，为了使mongo处理正则表达式，需要将其作为查询的一部分发送文档，这不是同一回事.

You are using a JavaScript native RegExp object for the regular expression, however for mongo to process the regular expression it needs to be sent as part of the query document, and this is not the same thing.

此外，正则表达式将与所需的值不匹配.对于完全匹配，它实际上可以是^A0111$，但是不区分大小写的匹配会导致出现问题，从而导致对可能的索引进行较大的扫描.因此，有一种更好的方式来编写它.另请参阅文档链接，以解决不区分大小写的匹配问题.

Also the regex will not match the values that you want. It could actualy be ^A0111$ for the exact match, but your case insensitive match causes a problem causing a larger scan of a possible index. So there is a better way to write that. Also see the documentation link for the problems with case insensitive matches.

使用 $regex 运算符:

db.categories.find({
    "$and": [
        {"company":"New Company"},
        { "$or": [
            { "worktypes.works.name": { "$regex": "^A00011$|^a00011$" }},
            { "worktypes.works.code": { "$regex": "^A00011$|^a00011$" }}
        ]}
    ]
})

$位置占位符也不适用于查询，它们仅用于投影或更新或查询找到的第一匹配元素.

Also the positional $ placeholders are not valid for a query, they are only used in projection or an update or the first matching element found by the query.

但是您的实际问题似乎是您试图仅获取与条件匹配"的数组元素.您无法使用.find()执行此操作，为此您需要使用 .aggregate() 代替:

But your actual problem seems to be that you are trying to only get the elements of an array that "match" your conditions. You cannot do this with .find() and for that you need to use .aggregate() instead:

db.categories.aggregate([

    // Always makes sense to match the actual documents
    { "$match": {
        "$and": [
            {"company":"New Company"},
            { "$or": [
                { "worktypes.works.name": { "$regex": "^A00011$|^a00011$" }},
                { "worktypes.works.code": { "$regex": "^A00011$|^a00011$" }}
            ]}
        ]
    }},

    // Unwind the worktypes array
    { "$unwind": "$worktypes" },

    // Unwind the works array
    { "$unwind": "$worktypes.works" },

    // Then use match to filter only the matching entries
    { "$match": {
       "$or": [
            { "worktypes.works.name": { "$regex": "^A00011$|^a00011$" } },
            { "worktypes.works.code": { "$regex": "^A00011$|^a00011$" } }
        ]
    }},

    /* Stop */
    // If you "really" need the arrays back then include all the following
    // Otherwise the steps up to here actually got you your results

    // First put the "works" array back together
    { "$group": {
        "_id": {
            "_id": "$_id",
            "company": "$company",
            "workname": "$worktypes.name"
        },
        "works": { "$push": "$worktypes.works" }
    }},

    // Then put the "worktypes" array back
    { "$group": {
        "_id": "$_id._id",
        "company": { "$first": "$_id.company" },
        "worktypes": {
            "$push": {
                "name": "$_id.workname",
                "works": "$works"
            } 
        } 
    }}
])

.aggregate() 可以完成所有这些步骤，因为它将数组元素分解为普通文档形式，因此可以使用 $match 运算符对其进行过滤.这样，仅返回匹配"的元素.

So what .aggregate() does with all of these stages is it breaks the array elements into normal document form so they can be filtered using the $match operator. In that way, only the elements that "match" are returned.

查找"的正确操作是匹配满足条件的文档".由于文档包含匹配的元素，因此将它们返回.这两个原则是完全不同的.

What "find" is correctly doing is matching the "document" that meets the conditions. Since documents contain the elements that match then they are returned. The two principles are very different things.

当您打算过滤"时，请使用聚合.

When you mean to "filter" use aggregate.

这篇关于正则表达式在mongoDB中的数组内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！