Foreign key on Mongodb

Question

I have the collection users and the collection shopping

The ratio is 1 user to N purchases where each purchase is a document.

What I intended was, when making a findOne in the users table, would automatically include the purchases, the result would be something like:

{
    "_id" : ObjectId("5430..."),
    "nome" : "Filipe",
    "email" : "[email protected]",
    "compras" : {
        {
            "_id" : ObjectId("5465..."),
            "data" : "23/05/2014",
            "valor" : "15.50"
        },
        {
            "_id" : ObjectId("895..."),
            "data" : "18/09/2014",
            "valor" : "78.25"
        },
    }
}

How do I do it in Mongodb?

Answer 1

This part of the answer is valid until before version 3.2. For version 3.2 onwards, see the rest of the answer.

Using only the findOne() or other methods of find, is not possible because Mongodb does not support joins, according to official documentation.

What you can do is use a framework of your programming language of choice to solve the references for you.

---

This excerpt from the reply for version 3.2 onwards. Thanks to @Pauloluvisoto for complementing the response.

As of version 3.2 of Mongodb there is the operator $lookup, capable of making a Join between two collections.

In the example below we have a collection called 'products', with the fields _id, descricao and valor. We also have another collection called 'orders' with the fields _id, nome_cliente, cidade and id_produto. This field id_produto of the 'requests' collection will be linked to the _id of the collection 'products'.

We will perform the stapling as follows:

db.pedidos.aggregate([
{
    $lookup:
    {
        from: "produtos",
        localField: "id_produto",
        foreignField: "_id",
        as: "desc_produto"
    }
}])

We’ll get a result like that:

{ "_id" : ObjectId("5685c8c74ad088fc05dcebe7"), "usuario" : "Carlos", "id_produto" : 1, "cidade" :
"Franca", "desc_produto" : [ { "_id" : 1, "titulo" : "Camisa", "valor" : 1500.3 } ] }
{ "_id" : ObjectId("5685c8d94ad088fc05dcebe8"), "usuario" : "Paulo H", "id_produto" : 2, "cidade" :
"Batatais", "desc_produto" : [ { "_id" : 2, "titulo" : "Saia", "valor" : 236.47 } ] }

One can use the $lookup to make Join between more than two tables. Example:

db.pedidos.aggregate([
{
    $lookup:
    {
        from: "produtos",
        localField: "id_produto",
        foreignField: "_id",
        as: "desc_produto"
    }
},
{
    $lookup:
    {
        from: "clientes",
        localField: "id_cliente",
        foreignField: "_id",
        as: "desc_cliente"
    }
}])

Mooring can be done through any field, and not only through the fields _id as shown in the examples.