Mongodb unmatch date aggregate lookup

Working with Data
, ,
1

I am trying to make attendance system and have multiple collection like attendances, shifts, leaves, Holidays. In the shifts collection have Week Off but obviously absents are not in record. I have created
a Playground.
My attendance collections as follows.

[
    {
      "_id": ObjectId("6249c77a99e5c26e50736c02"),
      "employee": ObjectId("622061b73b2eaac4b15d42e4"),
      "createdAt": "2022-04-03T16:12:42.328Z"
    },
    {
      "_id": ObjectId("624a700199e5c26e50736c07"),
      "employee": ObjectId("622061b73b2eaac4b15d42e4"),
      "createdAt": "2022-04-06T04:11:45.891Z"
    }
]

And my shifts collection looks like bellow.


    {
      "_id": ObjectId("6272e84b6fc62f16bd0f337d"),
      "month": "2022-04",
      "employee": ObjectId("622061b73b2eaac4b15d42e4"),
      "shift": [
        {
          "_id": ObjectId("6272e84b6fc62f16bd0f337c"),
          "date": "2022-04-03",
          "name": "Day"
        },
        {
          "_id": ObjectId("6272e84c6fc62f16bd0f337e"),
          "date": "2022-04-04",
          "name": "Week Off"
        },
        {
          "_id": ObjectId("6272e8546fc62f16bd0f337f"),
          "date": "2022-04-5",
          "name": "Night"
        }
      ]
    }
]

Here I need to get Week Off that have marked with employee and date but not related to attendance collections and as well as need employees wise Absents date in this same result which have no record and related to any collection.
I need something like this.

[
    {
        "_id": ObjectId("6249c77a99e5c26e50736c02"),
        "createdAt": "2022-04-03T16:12:42.328Z",
        "employee": ObjectId("622061b73b2eaac4b15d42e4"),
        "shift": {
            "_id": ObjectId("6272e84b6fc62f16bd0f337c"),
            "date": "2022-04-03",
            "name": "Day"
        }
    },
    {
        "_id": ObjectId("543761b43b2eaac4b25d42e8") //Not Required,,
        "createdAt": "2022-04-04T00:00:00.000Z",
        "employee": ObjectId("622061b73b2eaac4b15d42e4"),
        "shift": {
            "_id": ObjectId("6272e84c6fc62f16bd0f337e"),
            "date": "2022-04-04",
            "name": "Week Off"
        }
    },
    {
        "_id": ObjectId("668761b43b2eaac4b25d42e5") //Not Required,
        "createdAt": "2022-04-05T00:00:00.000Z",
        "employee": ObjectId("622061b73b2eaac4b15d42e4"),
        "absent": true
    },
    {
        "_id": ObjectId("624a700199e5c26e50736c07"),
        "createdAt": "2022-04-06T04:11:45.891Z",
        "employee": ObjectId("622061b73b2eaac4b15d42e4")
    }
]

Please help out.

2

I got the inspiration from Stackoverflow Post
But unable to implement in my scenario.

3

I did not have time to experiment yet but here is a recommendation I can make from seeing your sample documents.

Do not use string data type for dates such as:

Use date data type such as:

Dates as date are safer to use

Consider the following error from your sample documents:

versus what you get with the safer Date.

> d = new Date( "2022-04-5" )
>2022-04-05T04:00:00.000Z

Dates as date are more space efficient

Giving 2 collections:

> strings.find()
{ _id: 0, d: '2022-05-26' }
{ _id: 1, d: '2022-05-27' }
{ _id: 2, d: '2022-05-28' }
> dates.find()
{ _id: 0, d: 2022-05-26T00:00:00.000Z }
{ _id: 1, d: 2022-05-27T00:00:00.000Z }
{ _id: 2, d: 2022-05-28T00:00:00.000Z }

Using collections.stats() we get:

> strings.stats().avgObjSize
32
> dates.stats().avgObjSize
25

Not a big deal, but multiply by millions of documents. And that size difference is also compounded in any indexes that include your dates. And compounded in all data transfers during queries, results and replication.

Dates as date are faster during comparisons

When comparing 2 dates as date, it is 1 low level comparisons. When comparing 2 dates as string, it 1 low level comparisons for each character. When comparing 2 strings such as 2022-05-26 and 2022-05-27 you only detect difference, hence order, after the 10th character comparison.

Cannot compare strings and dates without converting

You already have some dates as date data type (field createdAt) so you will not be able to compare the 2 together without converting one to the other type. Indexes on the converted value cannot be used.

4

Thank you for your suggestion. @steevej . I will remember your suggestion.

Please suggest me if possible to achieve my requirement accordingly to my data.

1 Like
5

I’d love to help but I don’t understand what you are trying to do. You give an example of what you want to get back but not what it means. Do you want to get back a record for each employee? Each day that they worked? Something else?

7

Yes I want to get back the records for each employee and each day that warked and not worked. @Asya_Kamsky