mongodbaggregateprismanestdate-fns

the created_at search in prisma aggregateRow mongodb does not work


I use the date-fns library to work with dates. the code provided their methods.

prisma: 5.22.0

@prisma/client: 5.22.0

async getAllOnYear() {
    const date = new Date()
    const startYear = startOfYear(date)
    const endYear = endOfYear(date)

    console.log('Start Year:', startYear)
    console.log('End Year:', endYear)

    const monthlyStatisticsRaw = await this.prisma.productRelease.aggregateRaw({
        pipeline: [
            {
            $match: {
                marking: { $ne: EnumProductReleaseMarking.deleted },
                created_at: {
                    $gte: startYear,
                    $lte: endYear
                }
            }
        },
        {
            $group: {
                _id: {
                    month: { $month: '$created_at' }
                },
                totalAmount: { $sum: '$total_amount' },
                totalSale: { $sum: '$total_sale' },
                totalSwap: { $sum: '$total_swap' },
                totalBonus: { $sum: '$total_bonus' },
                count: { $sum: 1 }
            }
            }
        ]
    })

    console.log('raw', monthlyStatisticsRaw)
}

console:

Start Year: 2023-12-31T19:00:00.000Z 
End Year: 2024-12-31T18:59:59.999Z 
raw []

prisma model:

model ProductRelease {
    id        String   @id @default(auto()) @map("_id") @db.ObjectId
    createdAt DateTime @default(now()) @map("created_at")
    updatedAt DateTime @updatedAt @map("updated_at")

    user          User?   @relation(fields: [userId], references: [id])
    userId        String? @map("user_id") @db.ObjectId
    
    tag     String                    @unique
    status  EnumProductReleaseStatus
    marking EnumProductReleaseMarking @default(null)
    
    totalAmount Float @map("total_amount")
    totalSale   Int   @map("total_sale")
    totalSwap   Int   @map("total_swap")
    totalBonus  Int   @map("total_bonus")
    
    @@map("product_release")

}

I tried different ways to convert the date to toISOString() and so on. I tried changing created_at to createdAt, but with no result.

if you delete created_at, everything works out:

Start Year: 2023-12-31T19:00:00.000Z
End Year: 2024-12-31T18:59:59.999Z
raw [
     {
        _id: { month: 11 },
        totalAmount: 345664,
        totalSale: 336,
        totalSwap: 50,
        totalBonus: 106,
        count: 10
     }
]

to complete the picture, I also throw off the data on request:

await this.prisma.productRelease.aggregateRaw({
    pipeline: [
        {
            $project: { created_at: 1, marking: true }
        },
        {
            $sort: { created_at: 1 }
        }
    ]
})

result:

 {
        _id: { '$oid': '673c955783744a1eaf4e52ee' },
        created_at: { '$date': '2024-11-19T13:40:38.298Z' },
        marking: 'accounting'
 },
 {
        _id: { '$oid': '673e5083a9570d92a284c2b7' },
        created_at: { '$date': '2024-11-20T21:11:30.742Z' },
        marking: 'null'
 },
 {
       _id: { '$oid': '673f9542f9388c92967b84a8' },
        created_at: { '$date': '2024-11-21T20:17:05.329Z' },
        marking: 'null'
 },

Solution

  • I was able to first output the working method to $expr and then found the method for the usual $match:

    await this.prisma.productRelease.aggregateRaw({
        pipeline: [
            {
                $match: {
                    marking: { $ne: EnumProductReleaseMarking.deleted },
                    created_at: {
                        $gte: { $date: startYear },
                        $lte: { $date: endYear }
                    }
                }
            },
            {
                $group: {
                    _id: {
                        month: { $month: '$created_at' }
                    },
                    totalAmount: { $sum: '$total_amount' },
                    totalSale: { $sum: '$total_sale' },
                    totalSwap: { $sum: '$total_swap' },
                    totalBonus: { $sum: '$total_bonus' },
                    count: { $sum: 1 }
                }
            }
        ]
    })