Analog to SQL 'JOIN' for Javascript objects?

Question

What's a pragmatic analog to SQL 'JOIN' for tables represented as arrays of Javascript objects? Javascript Array.join and D3.js 'd3.merge` are not the same concept.

E.g. SELECT * FROM authors LEFT JOIN books ON authors.id = books.author_id?

First table:

var authors = 
[  { id: 1, name: 'adam'},
   { id: 2, name: 'bob'},
   { id: 3, name: 'charlie'}, ...
]

Second table:

var books = 
[  { author_id: 1, title: 'Coloring for beginners'}, 
   { author_id: 1, title: 'Advanced coloring'}, 
   { author_id: 2, title: '50 Hikes in New England'},
   { author_id: 2, title: '50 Hikes in Illinois'},
   { author_id: 3, title: 'String Theory for Dummies'}, ...
]

The tables are loaded from CSV using D3.js d3.csv(), so have D3.js already, open to other libs but generally prefer coding directly if not too far out of the way.

I see Native way to merge objects in Javascript which uses RethinkDB, which seems over the top for this, but this is the idea.

Answer 1

Basically, like this:

// first, build an easier lookup of author data:
var authormap = {};
authors.forEach(function(author) {authormap[author.id] = author;});

// now do the "join":
books.forEach(function(book) {
    book.author = authormap[book.author_id];
});

// now you can access:
alert(books[0].author.name);

Answer 2

I was looking for something like this as well, and solved it using a bit of functional programming. I've taken the liberty of adding a couple of objects to your initial arrays in order to deal with the "NULL" cases.

var books = [
    {author_id: 1, title: 'Coloring for beginners'},
    {author_id: 1, title: 'Advanced coloring'},
    {author_id: 2, title: '50 Hikes in New England'},
    {author_id: 2, title: '50 Hikes in Illinois'},
    {author_id: 3, title: 'String Theory for Dummies'},
    {author_id: 5, title: 'Map-Reduce for Fun and Profit'}    
];
var authors = [
    {id: 1, name: 'adam'},
    {id: 2, name: 'bob'},
    {id: 3, name: 'charlie'},
    {id: 4, name: 'diane'}
];

So now you have a book without an author and an author without a book. My solution looks like this:

var joined = books.map(function(e) {
    return Object.assign({}, e, authors.reduce(function(acc, val) {
        if (val.id == e.author_id) {
            return val
        } else {
            return acc
        }
    }, {}))
});

The map method goes through every element of books using e and returns an array whose elements are the merged object of e, with its corresponding object in the authors array. Object.assign({},a,b) takes care the merge without modifying the original objects.

The corresponding object to each e in books is found by applying a reduce method on the authors array. Starting out with the initial value of an empty object {} (this is the second argument of reduce - it also could have been a null author such as {id:'', name ''}) the reduce method goes through the elements of authors using val and returns the object that ends up in acc. When a match is found between the books author_id and the author's id the entire matched author object ends up in acc and eventually gets returned by authors.reduce(...).

n.b. - Using reduce isn't that efficient because there is no way to break out of reduce loop once the match is found, it will continue to the end of the array

Answer 3

You can do it with Alasql JavaScript SQL library:

var res = alasql('SELECT * FROM ? authors \
       LEFT JOIN ? books ON authors.id = books.author_id',[authors, books]);

Try this example with your data in jsFiddle.

Also you can load CSV data directly into SQL expression:

alasql('SELECT * FROM CSV("authors.csv", {headers:true}) authors \
            LEFT JOIN CSV("books.csv", {headers:true}) books \
            ON authors.id = books.author_id',[], function(res) {
      console.log(res);
 });