28 - MongoDB

28 - MongoDB#

$lookup in MongoDB#

$lookup in MongoDB allows us to perform a similar actions as SQL’s join. Its syntax is formatted like:

{
    $lookup:
    {
        from:<collection to join>,
        localField:<field from the input documents>,
        foreignField:<field from the documents of the "from" collection>,
        as:<output array field>
    }
}

This is equivalent of the following SQL:

SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (
    SELECT *
    FROM <collection to join>
    WHERE <foreignField> = <collection.localField>
);

Example#

Consider this input collection and joined collection:

db.orders.insertMany([
    {"_id":1, "item":"almonds", "price":12, "quantity":2},
    {"_id":2, "item":"pecans", "price":20, "quantity":1},
    {"_id":3}
])

db.inventory.insertMany([
    {"_id":1, "sku":"almonds", "description":"product1", "instock":120},
    {"_id":2, "sku":"bread", "description":"product2", "instock":80},
    {"_id":3, "sku":"cashews", "description":"product3", "instock":60},
    {"_id":4, "sku":"pecans", "description":"product4", "instock":70},
    {"_id":5, "sku":null, "description":"Incomplete"},
    {"_id":6},
])

Now, we can use $lookup with aggregate() to perform something similar to a join:

db.orders.aggregate({
    {
        $lookup:
        {
            from:"inventory",
            localField:"item",
            foreignField:"sku",
            as:"inventory_docs"
        }
    }
})

This results in the following documents:

{"_id":1,
 "item":"almonds",
 "price":12,
 "quantity":2,
 "inventory_docs":[{"_id":1, "sku":"almonds", "description":"product1", "instock":120}]
}
{"_id":2,
 "item":"pecans",
 "price":20,
 "quantity":1,
 "inventory_docs":[{"_id": 4, "sku":"pecans", "description":"product4", "instock":70}]
}
{"_id":3,
 "inventory_docs":[{"_id":5, "sku":null, "description":"Incomplete"}, {"_id":6}]
}

Normalization and Denormalization#

Consider the WORKS_ON relation(table) in the Company database. How can we map this table to a collection in MongoDB?

Normalized WORKS_ON#

{_id: ObjectId("..."),
 ProjectId: "P1",
 WorkerId: "W1",
 Hours: 32.5}
{_id: ObjectId("..."),
 ProjectId: "P1",
 WorkerId: "W2",
 Hours: 20.0}

Denormalized WORKS_ON#

{
    _id: "P1",
    Pname: "ProductX",
    Plocation: "Bellaire",
    Workers: [
        {Ename: "John Smith",
         Hours: 32.5
        },
        {Ename: "Joyce English",
         Hours: 20.0
        }
    ]
}

Indexes#

  • Single Field Index - db.collection.createIndex({name:-1})

  • Compound Index - db.collection.createIndex({Lname:1, Fname:1})

  • Multikey Index - index on an array field

  • Hashed Index - db.collection.createIndex({_id: "hashed})

  • Text Index - one text index in each collection

  • Geospatial Index

Text Index and Search#

db.reviews.createIndex({comments:"text})

db.stores.createIndex({name: "text", description: "text"})

db.stores.find({$text:{$search:"\"coffee shop\""}})

Geospatial Index#

  • Legacy coordinate pairs: 2d index

  • GeoJSON Objects: 2dsphere index

  • location: {type: "Point", coordinates: [-73.856077, 40.848447]}

db.restaurants.createIndex({location: "2dsphere"})

db.restaurants.find({
    location: {
        $geoWithin: {
            $centerSphere: [[-73.93414657, 40.82302903], 5/3963.2]
        }
    }
})
Contents