Joins, or a way to pulling extra data from other namespaces about transporter

[nstott]

If we agree that merging the joined data into the existing document should be handled in a javascript vm in the source, then we're presented with a bit of a dilemma about how to request lookups
i.e. consider the case of redis, and mongo,
with redis we might want to query with
GET <key> or HGET <key> <value>
whereas with mongo, we'd want to do a db.findOne({_id: <id>}), or possibly a findOne(<bson query>)

We are forced to either provide a generic function that can take a variety of ways to query,
ie
Mongo

module.exports = function(doc, source) {
    doc["author"] = source.lookup({namespace: "boom.authors", query: {_id: doc.author_id}});
}

Redis

module.exports = function(doc, source) {
    doc["author"] = source.lookup({method: "HGET", key: "authors", value: doc.author_id});
}

or we provide specialized functions for each source type.
Redis

module.exports = function(doc, source) {
    doc["author"] = source.HGET("authors", doc.author_id);
}

Each of these options has drawbacks.

opinions?

[mrkurt]

I think you can probably get a long way with a stupid simple lookup interface right now, even just k/v lookups (find by _id in Mongo, get in Redis. Advanced queries (anything special on Redis probably counts) can come later.

And, the less actual work that happens in Javascript the more chance there is to optimize / scale this stuff later. Letting people do arbitrary queries and then run logic against them in JS seems like it's going to create a really hard-to-optimize performance bottleneck.

[andrewreedy]

👍

[tiengtinh]

👍 This is also one feature that I'm looking forward to

[shividhar]

+1 Definitely a sought after feature.

[allanlundhansen]

+1 denormalization of data for elasticsearch should be possible

[vinodtolexo]

+1 from me as well I can also contribute tothe code if required .

[jipperinbham]

dumping what the plan is here so I don't forget when I get to this soon...

t.Source(mongodb({uri: "connection string"…}).
  Join(postgres({uri: "connection string"…}), {
    id_map: {"account_id": "id"}, 
    field_map: {
      "name": "flegergle", 
      "slug": "account_slug"
   },
    query_ref: "accounts"}).
  Save(elasticsearch({uri: "connection string"...})
)

NOTE this is contingent on changes to the javascript DSL which is currently in progress.

the general idea here is to have a new method Join(...) that takes two parameters, an adaptor and a configuration for performing the query.

in the above pipeline, the following scenario would take place:

original doc

{
  "_id": "somespecial_ID",
  "name": "fancypants",
  "type": "foo",
  "account_id": 1567
}

and when sent to the Join the following query would be executed:

SELECT name AS flegergle, slug AS account_slug FROM accounts WHERE id = 1567

which would then send the following document down the pipeline:

{
  "_id": "somespecial_ID",
  "name": "fancypants",
  "type": "foo",
  "account_id": 1567,
  "flegergle": "Super Duper",
  "account_slug": "super-duper"
}

The initial implementation of this will likely only support joins to a single table/collection.