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

[pedrobrun]

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

[danstarns]

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?

[pedrobrun]

Could you provide us with a sample of data

@danstarns sure! here's some mock data: aggregation_sample_data.zip

[pedrobrun]

@Jolg42 @danstarns Were you able to reproduce it?

[pedrobrun]

@danstarns btw I forgot to send you the expected result, sorry. But basically it is just meant to return everything I looked up in the query. An array of objects that relate through the foreign and local fields of the $lookup queries.

[Jolg42]

@pedrobrun No we didn't check this yet, I just changed the label to reflect that is now actionable (= there is something to try a reproduction). We have a lot of issues to look at, so any additional information is welcome, like your:

• MongoDB version
• Prisma version from prisma -v
• Anything that you think could be relevant

[pedrobrun]

@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.

[Jolg42]

I imported the data with https://github.com/prisma/prisma/files/9435702/aggregation_sample_data.zip

mongoimport --host localhost:27018 --db 15013 --collection community --file community.json --jsonArray
mongoimport --host localhost:27018 --db 15013 --collection communitynft --file communitynft.json --jsonArray
mongoimport --host localhost:27018 --db 15013 --collection nftbenefit --file nftbenefit.json --jsonArray
// note I had to fix the json first and put [] around the object
mongoimport --host localhost:27018 --db 15013 --collection user --file user.json --jsonArray
mongoimport --host localhost:27018 --db 15013 --collection voucher --file voucher.json --jsonArray

[Jolg42]

Can reproduce in our internal dev version 4.4.0-dev.30

Here is the script to reproduce

import { PrismaClient } from "@prisma/client";
import { ObjectId } from "bson";
import { MongoClient } from "mongodb";

async function main() {
  const prisma = new PrismaClient();

  const client = await MongoClient.connect(
    "mongodb://root:prisma@localhost:27018/15013?authSource=admin"
  );
  const coll = client.db("15013").collection("user");
  const cursor = coll.aggregate([
    {
      $match: {
        // as string doesn't work
        // _id: "63053c88b472e7492f517dcd",
        // but works as ObjectID!
        _id: new ObjectId("63053c88b472e7492f517dcd"),
      },
    },
  ]);
  const resultFromMongoDriver = await cursor.toArray();
  console.log({ resultFromMongoDriver });
  await client.close();

  const usersFromFindMany = await prisma.user.findMany();
  console.log({ usersFromFindMany });

  const rawResult1 = await prisma.user.aggregateRaw({
    pipeline: [
      // @ts-ignore
      {
        $match: {
          // as string doesn't work (needs to be ObjectID)
          // _id: "63053c88b472e7492f517dcd",
          //
          // But ObjectID throws a TypeError... (Note: ignoring it with  @ts-ignore doesn't help)
          //
          // Type '{ $match: { _id: ObjectId; }; }' is not assignable to type 'InputJsonValue'.
          // Types of property '$match' are incompatible.
          //   Type '{ _id: ObjectId; }' is not assignable to type 'InputJsonValue | null | undefined'.
          //     Types of property '_id' are incompatible.
          //       Type 'ObjectId' is not assignable to type 'InputJsonValue | null | undefined'.
          //         Type 'ObjectId' is not assignable to type 'InputJsonObject'.
          //           Index signature for type 'string' is missing in type 'ObjectId'.
          _id: new ObjectId("63053c88b472e7492f517dcd"),
        },
      },
    ],
  });
  console.log({ rawResult1 });

  prisma.$disconnect();
}

main();

output

ts-node main.ts
{
  resultFromMongoDriver: [
    {
      _id: new ObjectId("63053c88b472e7492f517dcd"),
      email: '[email protected]',
      password: '$2b$12$wuytZTYUGeyF39DqPsRLu.mZAOT/iK2T8uz5tUMZpH4PQWRqtyajC',
      name: 'admin',
      address: 'string',
      role: 'ADMIN',
      status: 'ACTIVE',
      communityId: new ObjectId("6305304e27e9634fbaac6699"),
      updatedAt: 2022-08-23T20:45:59.828Z,
      createdAt: 2022-08-23T20:45:59.826Z
    }
  ]
}
{
  usersFromFindMany: [
    {
      id: '63053c88b472e7492f517dcd',
      address: 'string',
      communityId: '6305304e27e9634fbaac6699',
      createdAt: 2022-08-23T20:45:59.826Z,
      email: '[email protected]',
      name: 'admin',
      password: '$2b$12$wuytZTYUGeyF39DqPsRLu.mZAOT/iK2T8uz5tUMZpH4PQWRqtyajC',
      role: 'ADMIN',
      status: 'ACTIVE',
      updatedAt: 2022-08-23T20:45:59.828Z
    }
  ]
}
{ rawResult1: [] }

[viktornord]

Any progress on this issue ? Please fix this...

[Boromir1994]

I have same issue

[ran-konneto]

@Jolg42 any updates on this?

[Jolg42]

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.

[srinivasdareddy]

having same issue. Any update on this?

Anyone has any workaround?

[pedrobrun]

@srinivasdareddy Unfortunately I'm not very hopeful that this'll be fixed in the very near future. There are currently 2.3k issues open. What I did was migrating to mongoose

[Vaasu-Dhand]

bump

[heteibako]

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

Hope it helps. :)

[QasimRRizvi]

Thanks @heteibako that's working for me :)

[Bricks666]

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

[theguywhocodes96]

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

[Evilscaught]

This may be relevant for those wondering where the $oid came from https://www.prisma.io/docs/concepts/components/prisma-schema/relations#mongodb (subject matter pasted below if the link ever stops working)

MongoDB

For MongoDB, Prisma currently uses a normalized data model design, which means that documents reference each other by ID in a similar way to relational databases.

The following document represents a User (in the User collection):

{ "_id": { "$oid": "60d5922d00581b8f0062e3a8" }, "name": "Ella" }

The following list of Post documents (in the Post collection) each have a authorId field which reference the same user:

[
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3a9" },
    "title": "How to make sushi",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  },
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3aa" },
    "title": "How to re-install Windows",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  }
]

This data structure represents a one-to-many relation because multiple Post documents refer to the same User document.

[heteibako]

@heteibako , @Evilscaught , @Bricks666 , @QasimRRizvi , @theguywhocodes96 , what about, if i want to use $in, not single id

{ '_id': { $oid: '669f6d9148b7b4ef0faf1412' } } its okay

{ '_id': { $in: [{ $oid: '669f6d9148b7b4ef0faf1412' }, { $oid: '669f6d9148b7b4ef0faf1413' }] } } its not okay, not getting needed data

Could anybody help for this case ?

This?

 $in: ids?.map((id: string) => ({  $oid: id  }))

$match: { 'your-key-here': { $in: ids?.map((id: string) => ({ $oid: id })) } }

[Evilscaught]

@heteibako For querying with multiple ids, I've had to wrap my ids in an ObjectId.

import { ObjectId } from 'mongodb';

const match = { $match: { 'your-key-here': { $in: (ids || []).map( (id: string) => ({ $oid: ObjectId(id) }) ) } } }

// Do something with match

* for future reference, this example is using "prisma": "4", "mongodb": "^4.9.0" and node version 16.15.0

[heteibako]

@Evilscaught Interesting. The code above is from a project that I was working on, and I had not needed to wrap the id in ObjectId. But good to know that it works. My Prisma version is: 5.16.0