1

Background

I have two arrays (Array 1 and Array 2). Both of them share an ID type (Column 2 of Array 1 and Column 0 of Array 2). I want to combine the two arrays vertically only when there is a match in that ID.

Starting Arrays:

array1 = [[AB2C, Red, 113]       <--Match
          [BE4F, Green,164]
          [AE3G, Blue, 143]];    <--Match

array2 = [[143, FabricB2, W5]    <--Match
          [189, FabricC9, W4]
          [113, FabricA3, W5]];  <--Match

Desired Ending Array

array3 = [[AB2C, Red, 113, FabricA3, W5]
          [AE3G, Blue, 143, FabricB2, W5]];

Methodology

It seems to me like the most efficient way to do this would be to:

  1. Create an empty array.
  2. Push Array 1 data to Array 3 if the ID column makes a match with one in Array 2.
  3. Push Array 2 data to Array 3 where the ID column matches with Array 3. We should also only add Columns 1 & 2 of Array 2 so we aren't duplicating the ID Column.

What I've Tried

For Step 2 I've been trying to push Array 1 to Array 3 with a push and map combo like the below but it's not working properly. My intention was to take each row of Array 1, run it through every row of Array 2 to check for a match; if there's a match then push to Array 3.

For Step 3 my thought was to take each row of Column 2 of Array 3, run it through each row of Column 0 of Array 2, and when finding a match, concat those to Array 3. I never got past the equation for Step 2 in order to get to this.

I would REALLY appreciate your help!

array1 = [[AB2C, Red, 113]
          [BE4F, Green,164]
          [AE3G, Blue, 143]];

array2 = [[143, FabricB2, W5]
          [189, FabricC9, W4]
          [113, FabricA3, W5]];

array3 = [];

array3.push(array1.map( function(e) { return e[2] == array2.map ( function (f) { return f[0] } ) }));
Improve this question
1
  • Quote "" your array values or they're invalid. Commented Apr 17, 2020 at 15:12

4 Answers 4

Reset to default
1 

array1 = [
  ['AB2C', 'Red', 113],
  ['BE4F', 'Green', 164],
  ['AE3G', 'Blue', 143],
];

array2 = [
  [143, 'FabricB2', 'W5'],
  [189, 'FabricC9', 'W4'],
  [113, 'FabricA3', ' W5'],
];


const res = array1
  .map(x => array2
    .filter(y => y[0] === x[2])
    .map(y => [x[0], x[1], ...y]))
  .flatMap(x => x)

console.log(res)

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

1 Comment

Thank you. I particularly liked this option because it allowed for an increasing number of columns in array2 without having to specify them individually.
1
  • Use map to create a map of array2.
  • Filter out array1 based on whether Map has this array's id.
  • Since filter is a loop, it is possible to modify the row inside filter 

const array1 = [
  ['AB2C', 'Red', 113],
  ['BE4F', 'Green', 164],
  ['AE3G', 'Blue', 143],
];

const array2 = [
  [143, 'FabricB2', 'W5'],
  [189, 'FabricC9', 'W4'],
  [113, 'FabricA3', ' W5'],
];

const array2Map = new Map;
array2.forEach(([row0, ...rest]) => array2Map.set(row0, rest));

const out = array1.filter(row => array2Map.has(row[2]) && row.push(...array2Map.get(row[2])));
console.info(out)

Improve this answer

1 Comment

Thank you for your consistent input on my questions!!
0

I think this should do the trick for you:

let array1 = [['AB2C', 'Red', 113], ['BE4F', 'Green',164], ['AE3G', 'Blue', 143]];

let array2 = [[143, 'FabricB2', 'W5'], [189, 'FabricC9', 'W4'], [113, 'FabricA3', 'W5']];

let array3 = []
array1.forEach(i => {
  let match = i[2]
  array2.forEach(j => {
    if(j[0] === match) {
      array3.push([...i, j[1], j[2]])
    }
  })
})

console.log(array3)

Sandbox Link

Improve this answer

1 Comment

Disadvantage being you can't break the .forEach(), if a match is found. This leads unnecessary looping after finding the match. A plain for-loop here might be better.
0

Here's an alternative approach you might be interested in.

Now that we have the V8 runtime in Apps Script, you can leverage a 3rd party library like AlaSQL to perform complex SQL queries.

Just copy-and-paste the latest version of AlaSQL (full or minified) into is own script file in your Apps Script project.

Then you can run queries like this:

const arr1 = [
  ['AB2C', 'Red', 113],
  ['BE4F', 'Green', 164],
  ['AE3G', 'Blue', 143],
].map( ([code, color, id]) => ({id, color, code}));

const arr2 = [
  [143, 'FabricB2', 'W5'],
  [189, 'FabricC9', 'W4'],
  [113, 'FabricA3', 'W5'],
].map( ([id, fabric, textile]) => ({id, fabric, textile}));

const query = `
    SELECT arr1.id, arr1.code, arr1.color, arr2.fabric, arr2.textile
    FROM ? AS arr1
    INNER JOIN ? AS arr2 ON arr1.id = arr2.id
`;

var res = alasql(
        query, 
        [arr1, arr2]
    ).map(
        ({id, code, color, fabric, textile}) => [code, color, id, fabric, textile]
    );

console.log(res);
<script src="https://cdnjs.cloudflare.com/ajax/libs/alasql/0.5.5/alasql.min.js"></script>

I took a few liberties with your data tables converting them from an array of arrays to an array of objects and back again using destructuring assignments. The real power of this approach is that it allows you to leverage standard SQL to run all sorts of complex queries across multiple datasets.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.