mongodb - 为 Mongodb 聚合中的每个数组元素匹配并添加新字段
问题描述
我在下面有两个集合,我正在使用聚合和合并创建一个视图表。
account table:
{
"_id": {
"$oid": "5f61b573db16185cebfeaaf9"
},
"status": "ACTIVE",
"personalInfo": {
"entityName": "John Doe",
...
},
"createdDateTime": {
"$date": "2020-09-16T06:49:23.103Z"
},
"modifiedDateTime": {
"$date": "2020-09-16T06:49:23.103Z"
}
}
account_transactions:
{
"_id": {
"$oid": "5f61b5b3db16185cebfeab0c"
},
"accountId": "5f61b573db16185cebfeaaf9",
"transactionType": "Payment",
"transactionStatus": "NEW",
"createdDate": {
"$date": "2020-09-16T06:50:27.305Z"
},
"createdBy": "sujal.shakya@joinsage.com",
"lastModifiedDate": {
"$date": "2020-09-16T10:23:06.617Z"
}
}
{
"_id": {
"$oid": "5f61b5b3db16185cebfeab0f"
},
"accountId": "5f61b573db16185cebfeaaf9",
"transactionType": "Credit",
"createdDate": {
"$date": "2020-09-16T06:50:27.305Z"
},
"createdBy": "sujal.shakya@joinsage.com",
"lastModifiedDate": {
"$date": "2020-09-16T10:23:06.617Z"
}
}
下面的 mongodb 查询创建一个视图表,每个帐户具有多个事务(如果存在),如果不存在事务,则将虚拟事务添加到一些虚拟字段。
db.account.aggregate(
[
{
$match: {
'status': 'ACTIVE'
}
},
{$addFields: {"accountId": {"$toString": "$_id"}}},
{
$lookup: {
from: "account_transactions",
localField: "accountId",
foreignField: "accountId",
as: "transactions"
}
},
{
"$project": {
"accountType": 1,
"transactions": {
"$cond": [
{ "$eq": [ "$transactions", [] ] },
{$concatArrays: [[{"lastModifiedDate": {$arrayElemAt: ["$account.modifiedDateTime",0]}, "transactionType" : "BLANK"}]] },//adds dummy fields
{"$filter": {
"input": "$transactions",
"as": "transactions",
"cond": {
"$or": [
{
"$eq": [
"$$transactions.transactionType",
"Payment"
]
},
{
"$eq": [
"$$transactions.transactionType",
"Sale"
]
},
{
"$eq": [
"$$transactions.transactionType",
"Debit"
]
}
]
}
}
}
},
"createdBy": 1
}
},
{$merge: {into: "account_view_table", on: "_id", whenMatched: "replace", whenNotMatched: "insert"}}
])
我现在需要做的是在上面创建的视图表中添加一个新字段,通过匹配事务类型为每个事务创建。例如:如果一个账户有多笔交易,每笔交易类型不同,则检查每笔交易的交易类型,并在每笔交易上添加新字段“ transactionTypeOrder ”,如:
我一直在尝试不同的事情,但没有任何效果
{$addFields: {"transactions.transactionTypeOrder" :
{
$cond: [{$in:["$transactions.transactionType",["Payment"]]},"1",
{$cond: [ {$in:["$transactions.transactionType",["Sale"]]},"2",
{$cond: [{$in:["$transactions.transactionType",["Debit"]]},"3", "4" ]}
]}
]
}
}},
因此,如果 transactionType == "Payment" 添加 transactionTypeOrder: 1,如果 transactionType == "Sales" 添加 transactionTypeOrder: 2 等等。我正在寻找的输出如下所示:
第二种情况的情况也检查另一个字段:在相同的交易中,我还必须对不同的字段进行另一次检查以检查“transactionStatus”字段是否存在,如果不存在,则将“transactionStatusOrderAsc”字段添加为“####”和“ transactionStatusOrderDsc”为“~~~”,如果存在与“transactionStatus”相同的值所以输出将如下:
所以基本上,我必须检查 transactionType 并添加新的 Filed,同时还要检查 transactionStatus 和两个需要字段。
//result view table
{
"_id": {
"$oid": "5f61b573db16185cebfeaafd"
},
//...other fields.
"transactions": [{
"transactionType": "Payment",
"transactionTypeOrder": 1,
"transactionStatus": "New",
"transactionStatusOrderAsc": "New",
"transactionStatusOrderDsc": "New"
},
{
"transactionType": "Sales",
"transactionTypeOrder": 2,
"transactionStatusOrderAsc": "####", //since transactionStatus field doesn't exist here
"transactionStatusOrderDsc": "~~~~"
},
{
"transactionType": "Debit",
"transactionTypeOrder": 3,
"transactionStatus": "Old",
"transactionStatusOrderAsc": "Old",
"transactionStatusOrderDsc": "Old"
}
]
}
解决方案
您可以使用$map应用于数组的每个元素,并使用$switch来检查值并添加您的字段。
db.account.aggregate([
{
$match: {
"status": "ACTIVE"
}
},
{
$addFields: {
"accountId": {
"$toString": "$_id"
}
}
},
{
$lookup: {
from: "account_transactions",
localField: "accountId",
foreignField: "accountId",
as: "transactions"
}
},
{
"$project": {
"accountType": 1,
"createdBy": 1,
transactions: /**adds dummy fields*/
{
"$map": {
"input": "$transactions",
"as": "transactions",
"in": {
$switch: {
branches: [
{
case: {
$eq: [
"$$transactions.transactionType",
"Payment"
]
},
then: {
transactionType: "$$transactions.transactionType",
transactionTypeOrder: 1
}
},
{
case: {
$eq: [
"$$transactions.transactionType",
"Sales"
]
},
then: {
transactionType: "$$transactions.transactionType",
transactionTypeOrder: 2
}
},
{
case: {
$eq: [
"$$transactions.transactionType",
"Debit"
]
},
then: {
transactionType: "$$transactions.transactionType",
transactionTypeOrder: 3
}
},
],
default: {
transactionType: "$$transactions.transactionType",
transactionTypeOrder: -1
}
}
}
}
}
}
},
])
如果您需要每笔交易的其他字段,则必须在每种情况下添加它们。你可以在这里测试
从第一条评论编辑
如果您必须包含所有交易字段,则可以使用$mergeObjects而不是手动创建对象。
db.account.aggregate([
{
$match: {
"status": "ACTIVE"
}
},
{
$addFields: {
"accountId": {
"$toString": "$_id"
}
}
},
{
$lookup: {
from: "account_transactions",
localField: "accountId",
foreignField: "accountId",
as: "transactions"
}
},
{
"$project": {
"accountType": 1,
"createdBy": 1,
transactions: /**adds dummy fields*/
{
"$map": {
"input": "$transactions",
"as": "transactions",
"in": {
$switch: {
branches: [
{
case: {
$eq: [
"$$transactions.transactionType",
"Payment"
]
},
then: {
$mergeObjects: [
"$$transactions",
{
transactionTypeOrder: 1
}
]
}
},
{
case: {
$eq: [
"$$transactions.transactionType",
"Sales"
]
},
then: {
$mergeObjects: [
"$$transactions",
{
transactionTypeOrder: 2
}
]
}
},
{
case: {
$eq: [
"$$transactions.transactionType",
"Debit"
]
},
then: {
$mergeObjects: [
"$$transactions",
{
transactionTypeOrder: 3
}
]
}
},
],
default: {
$mergeObjects: [
"$$transactions",
{
transactionTypeOrder: -1
}
]
}
}
}
}
}
}
},
])
推荐阅读
- zsh - 需要使用 zmv 递归地重命名多个文件
- android-studio - Android 虚拟设备在热重载和热重启后未检测到代码更改 - Flutter
- sql - 如何在 SQL 跳过周末重复模式?
- reactjs - DraftJS React-Hook-Form - 提交编辑器作为输入
- javascript - 单击确定时如何向 React Router 的提示添加功能?
- java - 在 JDK12 中创建 Generic Arraylist 的语法是什么?
- azure - 有没有办法向 Azure Monitor 中的图表添加阈值?
- php - PHP 日期时间格式
- git - 尽管提交量很小,git push 却永远占用了大量的代码库——为什么,这可以修复吗?
- php - $_FILES 不工作并给出行错误