Merge items from two arrays based on matching ID

Question

I have a data object like this :

{
  "data1": [
    [
      "ID",
      "name",
      "Birthday"
    ],
    [
      "10",
      "thomas",
      "1992-03-17"
    ],
    [
      "11",
      "Emily",
      "2000-03-03"
    ]
  ],
  "data2": [
    [
      "Balance",
      "ID"
    ],
    [
      "$4500",
      "10"
    ],
    [
      "$1500",
      "13"
    ]
  ]
}

It contains two arrays data1 and data2. The first row in each array is the name of the columns and the rest of the rows have the data (think of it like a table).

I want to compare the ID field in both arrays and if the IDs match then the final output will contain a column Balance with the balance corresponding to that ID and if the IDs don't match then the Balance will be $0.

Expected output:

{
  "output": [
    [
      "ID",
      "name",
      "Birthday",
      "Balance"
    ],
    [
      "10",
      "thomas",
      "1992-03-17",
      "$4500" //ID 10 matched so the balance added here
    ],
    [
      "11",
      "Emily",
      "2000-03-03",
      "0" //0 bcoz the ID 11 is not there in data2 array
    ]
  ]

}

I find this challenging to accomplish. Think of it like a LEFT-JOIN in MySQL. I referred to this solution but it doesn't work in my case as I don't have the keys in my response.

EDIT: I also need to join the other fields as well.

Answer 1

You can use Array.prototype.map(), find, filter, slice, reduce, concat, includes and Object.assign().

This solution:

• Handles arbitrary ordering of the items. The order is read from the headers.
• Appends a Balance field only if there is one present in data2.
• Joins all other fields (requested by OP, see comments below).
• Takes default values as an input and uses them if the data is not present in data1 and data2.

function merge({ data1, data2 }, defaults) {
  // get the final headers, add/move 'Balance' to the end
  const headers = [...data1[0].filter(x => x !== 'Balance')]
    .concat(data2[0].includes('Balance') ? ['Balance'] : []);
  
  // map the data from data1 to an array of objects, each key is the header name, also merge the default values.
  const d1 = data1.slice(1)
    .map(x => x.reduce((acc, y, i) => ({ ...defaults, ...acc, [data1[0][i]]: y }), {}));
  // map the data from data2 to an array of objects, each key is the header name
  const d2 = data2.slice(1)
    .map(x => x.reduce((acc, y, i) => ({ ...acc, [data2[0][i]]: y }), {}));
  
  // combine d1 and d2
  const output = d1.map((x, i) => { // iterate over d1
    // merge values from d2 into this value
    const d = Object.assign(x, d2.find(y => y['ID'] === x['ID']));
    // return an array ordered according to the header
    return headers.map(h => d[h]);
  });
  return { output: [headers, ...output] };
}

const test0 = {
  data1: [[ "ID","name","Birthday","other"],["10","thomas","1992-03-17","empty"],["11","Emily","2000-03-03","empty"]],
  data2: [["other", "ID", "Balance", "city"],["hello", "10", "$4500", "New York"],["world", "10","$8","Brazil"]]
};

const test1 = {
  data1: [["ID","name","Birthday"],["10","thomas","1992-03-17"],["11","Emily","2000-03-03"]],
  data2: [["other","ID"],["x","10"],["y","11"]]
};

console.log(merge(test0, { Balance: '$0' }));
console.log(merge(test1, { Balance: '$0' }));

Answer 2

const KEY_ID = "ID";

var data = {
  "data1": [
    [ "ID", "name", "Birthday" ],
    [ "10", "thomas", "1992-03-17" ],
    [ "11", "Emily", "2000-03-03" ]
  ],
  "data2": [
    [ "Balance", "ID" ],
    [ "$4500", "10" ],
    [ "$1500", "13" ]
  ]
}

var merged = Object.keys(data).map(function (key) {
  var tmp = data[key].slice();
  var heads = tmp.shift();
  return tmp.map(function (item) {
    var row = {};
    heads.forEach(function (head, i) {
      row[head] = item[i];
    });
    return row;
  });
}).flat().reduce(function (acc, row) {
  var found = acc.find(function (item) {
    return row[KEY_ID] === item[KEY_ID];
  })
  if (!found) {
    found = row;
    acc.push(found);
  } else {
    Object.keys(row).forEach(function (head) {
      found[head] = row[head];
    });
  }
  return acc;
}, []);

console.log(merged);

This solution is scalable: if you add properties, it will scale the new format.

Answer 3

You could abstract all table operations into a class-like:

 function Table(array) {
   const [head, ...values] = array;

  const Entry =(entry) => ({
   get(key) { return entry[ head.indexOf(key) ]; },
   set(key, value) { entry[ head.indexOf(key) ] = value; }
  });

  return {
    index(name) {       
      const result = {};
      for(const value of values)
        result[ value[ head.indexOf(name) ] ] = Entry(value);

      return result;
    },
    *[Symbol.iterator]() {
      for(const row of values)
         yield Entry(row);
     },

      addRow(key) { head.push(key); }
   };
 }

Usable as:

 const users = Table(obj.data1);
 const balances = Table(obj.data2);

 const balanceByID = balance.index("ID");

 users.addRow("Balance");

 for(const user of users)
   user.set("Balance", balanceByID[ user.get("ID") ].get("Balance"));

Answer 4

let a = { "data1": [ ... ],"data2": [ ...] }
let r = a.data1.reduce((r,u,i)=>{
  if(i !== 0)
  {
    let entry = a.data2.filter((a)=> a[1]===u[0])
    r.push([...u,entry.length ? entry[0][0] : 0])
  }
   return r
},[[
      "ID",
      "name",
      "Birthday",
      "Balance"
]])