# SQL-style join on arrays using ECMA6

I have a code snippet which performs an SQL-style join on objects in two arrays based on a specific field (in the code below, .id).

let preprocessed_array = [
{id: 1,category:'a'},
{id: 2,category:'b'},
{id: 3,category:'b'}
]

let new_array = [
{id: 4},
{id: 1,category:'b'},
{id: 5}
]

for(let i=0;i< preprocessed_array.length;i++)
{
for(let j=0;j<new_array.length;j++)
{
if( preprocessed_array[i].id===new_array[j].id)
{
new_array[j]=preprocessed_array[i]
}
}
}

console.log(new_array)

Although this snippet is straightforward, I don't like the imperative style. Is there a more declarative way to do this just with javascript / ECMA6 arrays?

• Wouldn't a Join mean that when the id is already found, you get the all the categories for the shared id? Why is for example the item with id one only assigned with category a at the end? – Icepickle Sep 14 '17 at 7:07
• Indeed, a joing would not be enough to get to the results I want. But it would be the core to a more declarative solution. – user1934212 Sep 14 '17 at 7:24

Build an index and use it to map the second array:

const index = new Map(source.map(el => [el.id, el]));
const result = picks.map(el => (index.get(el.id) || el));


The larger the arrays, the more effective the index becomes.
If this code is a bottleneck, it can be rewritten to build the index on-the-go (memoization).

P.S. source stands for preprocessed_array and picks for new_array in the original code.

• this answer is much better than mine +1 – I wrestled a bear once. Sep 15 '17 at 19:08

You can use map and find to do the same thing "declaratively", but in my opinion that's just a stupid buzzword and yours is more readable..

let preprocessed_array = [
{id: 1,category:'a'},
{id: 2,category:'b'},
{id: 3,category:'b'}
]

let new_array = [
{id: 4},
{id: 1,category:'b'},
{id: 5}
]

new_array = new_array.map(function(a) {
var match = preprocessed_array.find(function(b) {
return b.id === a.id;
});
return match === undefined ? a : match;
});

console.log(new_array)

• You are right. Your solution is slightly more declarative, but at the same time considerably less readable. I was looking for a fully declarative style, as in sql. Now I realize, that the array objects don't provide that. So are there libraries comparable to python pandas to achieve what I want? – user1934212 Sep 14 '17 at 7:22
• @user1934212 - there are indeed. jSQL is an SQL implementation written in js by yours truly, but doesn't yet support joins, alaSQL is a more fully featured one. linq is basically the same thing without actaully being an SQL implementation.. – I wrestled a bear once. Sep 14 '17 at 7:27
• Since I have a strong .NET background, I will look into the linq library first. Thanks for your help! – user1934212 Sep 14 '17 at 7:37
• I personally prefer the map/find variation, but agree that the benefit is somewhat lost with your current version. I would prefer function join(a, b) { return a.map(item => b.find(({id}) => id === item.id) || item); } – Gerrit0 Sep 14 '17 at 15:05
• @Gerrit0 I did that initially but the problem with that is if find returns a zero or null from the preprocessed array it will erroneously return the item from the other array. It should really check for undefined explicitly. – I wrestled a bear once. Sep 14 '17 at 15:11