mongodb 查询表数据大小（MongoDB之lookup联表规则整理）
mongodb 查询表数据大小（MongoDB之lookup联表规则整理）使用$in，确保字段数据存在数组中，这样的匹配结果存在错误的数据，所以之后需要进行拆分再次进行$eq进行匹配db.bill.aggregate([ { "$lookup": { "from": "in_store" "let": { "billGoods": "$goods" } "pipeline": [{ "$match": { "$expr": { "$and": [ {"$eq&#
$lookup官方文档https://www.docs4dev.com/docs/zh/mongodb/v3.6/reference/reference-operator-aggregation-lookup.html#lookup-multiple-joins
创建测试数据
db.classes.insert( [
{ _id: 1 title: "Reading is ..." enrollmentlist: [ "giraffe2" "pandabear" "artie" ] days: ["M" "W" "F"] }
{ _id: 2 title: "But Writing ..." enrollmentlist: [ "giraffe1" "artie" ] days: ["T" "F"] }
])
db.members.insert( [
{ _id: 1 name: "artie" joined: new Date("2016-05-01") status: "A" }
{ _id: 2 name: "giraffe" joined: new Date("2017-05-01") status: "D" }
{ _id: 3 name: "giraffe1" joined: new Date("2017-10-01") status: "A" }
{ _id: 4 name: "panda" joined: new Date("2018-10-11") status: "A" }
{ _id: 5 name: "pandabear" joined: new Date("2018-12-01") status: "A" }
{ _id: 6 name: "giraffe2" joined: new Date("2018-12-01") status: "D" }
])
商品资料：goods
订单：bill 数组字段：goods
入库单：in_store 数组字段：in_goods
业务背景：
一个订单可以产生多个入库单，
in_bill.bill_no与bill.bill_no对应，
bill.goods.code与in_bill.in_goods.code对应
bill = {
"bill_no" : "D20210723178"
"goods" : [
{
"amount" : 21
"goods_code" : "100009464821"
"price" : { "$numberDecimal" : "9999.00" }
"goods_id" : "1007"
}
]
}
in_bill = {
"in_no" : "RK20210723177"
"bill_no" : "D20210723178"
"in_goods" : [
{
"goods_code" : "100009464821"
"goods_id" : "1007"
"in_num" : 1
"in_price" : { "$numberDecimal" : "5499.00" }
}
]
}
goods = {
"goods_code" : "100012015170"
"id" : "1001"
"goods_name" : "华为 HUAWEI P40 Pro"
"cost_price" : 3500.0
"sale_price" : 6488.25
"market_price" : 7488.23
}
关于$expr表达式
$expr可以构建查询表达式来比较$match阶段中同一文档的字段。
考虑一个monthlybudget包含以下文档的集合：
{ "_id" : 1 "category" : "food" "budget": 400 "spent": 450 }
{ "_id" : 2 "category" : "drinks" "budget": 100 "spent": 150 }
{ "_id" : 3 "category" : "clothes" "budget": 100 "spent": 50 }
{ "_id" : 4 "category" : "misc" "budget": 500 "spent": 300 }
{ "_id" : 5 "category" : "travel" "budget": 200 "spent": 650 }
以下操作用于$expr查找spent金额超过 的文档budget：
db.monthlyBudget.find( { $expr: { $gt: [ "$spent" "$budget" ] } } )
直接通过数组字段进行关联的方式，不符合实际业务需要
类似下面的这种，直接通过bill.goods与in_store.in_goods进行关联，虽然可以允许，但是语法的解析为：
bill.goods里面的数组数据与in_store.in_goods里面的数组数据完全相同才算关联上
1】要求两边的数据行数一样，数据量是一致的（比如都有三条数据）
2】在嵌套字段goods_code维度，主对象的每行数据都能在关联对象的数组数据上匹配上
db.bill.aggregate([
{
"$lookup": {
"from": "in_store"
"let": {
"billGoods": "$goods"
}
"pipeline": [{
"$match": {
"$expr": {
"$and": [
{"$eq": ["$in_goods.goods_code" "$$billGoods.goods_code"]}
]
}
}
}]
"as": "inStore"
}
}
])
bill.goods.goods_code与goods.goods_code进行关联
使用$in，确保字段数据存在数组中，这样的匹配结果存在错误的数据，所以之后需要进行拆分再次进行$eq进行匹配
db.bill.aggregate([
{
"$lookup": {
"from": "goods"
"let": {
"billGoods": "$goods"
}
"pipeline": [{
"$match": {
"$expr": {
"$and": [
{"$in": ["$goods_code" "$$billGoods.goods_code"]}
]
}
}
}]
"as": "bill|goods"
}
}
{
"$unwind": {
"path": "$bill|goods"
"preserveNullAndEmptyArrays": false
}
}
{
"$unwind": {
"path": "$goods"
"preserveNullAndEmptyArrays": false
}
}
{
'$project': {
'bill_no': 1
'total_goods_num': 1
'goods': 1
'userID': 1
'bill|goods': 1
'isEqual': {
'$eq': ['$goods.goods_code' '$bill|goods.goods_code']
}
}
}
{
'$match': {
'isEqual': true
}
}
])
第二种：主表-字段 与 联表-数组 进行关联查询
goods.goods_code与bill.goods.goods_code进行关联
db.goods.aggregate([
{
"$lookup": {
"from": "bill"
"let": {
"goodsCode": "$goods_code"
}
"pipeline": [{
"$match": {
"$expr": {
"$and": [
{"$in": ["$$goodsCode" "$goods.goods_code"]}
]
}
}
}]
"as": "goods|bill"
}
}
{
"$unwind": {
"path": "$goods|bill"
"preserveNullAndEmptyArrays": false
}
}
{
"$unwind": {
"path": "$goods|bill.goods"
"preserveNullAndEmptyArrays": false
}
}
{
'$project': {
'goods_code': 1
'id': 1
'goods_name': 1
'cost_price': 1
'sale_price': 1
'market_price': 1
'goods|bill': 1
'isEqual': {
'$eq': ['$goods_code' '$goods|bill.goods.goods_code']
}
}
}
{
'$match': {
'isEqual': true
}
}
])
第三种：主表-数组 与 联表-数组 进行关联查询
bill.goods.goodscode与in_store.in_goods.goods_code进行关联
1、先通过主字段进行关联（若有多个，则多个关联）
2、将数组字段进行unwind拆分
3、通过两边的数组字段进行关联
如果需要将bill.goods.code与in_bill.goods.code进行关联，则必须有个前提，是bill.bill_no=in_bill.bill_no先进行关联
首先通过bill.bill_no=in_bill.bill_no进行关联
然后，将关联之后产生的集合，先将别名集合数组拆分，依次按照bill.goods、in_bill.goods进行unwind拆分
再者，用bill.goods.code=in_bill.goods.code进行关联
两边数组联表查询示例
db.bill.aggregate([
{
"$lookup": {
"from": "in_store"
"let": {
"billNo": "$bill_no"
}
"pipeline": [{
"$match": {
"$expr": {
"$and": [
{"$eq": ["$bill_no" "$$billNo"]}
]
}
}
}]
"as": "inStore"
}
}
{
"$unwind": {
"path": "$inStore"
"preserveNullAndEmptyArrays": false
}
}
{
"$unwind": {
"path": "$goods"
"preserveNullAndEmptyArrays": false
}
}
{
"$unwind": {
"path": "$inStore.in_goods"
"preserveNullAndEmptyArrays": false
}
}
{
'$project': {
'bill_no': 1
'total_goods_num': 1
'goods': 1
'userID': 1
'in_no': '$inStore.in_no'
'in_goods': '$inStore.in_goods'
'isEqual': {
'$eq': ['$goods.goods_code' '$inStore.in_goods.goods_code']
}
}
}
{
'$match': {
'isEqual': true
}
}
])第四种：主表-对象.字段 与 联表-数组 进行关联查询
sale_bill.goods.code与bill.goods.goods_code进行关联
这里，sale_bill下面的goods是一个对象结构，数据如:
{
"total_goods_num" : NumberInt(5)
"created_at" : "2021-06-23 16:46:27"
"bill_no" : "D20210623173"
"userID" : "15055170344"
"updated_at" : "2021-10-11 18:39:16"
"cust_name" : "20211011183741"
"link_mobile" : "18955132225"
"goods" : {
"code" : "100016079918"
"name" : "测试的商品"
}
"total_wait_num" : NumberInt(2428)
"trade_code" : "321313213"
"bill_status" : "1"
"company_name" : "***有限公司"
"total_money" : NumberDecimal("20696.96")
}
db.sale_bill.aggregate([
{
"$lookup": {
"from": "bill"
"let": {
"goodsCode": "$goods.code"
}
"pipeline": [{
"$match": {
"$expr": {
"$and": [
{"$in": ["$$goodsCode" "$goods.goods_code"]}
]
}
}
}]
"as": "sale_bill|bill"
}
}
{
"$unwind": {
"path": "$sale_bill|bill"
"preserveNullAndEmptyArrays": false
}
}
{
"$unwind": {
"path": "$sale_bill|bill.goods"
"preserveNullAndEmptyArrays": false
}
}
{
'$project': {
'bill_no': 1
'goods': 1
'userID': 1
'sale_bill|bill': 1
'isEqual': {
'$eq': ['$goods.code' '$sale_bill|bill.goods.goods_code']
}
}
}
{
'$match': {
'isEqual': true
}
}
])
