MongoDB Grows Up – the $lookup aggregator

mongodbartSo far, I’ve been avoiding anything having to do with NoSQL because of the inability to do, in the classical RDBMS world, table joins.  The idea of having to pull into memory (well, my application’s memory) all the results of one table and then join them (with more code) to another table was appalling to me, particularly since I want my queries to be runtime generated by metadata schema.

However, as of the release of MongoDB 3.2, that has changed!  I can now do multiple table (collection) joins, which in my opinion, is critical for working with semantic data.  As Semag points out:

“Data is organized based on binary models of objects, usually in groups of three parts: two objects and their relationship.”

So now, in MongoDB, I can do something very simple (and very non-semantic in this example):

db.createCollection("Person")
db.createCollection("Phone")
db.createCollection("PersonPhone")

db.Person.insert({ID: 1, LastName: "Clifton", FirstName: "Marc"})
db.Person.insert({ID: 2, LastName: "Wagers", FirstName: "Kelli"})

db.Phone.insert({ID: 1, Number: "518-555-1212"})
db.Phone.insert({ID: 2, Number: "518-123-4567"})

db.PersonPhone.insert({ID: 1, PersonID: 1, PhoneID: 1})
db.PersonPhone.insert({ID: 2, PersonID: 2, PhoneID: 1})
db.PersonPhone.insert({ID: 3, PersonID: 2, PhoneID: 2})

Notice I have to tables, Person and Phone (please ignore my case style, I come from a different world, some may say planet).  I can now query the data using the $lookup aggregate function (plus a few other pieces):

db.PersonPhone.aggregate([
{ $lookup: { from: "Person", localField: "PersonID", foreignField: "ID", as: "PersonName" } }, 
{ $lookup: { from: "Phone", localField: "PhoneID", foreignField: "ID", as: "PersonPhone" } }, 
{ $match: {PersonID: 2} }, 
{$project: {"PersonName.LastName":1, "PersonName.FirstName":1, "PersonPhone.Number": 1, _id:0}} ])

and I get a lovely resulting dataset:

{
  "PersonName": [
    {
      "LastName": "Wagers",
      "FirstName": "Kelli"
    }
  ],
  "PersonPhone": [
    {
      "Number": "518-555-1212"
    }
  ]
}{
  "PersonName": [
    {
      "LastName": "Wagers",
      "FirstName": "Kelli"
    }
  ],
  "PersonPhone": [
    {
      "Number": "518-123-4567"
    }
  ]
}

So now I can use finally MongoDB to satisfy the foundational tenet of a semantic database: a relationship between two objects.

And the nice thing about using a NoSQL database is that schema is not fixed in concrete, as it is with a SQL database, which would normally require additional layers of manipulation to deal with the second foundational tenet of a semantic database: that your concept of, say, a person’s name might be semantically different (but still compatible) with mine — especially when we consider culture.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s