Foreign key in MongoDB

Question

Foreign key in MongoDB

Navigation

#1 by (2 votes)

4

I have the users collection and the shopping

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

What I intended was, when doing a findOne in the users table, it would automatically include purchases, the result would be something of the sort:

{
    "_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 this in MongoDB?

mongodb

asked by anonymous 04.10.2014 / 21:17

1 answer

Error installing / running WampServer 2.5 How to return concrete classes, with filled navigation properties, through Linq queries?

score 2 · Accepted Answer

This stretch of the answer is valid even before version 3.2. For version 3.2 onwards, see the rest of the answer.

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

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

This excerpt from the answer to version 3.2 onwards. Thanks to @PauloLuvisoto for Response Completion .

As of version 3.2 of MongoDB there is the $lookup operator, able to make 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 field of the 'products' collection.

We will perform the rendering as follows:

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

We'll get a result like this:

{ "_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 } ] }

You can use $lookup to join 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, not just through the _id fields as shown in the examples.