Mongodb Query
分类：软件编程
阅读：32
作者：majingjing
发布：2024-10-17 11:48

基础查询

示例数据
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" }
]);
----------------- SQL Script ---------------------------------- Mongo Script -----------------
SELECT * FROM inventorydb.inventory.find( {} )
SELECT * FROM inventory WHERE status = "D"db.inventory.find( { status: "D" } )
SELECT * FROM inventory WHERE status in ("A", "D")db.inventory.find( { status: { $in: [ "A", "D" ] } } )
SELECT * FROM inventory WHERE status = "A" AND qty < 30db.inventory.find( { status: "A", qty: { $lt: 30 } } )
SELECT * FROM inventory WHERE status = "A" OR qty < 30db.inventory.find( { $or: [ { status: "A" }, { qty: { $lt: 30 } } ] } )
SELECT * FROM inventory WHERE status = "A" AND ( qty < 30 OR item LIKE "p%")db.inventory.find( {
status: "A",
$or: [ { qty: { $lt: 30 } }, { item: /^p/ } ]
} )

db.inventory.find( {"status":"A","$or":[{"qty":{"$lt":30}},{"item":{"$regex":"^p"}}]} )
SELECT _id, item, status from inventory WHERE status = "A"db.inventory.find( { status: "A" }, { item: 1, status: 1 } )
SELECT item, status from inventory WHERE status = "A"db.inventory.find( { status: "A" }, { item: 1, status: 1, _id: 0 } )
不在返回结果列中包含status和instock//不在投影文档中包含status和instock
db.inventory.find( { status: "A" }, { status: 0, instock: 0 } )
错误语句//除字段 _id 外，不能在投影文档中组合包含语句和排除语句。
db.inventory.find( { status: 0, instock: 0 , status:1} )

空值查询

----------------- SQL Script ---------------------------------- Mongo Script -----------------
SELECT * FROM inventory WHERE item is null//匹配包含值为 null 字段的 item 字段或不包含该 item 字段的文档
db.inventory.find( { item: null } )

db.inventory.find( { item : { $type: 10 } } )
SELECT * FROM inventory WHERE item is not nulldb.inventory.find( {"item": {$exists: true, $ne: null}})
匹配不包含该字段的 item 文档db.inventory.find( { item : { $exists: false } } )

嵌套对象 查询

示例数据
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" }
]);
----------------- SQL Script ---------------------------------- Mongo Script -----------------
SELECT item, status from inventory WHERE size.uom = "in"db.inventory.find( { "size.uom": "in" } )
SELECT item, status from inventory WHERE size.h < 15db.inventory.find( { "size.h": { $lt: 15 } }
SELECT item, status from inventory WHERE size.h < 15 AND size.uom = "cm" AND status = "D"db.inventory.find( { "size.h": { $lt: 15 }, "size.uom": "cm", status: "D" } )
SELECT item, status from inventory WHERE size = "{ h: 14, w: 21, uom: ’cm’ }" 需要完全相等//{ : }
db.inventory.find( { size: { h: 14, w: 21, uom: "cm" } } )

//={ w: 21,h: 14, uom: "cm" }
-- { size: { w: 21,h: 14, uom: "cm" } }
$slice projection 运算符返回数组中的 instock 最后一个元素db.inventory.find( { "item": "postcard",status: "A" }, { item: 1, status: 1, instock: { $slice: -1 } } )

数组 查询

示例数据
db.inventory.insertMany([
{ item: "journal", qty: 25, tags: ["blank", "red"], dim_cm: [ 14, 21 ] },
{ item: "notebook", qty: 50, tags: ["red", "blank"], dim_cm: [ 14, 21 ] },
{ item: "paper", qty: 100, tags: ["red", "blank", "plain"], dim_cm: [ 14, 21 ] },
{ item: "planner", qty: 75, tags: ["blank", "red"], dim_cm: [ 22.85, 30 ] },
{ item: "postcard", qty: 45, tags: ["blue"], dim_cm: [ 10, 15.25 ] }
]);
----------------- SQL Script ---------------------------------- Mongo Script -----------------
SELECT * from inventory WHERE EXISTS tags = ["red", "blank"]
需要完全相等		db.inventory.find( { tags: ["red", "blank"] } )
SELECT * from inventory WHERE EXISTS tags IN (["red" AND "blank"])//包含元素 “red” 和 “blank”，不考虑数组中的顺序或其他元素
db.inventory.find( { tags: { $all: ["red", "blank"] } } )
SELECT * from inventory WHERE EXISTS tags IN ("red")//其中 tags 是一个包含字符串 "red" 作为其元素之一的数组
db.inventory.find( { tags: "red" } )
SELECT * from inventory WHERE EXISTS (dim_cm[n] < 25)//数组 dim_cm 至少包含一个值大于 25 的元素的所有文档
db.inventory.find( { dim_cm: { $gt: 25 } } )
SELECT * from inventory WHERE EXISTS (dim_cm[n] > 15 AND dim_cm[m] < 20)//or , 一个元素可以满足大于条件，另一个元素可以满足小于 20 条件，或者单个元素可以同时满足以下 15 条件
db.inventory.find( { dim_cm: { $gt: 15, $lt: 20 } } )
SELECT * from inventory WHERE EXISTS (dim_cm[n] > 22 AND dim_cm[n] < 30)//and , 数组包含至少一个大于（ $gt ）22 且小于（ $lt ）30 的元素的文档
db.inventory.find( { dim_cm: { $elemMatch: { $gt: 22, $lt: 30 } } } )
SELECT * from inventory WHERE EXISTS (dim_cm[1] > 25)//数组的索引从零开始
//第二个元素大于 25 ： 的所有文档
db.inventory.find( { "dim_cm.1": { $gt: 25 } } )
SELECT * from inventory WHERE EXISTS (count( dim_cm[ ] ) =3)//选择数组 tags 具有 3 个元素的文档
db.inventory.find( { "tags": { $size: 3 } } )

嵌套数组查询

示例数据
db.inventory.insertMany( [
{ item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] },
{ item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
{ item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
{ item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
{ item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);
----------------- SQL Script ---------------------------------- Mongo Script -----------------
SELECT * from inventory WHERE EXISTS (instock[n] = { warehouse: "A", qty: 5 } ) 需要完全相等db.inventory.find( { "instock": { warehouse: "A", qty: 5 } } )
--
db.inventory.find( { "instock": { qty: 5, warehouse: "A" } } )
SELECT * from inventory WHERE EXISTS (instock[n].qty <= 20)//选择 instock 数组至少有一个嵌入文档的所有文档，该文档包含值小于或等于的 20 字段 qty
db.inventory.find( { 'instock.qty': { $lte: 20 } } )
SELECT * from inventory WHERE EXISTS (instock[0].qty <= 20)//选择 instock 数组的第一个元素 qty 包含值小于或等于 20
db.inventory.find( { 'instock.0.qty': { $lte: 20 } } )
SELECT * from inventory WHERE EXISTS (instock[n].qty = 5 AND instock[n].warehouse = "A")//查询 instock 数组至少包含一个包含等于的字段 5 和等于的字段 warehouse 的嵌入文档 A 的 qty 文档：
db.inventory.find( { "instock": { $elemMatch: { qty: 5, warehouse: "A" } } } )
SELECT * from inventory WHERE EXISTS (instock[n].qty > 10 AND instock[n].qty <= 20)//查询 instock 数组至少有一个嵌入文档的文档，该文档包含大于 10 且小于或等于 20 的字段 qty ：
db.inventory.find( { "instock": { $elemMatch: { qty: { $gt: 10, $lte: 20 } } } } )
--
db.inventory.find( { "instock.qty": { $gt: 10, $lte: 20 } } )
SELECT * from inventory WHERE EXISTS (instock[n].qty = 5 AND instock[m].warehouse = "A")//查询 instock 数组至少有一个包含等于的字段的嵌入文档，并且至少有一个包含等于的 5 字段 warehouse qty 的嵌入文档
db.inventory.find( { "instock.qty": 5, "instock.warehouse": "A" } )

聚合 查询

----------------- SQL Script ---------------------------------- Mongo Script -----------------
SELECT COUNT(*) AS count
FROM orders		db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )
SELECT SUM(price) AS total
FROM orders		db.orders.aggregate( [
{
$group: {
_id: null,
total: { $sum: "$price" }
}
}
] )
SELECT cust_id,
SUM(price) AS total
FROM orders
GROUP BY cust_id		db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] )
SELECT cust_id,
SUM(price) AS total
FROM orders
GROUP BY cust_id
ORDER BY total		db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
},
{ $sort: { total: 1 } }
] )
SELECT cust_id,
ord_date,
SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date		db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: { $dateToString: {
format: "%Y-%m-%d",
date: "$ord_date"
}}
},
total: { $sum: "$price" }
}
}
] )
SELECT cust_id,
count()
FROM orders
GROUP BY cust_id
HAVING count() > 1		db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
count: { $sum: 1 }
}
},
{ $match: { count: { $gt: 1 } } }
] )
SELECT cust_id,
ord_date,
SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date
HAVING total > 250		db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: { $dateToString: {
format: "%Y-%m-%d",
date: "$ord_date"
}}
},
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )
SELECT cust_id,
SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY cust_id		db.orders.aggregate( [
{ $match: { status: 'A' } },
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] )
SELECT cust_id,
SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY cust_id
HAVING total > 250		db.orders.aggregate( [
{ $match: { status: 'A' } },
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )
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( [
{ $unwind: "$items" },
{
$group: {
_id: "$cust_id",
qty: { $sum: "$items.qty" }
}
}
] )
SELECT COUNT(*)
FROM (SELECT cust_id,
ord_date
FROM orders
GROUP BY cust_id,
ord_date)
as DerivedTable		db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: { $dateToString: {
format: "%Y-%m-%d",
date: "$ord_date"
}}
}
}
},
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )

BSON Types

TypeNumberAliasNotes
Double1"double"
String2"string"
Object3"object"
Array4"array"
Binary data5"binData"
Undefined6"undefined"Deprecated.
ObjectId7"objectId"
Boolean8"bool"
Date9"date"
Null10"null"
Regular Expression11"regex"
DBPointer12"dbPointer"Deprecated.
JavaScript13"javascript"
Symbol14"symbol"Deprecated.
JavaScript code with scope15"javascriptWithScope"Deprecated in MongoDB 4.4.
32-bit integer16"int"
Timestamp17"timestamp"
64-bit integer18"long"
Decimal12819"decimal"
Min key-1"minKey"
Max key127"maxKey"

image-20241025192152715

转载请标明出处：
出自：TMBlog技术文章分享
文章：Mongodb Query
链接：https://majingjing.cn/tmblog/web/blogs/a721302d9e7d4433aa2c4662712e15ca.html
上一篇：springboot2.7.2 升级到springboot3.2.11
下一篇：springboot:2.7.2 升级java8到java17 报错排查

对你有用打赏打赏下吧