我有以下收藏
实体:
const Entity = new Schema(
{
name: {
type: String,
required: true,
},
type: {
type: String,
required: true,
enum: ["owner", "customer", "supplier"],
},
字符串
账号:
const Account = new Schema(
{
name: {
type: String,
required: true,
},
phone: {
type: String,
default: "+964",
},
型
公关部:
const PR = new Schema(
{
number: {
type: Number,
default: 0,
},
customer: {
type: Schema.Types.ObjectId,
ref: "Entity",
},
owner: {
type: Schema.Types.ObjectId,
ref: "Entity",
},
account: {
type: Schema.Types.ObjectId,
ref: "Account",
},
items: [item.schema]
,
total_usd: {
type: Number,
default: 0,
},
total_iqd: {
type: Number,
default: 0,
},
型
发票:
const Invoice = new Schema(
{
number: {
type: Number,
index: true,
default: 0,
},
customer: {
type: Schema.Types.ObjectId,
ref: "Entity",
},
owner: {
type: Schema.Types.ObjectId,
ref: "Entity",
},
account: {
type: Schema.Types.ObjectId,
ref: "Account",
},
total_usd: {
type: Number,
default: 0,
},
total_iqd: {
type: Number,
default: 0,
},
型
我需要得到status and year and account
分组的PR
和Invoices
集合的total_usd
和total_iqd
的和
我正在使用下面的查询,它会产生非常奇怪的结果和数字
await entityModel
.aggregate([
{ $match: { _id: new mongoose.Types.ObjectId(req.query._id) } },
{
$lookup: {
from: "invoices",
localField: "_id",
foreignField: "customer",
as: "invoices",
},
},
{
$unwind: "$invoices",
},
{
$lookup: {
from: "accounts",
localField: "invoices.account",
foreignField: "_id",
pipeline: [{ $project: { _id: 0, name: 1 } }],
as: "invoices.account",
},
},
{ $unwind: "$invoices.account" },
{
$lookup: {
from: "prs",
localField: "_id",
foreignField: "customer",
as: "prs",
},
},
{
$unwind: "$prs",
},
{
$lookup: {
from: "accounts",
localField: "prs.account",
foreignField: "_id",
pipeline: [{ $project: { _id: 0, name: 1 } }],
as: "prs.account",
},
},
{ $unwind: "$prs.account" },
{
$group: {
_id: {
inv_status: "$invoices.status",
inv_account: "$invoices.account",
inv_year: { $year: "$invoices.created_at" },
pr_status: "$prs.status",
pr_account: "$prs.account",
pr_year: { $year: "$prs.created_at" },
},
inv_sum_usd: { $sum: "$invoices.total_usd" },
inv_sum_iqd: { $sum: "$invoices.total_iqd" },
pr_sum_usd: { $sum: "$prs.total_usd" },
pr_sum_iqd: { $sum: "$prs.total_iqd" },
},
},
])
.then((result) => {
res.send(result);
})
型
结果如下:
[
{
"_id": {
"inv_status": "approved",
"inv_account": {
"name": "FIIQ"
},
"inv_year": 2023,
"pr_status": "approved",
"pr_account": {
"name": "FIIX"
},
"pr_year": 2023
},
"inv_sum_usd": 480000,
"inv_sum_iqd": 0,
"pr_sum_usd": 514440,
"pr_sum_iqd": 0
},
{
"_id": {
"inv_status": "approved",
"inv_account": {
"name": "FIIQ"
},
"inv_year": 2023,
"pr_status": "approved",
"pr_account": {
"name": "FIIQ"
},
"pr_year": 2023
},
"inv_sum_usd": 144000,
"inv_sum_iqd": 0,
"pr_sum_usd": 40700,
"pr_sum_iqd": 14256000
},
{
"_id": {
"inv_status": "approved",
"inv_account": {
"name": "FIIX"
},
"inv_year": 2023,
"pr_status": "approved",
"pr_account": {
"name": "FIIX"
},
"pr_year": 2023
},
"inv_sum_usd": 2551740,
"inv_sum_iqd": 0,
"pr_sum_usd": 2829420,
"pr_sum_iqd": 0
},
{
"_id": {
"inv_status": "approved",
"inv_account": {
"name": "FIIX"
},
"inv_year": 2023,
"pr_status": "approved",
"pr_account": {
"name": "FIIQ"
},
"pr_year": 2023
},
"inv_sum_usd": 765522,
"inv_sum_iqd": 0,
"pr_sum_usd": 223850,
"pr_sum_iqd": 78408000
}
]
型
这是不匹配我的数据?!!(总美元的FIIX = 257220)任何帮助将不胜感激
1条答案
按热度按时间ne5o7dgx1#
这个问题源于展开后的查找,它像carryout连接一样增加了文档的数量。
例如,假设我们有这个文档:
字符串
解压缩
a
后,我们得到型
然后展开
b
,我们得到型
你可以在任何解卷发生之前perform the lookups and zip the looked up fields来解决这个问题。
型
您可以通过将组阶段更改为以下方式更改the layout of the results:
型