MongoDB查詢總結(jié)
介紹
前面寫過一篇關(guān)于Mongo?db的例子——淺談MongoDB數(shù)據(jù)庫，當時使用的只是簡單的查詢辩涝，然后后面業(yè)務(wù)變的有點復(fù)雜恋技，原先沒有仔細研究過Mongodb的查詢肝集，以為就是簡單調(diào)用下find就可以了肿孵，乃衣服却盘。
所以今天特地舉例說明一下Mongo中查詢問題栅干。
Mongo查詢可以?分為2種：
普通查詢碍讨，類似于Sql中的
select where聚合查詢，類似于Sql中的
group by
普通查詢
首先放一下官方文檔茂蚓，普通查詢主要用到db.collection.find()函數(shù)壕鹉。
定義下示例數(shù)據(jù)庫，下面是是初始化數(shù)據(jù)聋涨，可以在Mongo中的控制臺?執(zhí)行晾浴。
db.inventory.insertMany([
{ item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
{ item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" },
{ item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" },
{ item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" },
{ item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" }
]);
- 查詢所有
db.inventory.find( {} )
映射Sql語句
SELECT * FROM inventory
- 條件查詢
語法格式
{ <field1>: <value1>, ... }
比如查詢status為D記錄。
db.inventory.find( { status: "D" } )
映射Sql語句
SELECT * FROM inventory WHERE status = "D"
- 使用操作符進行條件查詢
語法格式
{ <field1>: { <operator1>: <value1> }, ... }
比如查詢滿足status是?數(shù)組[A,D]中的記錄
db.inventory.find( { status: { $in: [ "A", "D" ] } } )
映射Sql語句
SELECT * FROM inventory WHERE status in ("A", "D")
- AND 條件查詢
直接在find函數(shù)指定多個字段滿足即可牍白，這樣就是 and 條件脊凰。
比如下面語句就是 status 為 A，qty 小于 30。
db.inventory.find( { status: "A", qty: { $lt: 30 } } )
映射Sql語句
SELECT * FROM inventory WHERE status = "A" AND qty < 30
- OR 條件查詢
?OR 和 AND 就不一樣了狸涌，需要用到操作符 $or切省，如下所示。
db.inventory.find( { $or: [ { status: "A" }, { qty: { $lt: 30 } } ] } )
類似于SQL中的
SELECT * FROM inventory WHERE status = "A" OR qty < 30
- OR 和 AND 集合一起
db.inventory.find( {
status: "A",
$or: [ { qty: { $lt: 30 } }, { item: /^p/ } ]
} )
表示這樣的意思帕胆。
SELECT * FROM inventory WHERE status = "A" AND ( qty < 30 OR item LIKE "p%")
查詢舉例
- 查詢?nèi)?/li>
SELECT *
FROM people
db.people.find()
- 指定字段
SELECT id,
user_id,
status
FROM people
db.people.find(
{ },
{ user_id: 1, status: 1 }
)
SELECT user_id, status
FROM people
- 指定字段朝捆，不顯示
_id
db.people.find(
{ },
{ user_id: 1, status: 1, _id: 0 }
)
- 條件查詢?nèi)?/li>
SELECT *
FROM people
WHERE status = "A"
db.people.find(
{ status: "A" }
)
- 條件查詢?指定字段
SELECT user_id, status
FROM people
WHERE status = "A"
db.people.find(
{ status: "A" },
{ user_id: 1, status: 1, _id: 0 }
)
- 條件查詢不等于
SELECT *
FROM people
WHERE status != "A"
db.people.find(
{ status: { $ne: "A" } }
)
- 條件查詢 AND
SELECT *
FROM people
WHERE status = "A"
AND age = 50
db.people.find(
{ status: "A",
age: 50 }
)
- 條件查詢 OR
SELECT *
FROM people
WHERE status = "A"
OR age = 50
db.people.find(
{ $or: [ { status: "A" } ,
{ age: 50 } ] }
)
- 條件查詢 ?>
SELECT *
FROM people
WHERE age > 25
db.people.find(
{ age: { $gt: 25 } }
)
- 條件查詢 ?<
SELECT *
FROM people
WHERE age < 25
db.people.find(
{ age: { $lt: 25 } }
)
- ?復(fù)雜的條件查詢
SELECT *
FROM people
WHERE age > 25
AND age <= 50
db.people.find(
{ age: { $gt: 25, $lte: 50 } }
)
- 條件查詢 ?LIKE
SELECT *
FROM people
WHERE user_id like "%bc%"
db.people.find( { user_id: /bc/ } )
// OR
db.people.find( { user_id: { $regex: /bc/ } } )
SELECT *
FROM people
WHERE user_id like "bc%"
db.people.find( { user_id: /^bc/ } )
// OR
db.people.find( { user_id: { $regex: /^bc/ } } )
- 排序
SELECT *
FROM people
WHERE status = "A"
ORDER BY user_id ASC
db.people.find( { status: "A" } ).sort( { user_id: 1 } )
SELECT *
FROM people
WHERE status = "A"
ORDER BY user_id DESC
db.people.find( { status: "A" } ).sort( { user_id: -1 } )
- 統(tǒng)計數(shù)量
SELECT COUNT(*)
FROM people
db.people.count()
// or
db.people.find().count()
SELECT COUNT(user_id)
FROM people
db.people.count( { user_id: { $exists: true } } )
or
db.people.find( { user_id: { $exists: true } } ).count()
SELECT COUNT(*)
FROM people
WHERE age > 30
db.people.count( { age: { $gt: 30 } } )
// or
db.people.find( { age: { $gt: 30 } } ).count()
- 去除重復(fù)distinct
SELECT DISTINCT(status)
FROM people
db.people.distinct( "status" )
SELECT *
FROM people
LIMIT 1
- 限制數(shù)量
db.people.findOne()
// or
db.people.find().limit(1)
SELECT *
FROM people
LIMIT 5
SKIP 10
db.people.find().limit(5).skip(10)
- EXPLAIN
EXPLAIN SELECT *
FROM people
WHERE status = "A"
db.people.find( { status: "A" } ).explain()
聚合查詢
上面?普通查詢使用find函數(shù)即可，但是聚合查詢使用另外一個函數(shù)aggregate懒豹，這里是官方文檔芙盘。
初始化數(shù)據(jù)如下，有2個表 orders 和 order_lineitem ?脸秽，外鍵關(guān)聯(lián)order_lineitem.order_id and the orders.id儒老。
{
cust_id: "abc123",
ord_date: ISODate("2012-11-02T17:04:11.102Z"),
status: 'A',
price: 50,
items: [ { sku: "xxx", qty: 25, price: 1 },
{ sku: "yyy", qty: 25, price: 1 } ]
}
- 統(tǒng)計數(shù)量
db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )
映射Sql語句
SELECT COUNT(*) AS count
FROM orders
- 計算總和
db.orders.aggregate( [
{
$group: {
_id: null,
total: { $sum: "$price" }
}
}
] )
映射Sql語句
SELECT SUM(price) AS total
FROM orders
- 分組計算總和
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] )
映射Sql語句
SELECT cust_id,
SUM(price) AS total
FROM orders
GROUP BY cust_id
- 分組計算總和并排序
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
},
{ $sort: { total: 1 } }
] )
映射Sql語句
SELECT cust_id,
SUM(price) AS total
FROM orders
GROUP BY cust_id
ORDER BY tota
- 多個字段分組
db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
}
] )
映射Sql語句
SELECT cust_id,
ord_date,
SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date
- 條件分組——HAVING
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
count: { $sum: 1 }
}
},
{ $match: { count: { $gt: 1 } } }
] )
映射Sql語句
SELECT cust_id,
count(*)
FROM orders
GROUP BY cust_id
HAVING count(*) > 1
- 復(fù)雜條件分組統(tǒng)計
db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )
映射Sql語句
SELECT cust_id,
ord_date,
SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date
HAVING total > 250
- 復(fù)雜條件分組統(tǒng)計示例1
db.orders.aggregate( [
{ $match: { status: 'A' } },
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] )
映射Sql語句
SELECT cust_id,
SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY cust_id
- 復(fù)雜條件分組統(tǒng)計示例2
db.orders.aggregate( [
{ $match: { status: 'A' } },
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )
映射Sql語句
SELECT cust_id,
SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY cust_id
HAVING total > 250
- 表關(guān)聯(lián)
db.orders.aggregate( [
{ $unwind: "$items" },
{
$group: {
_id: "$cust_id",
qty: { $sum: "$items.qty" }
}
}
] )
映射Sql語句
SELECT cust_id,
SUM(li.qty) as qty
FROM orders o,
order_lineitem li
WHERE li.order_id = o.id
GROUP BY cust_id
- 嵌套查詢
db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
}
}
},
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )
映射Sql語句
SELECT COUNT(*)
FROM (SELECT cust_id,
ord_date
FROM orders
GROUP BY cust_id,
ord_date)
as DerivedTable
Map-Reduce
Mongo中聚合查詢還有一種叫Map-Reduce，官方文檔在這里记餐，在思想上它跟Hadoop一樣驮樊，從一個單一集合中輸入數(shù)據(jù)，然后將結(jié)果輸出到一個集合中片酝。通常在使用類似SQL中Group By操作時囚衔，Map/Reduce會是一個好的工具。
Map-Reduce
接口方法定義
db.collection.mapReduce(
<map>,
<reduce>,
{
out: <collection>,
query: <document>,
sort: <document>,
limit: <number>,
finalize: <function>,
scope: <document>,
jsMode: <boolean>,
verbose: <boolean>,
bypassDocumentValidation: <boolean>
}
)
參數(shù)說明
mapReduce： 要執(zhí)行Map/Reduce集合的名字
map: map 函數(shù) (下面會詳細介紹）
reduce: reduce函數(shù)(下面會詳細介紹）
out: 存放結(jié)果的集合 (下面會詳細介紹）
query: 設(shè)置查詢條件 <可選>
sort: 按某個鍵來排序 <可選>
limit: 指明從集合檢索文檔個數(shù)的最大值 <可選>
finalize: 對reduce結(jié)果做進一步處理 <可選>
scope: 指明通過map/reduce/finalize可以訪問到的變量 <可選>
jsMode: 指明Map/Reduce執(zhí)行過程中文檔保持JSON狀態(tài) <可選>
verbose: 提供關(guān)于任務(wù)執(zhí)行的統(tǒng)計數(shù)據(jù) <可選>
示例說明
?舉例說明Map-Reduce的用途钠怯，?雖然代碼比較多佳魔，也行用上面的聚合查詢，一下子就搞定了晦炊，但是這里只是舉例鞠鲜。
比如有個訂單表，如下所示断国，我們需要計算每個人的訂單總價贤姆。
{
_id: ObjectId("50a8240b927d5d8b5891743c"),
cust_id: "abc123",
ord_date: new Date("Oct 04, 2012"),
status: 'A',
price: 25,
items: [ { sku: "mmm", qty: 5, price: 2.5 },
{ sku: "nnn", qty: 5, price: 2.5 } ]
}
首先定義Map方法，就說我們后面的聚合計算需要哪些字段稳衬，?由于需要計算每個人的訂單總結(jié)霞捡，那么個人信息和加個肯定是我們需要的。
var mapFunction1 = function() {
emit(this.cust_id, this.price);
};
然后定義reduce方法薄疚，計算每個人的訂單價格碧信。
var reduceFunction1 = function(keyCustId, valuesPrices) {
return Array.sum(valuesPrices);
};
然后存儲最后的計算結(jié)果。
db.orders.mapReduce(
mapFunction1,
reduceFunction1,
{ out: "map_reduce_example" }
)
這樣一個簡單的Map-Reduce實例就完成了街夭，結(jié)果放在map_reduce_example中砰碴。
上面示例比較簡單，那么我們來一個復(fù)雜一點的例子板丽。
一條訂單記錄中呈枉，有sdk的名稱、數(shù)量、價格猖辫，那么要查詢出日期大于01/01/2012酥泞，所有訂單的總數(shù)，以及?平均sdk價格啃憎。
首先還是定義個map函數(shù)芝囤。
var mapFunction2 = function() {
for (var idx = 0; idx < this.items.length; idx++) {
var key = this.items[idx].sku;
var value = {
count: 1,
qty: this.items[idx].qty
};
emit(key, value);
}
};
然后算出sku的數(shù)量，和總價格辛萍。
var reduceFunction2 = function(keySKU, countObjVals) {
reducedVal = { count: 0, qty: 0 };
for (var idx = 0; idx < countObjVals.length; idx++) {
reducedVal.count += countObjVals[idx].count;
reducedVal.qty += countObjVals[idx].qty;
}
return reducedVal;
};
總價格出來后凡人，還要計算出平均價格。
var finalizeFunction2 = function (key, reducedVal) {
reducedVal.avg = reducedVal.qty / reducedVal.count;
return reducedVal;
};
還有日期的條件過濾叹阔，最后得出完整的map-reduce。
db.orders.mapReduce(
mapFunction2,
reduceFunction2,
{
out: { merge: "map_reduce_example" },
query: {
ord_date:{ $gt: new Date('01/01/2012') }
},
finalize: finalizeFunction2
}
)
總結(jié)
以上就是我對MongoDB的示例總結(jié)传睹，本人是一個初學(xué)者耳幢，也有很多地方不懂，如果有錯誤的地方欧啤，歡迎指出睛藻。
