prisma: Aggregate Raw not working when trying to `$match` on `_id` (needs to be an `ObjectId` but it's not a valid input)

new aggregate raw support

@matthewmueller Do you guys know about any issue that the raw aggregation support is having? I’m trying to run a pipeline though it, which I know for a fact that is working because I wrote it and tested it at first on MongoDB Compass.

return this.prisma.user.aggregateRaw({
      pipeline: [
        {
          $match: {
            _id: id,
          },
        },
        {
          $project: {
            communityId: 1,
          },
        },
        {
          $lookup: {
            from: 'Community',
            localField: 'communityId',
            foreignField: '_id',
            as: 'community',
          },
        },
        {
          $project: {
            communityId: 0,
          },
        },
        {
          $unwind: {
            path: '$community',
          },
        },
        {
          $lookup: {
            from: 'CommunityNft',
            localField: 'community._id',
            foreignField: 'communityId',
            as: 'communityNft',
          },
        },
        {
          $unwind: {
            path: '$communityNft',
          },
        },
        {
          $lookup: {
            from: 'NftBenefit',
            localField: 'communityNft._id',
            foreignField: 'communityNftId',
            as: 'nftBenefits',
          },
        },
        {
          $lookup: {
            from: 'Voucher',
            localField: 'nftBenefits._id',
            foreignField: 'nftBenefitId',
            as: 'vouchers',
          },
        },
      ],
    });

This operation is returning an empty array.

_Originally posted by @pedrobrun in https://github.com/prisma/prisma/issues/6712#issuecomment-1227771299_

About this issue

  • Original URL
  • State: open
  • Created 2 years ago
  • Reactions: 2
  • Comments: 20 (5 by maintainers)

Most upvoted comments

@pedrobrun Try this: $match: { _id: { $oid: id } }

Hope it helps. 😃

Thanks @heteibako that’s working for me 😃

Unfortunately no news, I’m not part of the team that will work on this. I can only say it’s on their radar, so they will pick this up one day, but they have to prioritize with other issues.

Thanks @heteibako for the solution. You have saved me a great deal of my time.

+1 I’ve looked for solving for several hours. Thanks @heteibako

@Jolg42 ok, got it! Thanks for the update 😃 Prisma’s version was 4.2.1. Mongo’s locally installed version is 5.0.7, but the development db is hosted on Atlas. Tbh there’s no additional information. I had to migrate to Mongoose as I had some deadlines close and this was a core feature and I didn’t want to have to hit 4+ collections separately. Once this is sorted out I’ll probably move back to Prisma though.

Could you provide us with a sample of data or a way to insert it and then provide us with the expected output from running this query?