在 MongoDB 中查询和过滤键名而不是值
我想从一个集合中找到与某个字符串部分匹配的所有键名.
I want to find all key names from a collection that partially match a certain string.
我得到的最接近的方法是检查某个键是否存在，但这是完全匹配的:
The closest I got was to check if a certain key exists, but that's an exact match:
db.collection.find({ "fkClientID": { $exists:1 }})
我想获取所有以 fk 开头的键.
I'd like to get all keys that start with fk instead.
推荐答案
如果您拥有最新的 MongoDB 3.4.4，那么您可以在带有 $objectToArray/aggregation/redact/" rel="nofollow noreferrer">$redact 作为 最 最快的方式，这可能是使用本机运算符完成的.并不是说扫描集合是快速的".但尽可能快地做到这一点:
If you have the latest MongoDB 3.4.4 then you can use $objectToArray in an aggregate statement with $redact as the the most blazing fast way this can possibly be done with native operators. Not that scanning the collection is "fast". but as fast as you get for this:
db[collname].aggregate([
{ "$redact": {
"$cond": {
"if": {
"$gt": [
{ "$size": { "$filter": {
"input": { "$objectToArray": "$$ROOT" },
"as": "doc",
"cond": {
"$eq": [ { "$substr": [ "$$doc.k", 0, 2 ] }, "fk" ]
}
}}},
0
]
},
"then": "$$KEEP",
"else": "$$PRUNE"
}
}}
])
目前未记录的 $objectToArray 将对象"转换为数组中的键"和值"形式.所以这个:
The presently undocumented $objectToArray translates an "object" into "key" and "value" form in an array. So this:
{ "a": 1, "b": 2 }
变成这样:
[{ "k": "a", "v": 1 }, { "k": "b", "v": 2 }]
与 $$ROOT 一起使用，这是一个引用当前文档对象"的特殊变量，我们将其转换为数组，因此 k" 的值可以是检查.
Used with $$ROOT which is a special variable referring to the current document "object", we translate to an array so the values of "k" can be inspected.
那么只需要应用$filter并使用$substr获取key"字符串的前面字符即可.
Then it's just a matter of applying $filter and using $substr to get the preceding characters of the "key" string.
为了记录，这将是 MongoDB 3.4.4 获取匹配键的唯一列表的最佳方式:
For the record, this would be the MongoDB 3.4.4 optimal way of obtaining an unique list of the matching keys:
db[collname].aggregate([
{ "$redact": {
"$cond": {
"if": {
"$gt": [
{ "$size": { "$filter": {
"input": { "$objectToArray": "$$ROOT" },
"as": "doc",
"cond": {
"$eq": [ { "$substr": [ "$$doc.k", 0, 2 ] }, "fk" ]
}
}}},
0
]
},
"then": "$$KEEP",
"else": "$$PRUNE"
}
}},
{ "$project": {
"j": {
"$filter": {
"input": { "$objectToArray": "$$ROOT" },
"as": "doc",
"cond": {
"$eq": [ { "$substr": [ "$$doc.k", 0, 2 ] }, "fk" ]
}
}
}
}},
{ "$unwind": "$j" },
{ "$group": { "_id": "$j.k" }}
])
这是安全的规定，考虑到密钥可能不会出现在所有文档中，并且文档中可能存在多个密钥.
That's the safe provision, which is considering that the key may not be present in all documents and that there could possibly be multiple keys in the document.
如果您绝对确定您总是"在文档中拥有密钥并且只有一个，那么您可以缩短为 $group:
If you are absolutely certain that you "always" have the key present in the document and that there will only be one, then you can shorten to just $group:
db[colname].aggregate([
{ "$group": {
"_id": {
"$arrayElemAt": [
{ "$map": {
"input": { "$filter": {
"input": { "$objectToArray": "$$ROOT" },
"as": "doc",
"cond": {
"$eq": [ { "$substr": [ "$$doc.k", 0, 2 ] }, "fk" ]
}
}},
"as": "el",
"in": "$$el.k"
}},
0
]
}
}}
])
<小时>
在早期版本中最有效的方法是使用 $where 语法，允许 JavaScript 表达式求值.并不是说任何评估 JavaScript 的东西都是你能做的最"有效的事情，但是分析键"而不是数据"对于任何数据存储来说都不是最佳的:
The most efficient way in earlier versions would be using the $where syntax that allows a JavaScript expression to evaluate. Not that anything that evaluates JavaScript is the "most" efficient thing you can do, but analyzing "keys" as opposed to "data" is not optimal for any data store:
db[collname].find(function() { return Object.keys(this).some( k => /^fk/.test(k) ) })
内联function只是shell简写，也可以写成:
The inline function there is just shell shorthand and this could also be written as:
db[collname].find({ "$where": "return Object.keys(this).some( k => /^fk/.test(k) )" })
$where<的唯一要求/a> 是该表达式为您要返回的任何文档返回 true 值，因此文档返回原样.
The only requirement for $where is that the expression returns a true value for any document you want to return, so the documents return unaltered.
相关文章