"Full outer join" using JavaScript

Question

I don't mind using a library to solve this problem, especially if it simplifies the code.

I've got some data like this:

 [{source: 'b', foo: 'bar'},
   {source:'d', foo: 'baz'}]

And I've got another array like this:

['b', 'c', 'e']

I'd like to process these two arrays and get this as output:

 [{source: 'b', foo: 'bar'},
    {source: 'c', foo: 'someDefaultValue'}, 
    {source:'d', foo: 'baz'}, 
    {source: 'e', foo: 'someDefaultValue'}]

To elaborate, if data is in the first array, it should remain in the result. If data is in the second array, it should appear in the result with default values. I want the result to be sorted by source.

In SQL terms, I'd refer to this as a "Full Outer Join on the source column." I'm finding it difficult to write code in JavaScript that works this way. How would I get the result given the two inputs?

Code Maniac · Accepted Answer · 2019-08-09 18:34:59Z

1

You can do something like this

Create a Map with source as key,
Initialize final with arr1 values, if you don't need immutability you can directly push on arr1
Loop over arr2 and check if value is not available in mapper, push in final with default value
Sort based on source

let arr1 =  [{source: 'b', foo: 'bar'}, {source:'d', foo: 'baz'}]
let arr2 = ['b', 'c', 'e']
let final = [...arr1]
let mapper = new Map(arr1.map(v=>[v.source,v.source]))

arr2.forEach(val=>{
  if(!mapper.has(val)){
    final.push({source:val, foo:'default'})
  }
})

final.sort((a,b)=> a.source.localeCompare(b.source))

console.log(final)

edited Aug 9, 2019 at 18:34

answered Aug 9, 2019 at 18:23

Code Maniac

37.9k5 gold badges44 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nina Scholz · Accepted Answer · 2019-08-09 19:28:39Z

1

You could take two arrays of object and a key on which the join is taking place an collect all values in a map.

function fullOuterJoin(left, right, on) {
    const
        getV = o => o[on],
        getMap = (m, o) => m.set(getV(o), Object.assign(m.get(getV(o)) || {}, o));

    return Array
        .from(left.reduce(getMap, right.reduce(getMap, new Map)).values())
        .sort((a, b) => getV(a).localeCompare(getV(b)));
}

var left = [{ source: 'b', foo: 'bar' }, { source:'d', foo: 'baz' }],
    source = ['b', 'c', 'e'],
    right = source.map(source => ({ source, foo: 'someDefaultValue' })),
    result = fullOuterJoin(left, right, 'source');

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

edited Aug 9, 2019 at 19:28

answered Aug 9, 2019 at 18:43

Nina Scholz

388k26 gold badges367 silver badges417 bronze badges

2 Comments

Code Maniac Over a year ago

output sequence is not properly sorted

Nina Scholz Over a year ago

just add a sorting.

fiveobjects · Accepted Answer · 2019-08-09 18:24:18Z

0

I am assuming you want to combine the result into a new array instead of modifying inputs.

Index source values from array 1
Iterate array 2 and check whether the corresponding source exists. If yes skip it and if not add one.

Optionally, you may sort the result by source if you need to.

Here you go:

let data1 = [{ source: 'b', foo: 'bar' }, { source: 'd', foo: 'baz' }];
let data2 = ['b', 'c', 'e'];
let indices = [];
let data3 = [...data1];
data1.forEach(token => indices.push(token.source));
data2.forEach(token => {
    if (!indices.includes(token)) {
        data3.push({ source: token, foo: 'default' })
    }
});
console.log(data3);

answered Aug 9, 2019 at 18:24

fiveobjects

4,5871 gold badge22 silver badges18 bronze badges

Comments

Josh Rutherford · Accepted Answer · 2019-08-09 18:24:28Z

0

Not sure there's any super easy way of doing this without just writing it up yourself.

const data = [
    {source: 'b', foo: 'bar'},
    {source: 'd', foo: 'baz'}
];
const other = ['b', 'c', 'e'];
const defaultValue = 'someDefaultValue';

const joined = [...data];
other.forEach(source => {
    if (!data.some(item => item.source === source)) {
        joined.push({source, foo: defaultValue});
    }
});

console.log(joined);

answered Aug 9, 2019 at 18:24

Josh Rutherford

2,7231 gold badge19 silver badges20 bronze badges

1 Comment

Daniel Kaplan Over a year ago

The output is not in order, though I suppose I could figure that out on my own.

Dominik Matis · Accepted Answer · 2019-08-09 18:37:59Z

0

So say that arrays are called first and second in the order which you wrote.

then I think this is the solution

let result = [...first];
for(let letter of second) {
    let contains = false;
    for(let obj of first) {
        if(obj.source == letter) {
            contains = true;
            result = [...result, obj]
        }
    }
    if(!contains) result = [...result, {source: letter, bar: "defaultValue"}] 
} 
result = result.sort((a, b) => a.source < b.source ? -1 : (a.source == b.source ? 0 : 1));
result = Array.from(new Set(result))

edited Aug 9, 2019 at 18:37

answered Aug 9, 2019 at 18:31

Dominik Matis

2,15612 silver badges15 bronze badges

1 Comment

Dominik Matis Over a year ago

I just copy all objects from your first array into result, then I check for every letter in second array (your array of letters), if they are as value in some object from first array, if so, copy it into, if not, create new object with defaultValue in bar property, then sort result by source property and then remove duplicates

MrRobboto · Accepted Answer · 2019-08-09 19:34:18Z

0

I didn't want to go too complex, and didn't want to use newer syntax, but here's more of a helper function, more generic for people to use and expand on. Hopefully is straight forward enough. This only sorts properly for string values. It is immutable so doesn't change the original arrays.

var arrLeft = [
  {source: 'b', foo: 'bar'},
  {source:'d', foo: 'baz'}
];

var arrRight = ['b', 'c', 'e'];

var fullJoin = function(arr1, arr2, key, defaultVal) {
  // Create deep copy array of the left side to include all...
  var newArr = JSON.parse(JSON.stringify(arr1));
  var newEle;
  // Loop through right side to make sure to include all
  for (var i = 0; i < arr2.length; i++) {
    // If it doesn't exist in left side, add it with default value
    if (!newArr.find(function(p) { return p[key] === arr2[i]; })) {
      newEle = {};
      newEle[key] = arr2[i];
      newEle.foo = defaultVal;
      newArr.push(newEle);
    }
  }
  // Sort on key alphabetically
  return newArr.sort(function(a, b) {
    var sourceA = a[key].toLowerCase();
    var sourceB = b[key].toLowerCase();
    switch(true) {
      case sourceA > sourceB:
        return 1;
      case sourceA < sourceB:
        return -1;
      default:
        return 0;
    }
  });
};
// Call like so
console.log(fullJoin(arrLeft,arrRight,'source','somedefault'));

answered Aug 9, 2019 at 19:34

MrRobboto

7583 silver badges10 bronze badges

1 Comment

MrRobboto Over a year ago

There are more elegant ways of course but since this question is about mimicking sql I figured I'd offer a more procedural code snippet for folks who prefer code that many developers can read and understand easily.

Felipe Benevides · Accepted Answer · 2023-04-12 21:10:27Z

0

arr1 = [{source: 'b', foo: 'bar'},
 {source:'d',  foo: 'baz'}]

arr2 = ['b', 'c', 'e']

It's simple

arr2.map((x)=>{!arr1.map((x)=>x.source).includes(x)?arr1.push({source:x,foo:'someDefaultValue'}):null})

Result is arr1

edited Apr 12, 2023 at 21:10

answered Apr 12, 2023 at 21:05

Felipe Benevides

92 bronze badges

Collectives™ on Stack Overflow

"Full outer join" using JavaScript

7 Answers 7

Comments

2 Comments

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

2 Comments

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related