How to join data from two collections to find purchases?

Working with Data
, , ,
1

Hello everyone.
I have a collection of books.

[
    {
        "id": 1,
        "name": "Crime and punishment"
    },
    {
        "id": 2,
        "name": "Atomic habits"
    },
    {
        "id": 3,
        "name": "Demons"
    },
    {
        "id": 4,
        "name": "C# for beginners"
    }
]

And I have a shopping list(The id in books is the id of the book in books collection):

[
    {
        "id": 1,
        "userId": 75,
        "books": [
            {
                "id": 1,
                "price": 50
            },
            {
                "id": 2,
                "price": 20
            }
        ]
    },
    {
        "id": 2,
        "userId": 184,
        "books": [
            {
                "id": 3,
                "price": 10
            },
            {
                "id": 4,
                "price": 99
            }
        ]
    }
]

I want a field for each product to inform me if the product is purchased or not.
There I tried to get the shopData list and if length !== 0 => we purchased the book.

 {
          $lookup: {
            from: 'Purchases',
            let: { id: "$id", 
            pipeline: [
              { $match: 
        { $expr: {                     
                $and: [
                { $eq: ['$userId', user.id] },
                { $in: ['$books.id', '$id'] }
              ], } } },
            ],
            as: 'shopData',
          },
}

But i always get empty shopData.
Tried through $addFields, to immediately on the database side to calculate whether it is in the lists or not, and not pull the entire list, but also to no avail.
What i’am doing wrong ?

2

The lookup you share generates a syntax error.

SyntaxError: Unexpected token, expected "," (15:1)

  13 |             as: 'shopData',
  14 |           },

I think you are missing a closing brace for your let: object.

Are you sure that your collection of books have books with id:1?

Do you manage an id field that is different from the default _id field?

If not then you might want to try with

let : {  "id" : "$_id" }
1 Like
3

I think you are missing a closing brace for your let: object.

Misspelled.

Are you sure that your collection of books have books with id:1 ?

For ease of code exchange I made this data like this, in fact the id there is _id: ObjectId("68728313db115d9555e") and in shopping list book id is "id":ObjectId("68728313db115d9555e")

Do you manage an id field that is different from the default _id field?

No. In Shopping list -> books I think the id is custom, but for all others the id is like this : "_id": ObjectId("4354qweq1312489ewq")
Full query:

[
      {
        $sort: { _id: -1 },
      },
      {
        $lookup: {
          from: 'Purchases',
          let: { id: '$_id' },
          pipeline: [
            {
              $match: {
                $expr: {
                  $and: [{ $in: ['$$id', '$books.id'] }, { $eq: ['$userId', user.id] }],
                },
              },
            },
          ],
          as: 'shopData',
        },
      },
      {
        $project: {
          _id: 1,
          name: 1,
          shopData: 1,
        },
      },
      { $skip: take * (pageParam - 1) },
      { $limit: take },
    ] as any[];
4

Please share real non-redacted documents that should be processed with your real non-redacted pipeline.

Try $eq rather than $in.

Also share the code that uses the pipeline.