Counting the document and adding the resultant to another collection

Working with Data
1

Hi,
I hope you all are fine. I came with a problem that I want to calculate the documents of a customer on the bases of date filter(last 30 days from the postedDate field) and then if there are more or equal to 5 documents then add a new field in another collection with a true flag otherwise false ( datatype for this field should be boolean)

Here are some samples of documents for your ease.

Collection A

{
     "accountId" : "0310100000041704",
    "postedDate" : ISODate("2020-12-22T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}

{
     "accountId" : "0310100000041704",
    "postedDate" : ISODate("2020-12-21T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}

{
     "accountId" : "0310100000041704",
    "postedDate" : ISODate("2020-12-20T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}

{
     "accountId" : "0310100000041704",
    "postedDate" : ISODate("2020-12-19T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}

{
     "accountId" : "0310100000041704",
    "postedDate" : ISODate("2020-12-18T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}

{
     "accountId" : "0310100000041704",
    "postedDate" : ISODate("2020-12-17T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}

{
     "accountId" : "0310100000041705",
    "postedDate" : ISODate("2020-12-19T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}

{
     "accountId" : "0310100000041705",
    "postedDate" : ISODate("2020-12-11T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}

{
     "accountId" : "0310100000041705",
    "postedDate" : ISODate("2020-11-19T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}


{
     "accountId" : "0310100000041706",
    "postedDate" : ISODate("2020-06-19T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}


{
     "accountId" : "0310100000041706",
    "postedDate" : ISODate("2020-07-19T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}


{
     "accountId" : "0310100000041706",
    "postedDate" : ISODate("2020-08-19T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}


{
     "accountId" : "0310100000041706",
    "postedDate" : ISODate("2020-09-19T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}


{
     "accountId" : "0310100000041706",
    "postedDate" : ISODate("2020-10-19T00:00:00Z"),
    "reasonCodeSending" : null,
    "reference" : "4cb103in41103",
    "remarks" : null,
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}

Collection B

{
    "accountId" : "0310100000041704",
    "remarks" : "B",
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00"
}

{
    "accountId" : "0310100000041705",
    "remarks" : "M",
    "typeKey" : "C",
    "valueDate" : "2020-07-09T00:00:00.000+04:00"
}

{
    "accountId" : "0310100000041706",
    "remarks" : "K",
    "typeKey" : "C",
    "valueDate" : "2020-08-09T00:00:00.000+04:00"
}

Expected Output

{
    "accountId" : "0310100000041704",
    "remarks" : "B",
    "typeKey" : "C",
    "valueDate" : "2020-06-09T00:00:00.000+04:00",
	"target": "true"	
}

{
    "accountId" : "0310100000041705",
    "remarks" : "M",
    "typeKey" : "C",
    "valueDate" : "2020-07-09T00:00:00.000+04:00",
	"target": "false"

}

{
    "accountId" : "0310100000041706",
    "remarks" : "K",
    "typeKey" : "C",
    "valueDate" : "2020-08-09T00:00:00.000+04:00",
	"target": "false"
}
2

Hello

I think you need $merge

1)Aggregate the first Collection(group etc) so in pipeline to have something like

 { 
  "accountId" "0310100000041704"
  "target"    true/false
 }

2)$merge with collection B

  on: "accountId"   
  whenMatched: "merge"
  whenNotMatched: "discard"

*merge requires Collection B to have a unique index on acountId

3

But you have missed couples of thing, how can I get the count of the document of last 30 days( form postedDate field) and then on that bases we have to create a new field in next collection.

4

@Prasad_Saya @scott_molinari
Need your answer on this.
One more thing that can we do this in mongodb or not that doing some calculation on single document and then add the result(field) in the second collection on the bases of first collection calculation?

5

Hello

Yes i know i thought the main problem was the merge,maybe the bellow can solve all problems

1)Filter
You can use subtract to filter those dates
$subtrack works in dates also,if it takes 2 days returns the difference in milliseconds

$subtract

date_difference= {$subtract [now_date posted_date]) 

(if you use java now_date=new Timestamp(System/currentTimeMillis))  

You need

date_difference <= 86400000*30 //1 day = 86400000 millisec

2)Then group by accountId,sum the members
$addField target true if >5 else false

3)And then you can do the merge

6

That looks cool but I am writing mongodb query not a java query.
Steps are:

  • counts the number of documents on the bases of date filter (still IDK how to get last 30 days documents w.r.t to postedDate filter) // last 30 days documents from posted date
  • if the count is greater or equal to 5 then add a new field with true indicator in another collection
  • else add false indicator in another collection.
7

Hello ,

The driver will calculate the now_date ,and the 86400000*30 ,before sending the query.

{"$lte" {$subtract [now_date, "$postedDate"]} 86400000*30}

Also $addField target true if >5 else false,you will use $cond ,not driver if

To be sure that all work i have to run the query , but try it i think it will work

8

Hello

I wrote the query hopefully does what you need

CollA(your data i kept only accountid and postedDate)

Screenshot from 2020-12-22 17-06-01
Screenshot from 2020-12-22 17-06-011671×971 84.8 KB

CollB (before running the query)

Screenshot from 2020-12-22 17-06-12
Screenshot from 2020-12-22 17-06-121674×553 52.4 KB

CollB(after running the query)

Screenshot from 2020-12-22 17-06-59
Screenshot from 2020-12-22 17-06-591672×591 55.4 KB

The query
2592000000 = 86400000 * 30
“2020-12-22T15:09:11Z” = its now_date ,dont use string,use your driver method to take the
Something like date(now)
One more thing,for this to work, CollB needs a unique index on accountId (merge needs it)

{
  "aggregate": "testcollA",
  "pipeline": [
    {
      "$group": {
        "_id": "$accountId",
        "sum": {
          "$sum": {
            "$cond": [
              {
                "$lte": [
                  {
                    "$subtract": [
                      "2020-12-22T15:09:11Z",
                      "$postedDate"
                    ]
                  },
                  2592000000
                ]
              },
              1,
              0
            ]
          }
        }
      }
    },
    {
      "$addFields": {
        "accountId": "$_id"
      }
    },
    {
      "$project": {
        "_id": 0
      }
    },
    {
      "$addFields": {
        "target": {
          "$gte": [
            "$sum",
            5
          ]
        }
      }
    },
    {
      "$project": {
        "_id": 0,
        "accountId": 1,
        "target": 1
      }
    },
    {
      "$merge": {
        "into": {
          "db": "testdb",
          "coll": "testcollB"
        },
        "on": [
          "accountId"
        ],
        "whenMatched": "merge",
        "whenNotMatched": "discard"
      }
    }
  ],
  "cursor": {},
  "maxTimeMS": 1200000
}
9

“$subtract”: [
“2020-12-22T15:09:11Z”,
“$postedDate”
]

But the requirement was that we have to count the previous 30 days documents starting from postedDate not from the current date.

10

30days = date1 - date2

In my query
30days = current_date - $postedDate (keep it if $postedDate is in the last month)

Which is the the date1 and date2 you need?

11

Previous 30 days document from postedDate

12

The before was
30days = current_date- $postedDate
Now it is
30days = $postedDate - $userOldestPostedDate
Gives

Screenshot from 2020-12-22 19-03-24
Screenshot from 2020-12-22 19-03-241674×794 57.4 KB

{
  "aggregate": "testcollA",
  "pipeline": [
    {
      "$lookup": {
        "from": "testcollA",
        "let": {
          "acid": "$accountId",
          "d": "$postedDate"
        },
        "pipeline": [
          {
            "$match": {
              "$expr": {
                "$eq": [
                  "$accountId",
                  "$$acid"
                ]
              }
            }
          },
          {
            "$group": {
              "_id": "$accountId",
              "userOldestPostedDate": {
                "$min": "$postedDate"
              }
            }
          },
          {
            "$addFields": {
              "accountId": "$_id"
            }
          },
          {
            "$project": {
              "_id": 0
            }
          },
          {
            "$project": {
              "userOldestPostedDate": 1
            }
          }
        ],
        "as": "joined"
      }
    },
    {
      "$unwind": {
        "path": "$joined"
      }
    },
    {
      "$replaceRoot": {
        "newRoot": {
          "$mergeObjects": [
            "$joined",
            "$$ROOT"
          ]
        }
      }
    },
    {
      "$unset": [
        "joined"
      ]
    },
    {
      "$group": {
        "_id": "$accountId",
        "sum": {
          "$sum": {
            "$cond": [
              {
                "$lte": [
                  {
                    "$subtract": [
                      "$postedDate",
                      "$userOldestPostedDate"
                    ]
                  },
                  2592000000
                ]
              },
              1,
              0
            ]
          }
        }
      }
    },
    {
      "$addFields": {
        "accountId": "$_id"
      }
    },
    {
      "$project": {
        "_id": 0
      }
    },
    {
      "$addFields": {
        "target": {
          "$gte": [
            "$sum",
            5
          ]
        }
      }
    },
    {
      "$project": {
        "_id": 0,
        "accountId": 1,
        "target": 1
      }
    },
    {
      "$merge": {
        "into": {
          "db": "testdb",
          "coll": "testcollB"
        },
        "on": [
          "accountId"
        ],
        "whenMatched": "merge",
        "whenNotMatched": "discard"
      }
    }
  ],
  "cursor": {},
  "maxTimeMS": 1200000
}