Javascript: Full Outer Join on a Array of Objects, using one key

Question

I have two arrays of objects which I want to "Full Outer Join", like in SQL:

Dataset A:

[ { id: 1, name: "apple", color: "red" }, {id: 2, name: "banana", color: "yellow"} ]

Dataset B:

[ { id: 1, name: "apple", color: "blue" }, {id: 3, name: "mango", color: "green"} ]

Intended result:

[ { id: 1, dataset_a: { id: 1, name: "apple", color: "red" }
         , dataset_b: { id: 1, name: "apple", color: "blue" }
  }
, { id: 2, dataset_a: { id: 2, name: "banana", color: "yellow"}
         , dataset_b: null
  }
, { id: 3, dataset_a: null
         , dataset_b: { id: 3, name: "mango", color: "green"}
  }
]

The id's are unique.
Lodash may be used.
I have no restriction on ES version.

Instead of null, an empty object would be OK too. The id's don't necessarily need to be repeated, as shown below. So, this would be just as good:

[ { id: 1, dataset_a: { name: "apple", color: "red" }
         , dataset_b: { name: "apple", color: "blue" }
  }
, { id: 2, dataset_a: { name: "banana", color: "yellow"}
         , dataset_b: {}
  }
, { id: 3, dataset_a: {}
         , dataset_b: { name: "mango", color: "green"}
  }
]

Nina Scholz solution, transformed into a a function:

fullOuterJoin(dataset_a_name, dataset_b_name, dataset_a, dataset_b, key) {
    const getNullProperties = keys => Object.fromEntries(keys.map(k => [k, null]));
    var data = { [dataset_a_name]:dataset_a, [dataset_b_name]:dataset_b },
        result = Object
            .entries(data)
            .reduce((r, [table, rows]) => {
                //forEach dynamic destructuring
                rows.forEach(({ [key]:id, ...row }) => {
                    if (!r[id]) r.items.push(r[id] = { [key]:id, ...getNullProperties(r.tables) });
                    r[id][table] = row;
                });
                r.tables.push(table);
                r.items.forEach(item => r.tables.forEach(t => item[t] = item[t] || null));
                return r;
            }, { tables: [], items: [] })
            .items;
        
    return result;
},

And have you tried anything yourself? Can you share the results? — Scott Sauyet
– Scott Sauyet, Commented Mar 23, 2020 at 18:27

marc_s · Accepted Answer · 2022-05-29 20:47:52Z

1

You could take a dynamic approach and store the wanted data sets in an object and iterate the entries form the object. Then group by id and get all items back.

This approach uses an object as hash table with id as key and an array as storage for the result set. If an id is not known, a new object with id and previously used keys with null value are used. Then the actual data set is added to the object.

Finally for missing tables null values are assigned as well.

const
    getNullProperties = keys => Object.fromEntries(keys.map(k => [k, null]));

var dataset_a = [{ id: 1, name: "apple", color: "red" }, { id: 2, name: "banana", color: "yellow" }],
    dataset_b = [{ id: 1, name: "apple", color: "blue" }, { id: 3, name: "mango", color: "green" }],
    data = { dataset_a, dataset_b },
    result = Object
        .entries(data)
        .reduce((r, [table, rows]) => {
            rows.forEach(({ id, ...row }) => {
                if (!r[id]) r.items.push(r[id] = { id, ...getNullProperties(r.tables) });
                r[id][table] = row;
            });
            r.tables.push(table);
            r.items.forEach(item => r.tables.forEach(t => item[t] = item[t] || null));
            return r;
        }, { tables: [], items: [] })
        .items;

console.log(result);

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

edited May 29, 2022 at 20:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 23, 2020 at 18:49

Nina Scholz

388k26 gold badges367 silver badges417 bronze badges

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

2 Comments

svenema Over a year ago

Very nice and works very well! Will have to sit down with a strong coffee to start to understand it :)

svenema Over a year ago

I've kinda figured out how it works. For those stumbling upon this question/answer later, to make it a dynamic function like "fullOuterJoin(dataset_a, dataset_b, key)", change the forEach "destructuring" into: rows.forEach(({ [key]:id, ...row }) => {

ErikM · Accepted Answer · 2020-03-23 18:45:19Z

1

A code snippet for specifically your need:

const datasetA = [ { id: 1, name: "apple", color: "red" }, {id: 2, name: "banana", color: "yellow"} ]
const datasetB = [ { id: 1, name: "apple", color: "blue" }, {id: 3, name: "mango", color: "green"} ]


const joined = [];

// datasetA
for (let i = 0; i < datasetA.length; i++) {
    let item = {
        id: datasetA[i].id,
        dataset_a: datasetA[i],
    };
    joined.push(item);
}
// datasetB
for (let i = 0; i < datasetB.length; i++) {
    const foundObject = joined.find(d => d.id === datasetB[i].id);
    if (foundObject) {
        foundObject['dataset_b'] = datasetB[i];
    }
    else {
        let item = {
            id: datasetB[i].id,
            dataset_a: {},
            dataset_b: datasetB[i],
        };
        joined.push(item);
    }
}

console.log(joined);

answered Mar 23, 2020 at 18:45

ErikM

1542 bronze badges

1 Comment

svenema Over a year ago

Works, as does Nina's approach, which has the added benefit to by dynamic. Will do a performance comparison later. Thanks for sharing, it's very educative to have a closer look at different approaches.

AssoDiPicche · Accepted Answer · 2020-03-23 18:25:05Z

var array1 = [ { id: 1, name: "apple", color: "red" }, {id: 2, name: "banana", color: "yellow"} ]
var array2 = [ { id: 1, name: "apple", color: "blue" }, {id: 3, name: "mango", color: "green"} ]

var array_sum = array1.concat(array2)

var array_result = []

array_sum.forEach(function(candidate, index){
  var obj_id = candidate.id;
  delete candidate.id
  if(array_result.length == 0){
    array_result.push({
      "id": obj_id,
      ["dataset_" + index]: candidate
    })
  }else{
    for(var i=0; i<array_result.length; i++){
      if(array_result[i].id == obj_id){
        array_result[i]["dataset_" + index] = candidate
        break;
      }else if(i == array_result.length - 1){
        array_result.push({
          "id": obj_id,
          ["dataset_" + index]: candidate
        })
      }
    }
  }
})
console.log(array_result)

Collectives™ on Stack Overflow

Javascript: Full Outer Join on a Array of Objects, using one key

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related