Get nested subdocuments in join using $lookup

Working with Data
, , ,
1

Hello Everyone !
I want to join two collections products and transactions using $lookup
The structures of the two collections are :

products collection

transactions collection
transactions collection436×553 7.22 KB

I want to list all transactions that have status “sold” for every product
First, I want just to have all transactions of each product

I tried to do this :
$lookup

But did’t work ?!

The goal is return all documents that contain the product id in status of transactions.

I try using this with “status.$id” but don’t work.

I want to know if there is another way to query nested documents instead of dot notation ?

Also How to join two collections in a specific condition (like in SQL) not just with localField and foreighField ?

Thanks !

2

Hi @ayoub_sadiki and welcome in the MongoDB Community :muscle: !

Using a value as a key isn’t a good idea because this leads to this kind of problems and it creates an instable schema that makes querying a lot more complex.

status.$id doesn’t exists in your doc. and the $ shouldn’t be here anyway.

To fix this, you will have to use $objectToArray on the status object. So your product id now become a value that you can use in the pipeline (and not a key!).

Then, you will be able to use $lookup.

To answer your final question:

$lookup has 2 forms. The one with a sub pipeline is the one you are after.

Cheers,
Maxime.