How to do a join from 2 JSON by a common ID field in Javascript

Question

I have two JSON files: JSON A has some company properties and the company_id, while JSON B has company names and company ids.

JSON A example:

[
  {
    "order_name": "Foo",
    "company_id": "112233"
  },
  {
    "order_name": "Bar",
    "company_id": "123456"
  }
]

JSONB example:

[
  {
     "company_id":"112233",
     "name":"ACME company",
  },
  {
     "company_id":"123456",
     "name":"John Doe Inc.",
  }
]

Which is the most efficient way to do a join by the company_id values? I would like to have the JSON C (merged result) with the company names correctly added, like this:

[
  {
    "order_name": "Foo",
    "company_id": "123456",
    "company_name": "John Doe Inc."
  },
  {
    "order_name": "Bar",
    "company_id": "112233",
    "company_name": "ACME company"
  }
]

Is looping and filter for each the only solution? Is there a more efficient way to do this from a performance point of view?

More info:

JSON is not sorted by company_id.
Array A could have more than one object with the same company_id
I'm using Javascript (in a Vue.js app), I don't need to support old browsers

https://stackoverflow.com/questions/30093561/merge-two-json-object-based-on-key-value-in-javascript — Emil Karlsson, Sep 28 '21 at 10:04
_"Which is the most efficient way..."_ - Asks for an (most likely) opinion-based answer. And what defines "efficiency" in the first place? — Andreas, Sep 28 '21 at 10:05
@Andreas aren't this JSON objects? For efficiency I mean from a performance point of view, I've added it on the question, thanks. — Andrea Bruno Brunato, Sep 28 '21 at 10:11
@EmilKarlsson This solutions should work, but is there a better way (lesser code, better performance) to do this nowadays, like using "filter" or "map"? — Andrea Bruno Brunato, Sep 28 '21 at 10:14
[There's no such thing as a "JSON Object"](http://benalman.com/news/2010/03/theres-no-such-thing-as-a-json/) — Andreas, Sep 28 '21 at 10:24

score 1 · Answer 1 · answered Sep 28 '21 at 10:16

I hope this will work for you. Let me know if you have any questions.

const arrayOne = [
  {
    "order_name": "Foo",
    "company_id": "112233"
  },
  {
    "order_name": "Bar",
    "company_id": "123456"
  }
];

const arrayTwo = [
  {
     "company_id":"112233",
     "name":"ACME company",
  },
  {
     "company_id":"123456",
     "name":"John Doe Inc.",
  }
];

const [source, target] = arrayOne.length > arrayTwo.length
  ? [arrayOne, arrayTwo]
  : [arrayTwo, arrayOne];
  
const merged = source.map(object =>
{
    // Assuming that in the 2nd array, the match is only found 1 time and it EXISTS.
    
    const matched = target.find(element => element.company_id === object.company_id);
    
    // Merge both objects together
    
    return {
         ...object,
         ...matched
    };
});

console.log(merged);

your code implementation is correct at all but needs more attention, the "matched" object could be "undefined". spread an undefined object will cause an exception. — nima, Sep 28 '21 at 10:35
If you check the comment, I specifically wrote that "Assuming that the object exists" and your solution is EXACTLY like mine. — Zahid Saeed, Sep 28 '21 at 14:36

score 1 · Answer 2 · answered Sep 28 '21 at 10:22

By having JSONs:

const jsonA = [
    {
      "order_name": "Foo",
      "company_id": "112233"
    },
    {
      "order_name": "Bar",
      "company_id": "123456"
    }
];

const jsonB = [
    {
       "company_id":"112233",
       "name":"ACME company",
    },
    {
       "company_id":"123456",
       "name":"John Doe Inc.",
    }
];

you can merge maps into 3rd map with something like this:

const transform = (data, current={}) => 
    data.reduce((prev, company) => {
        if(!prev[company['company_id']]) prev[company['company_id']] = {};
        prev[company['company_id']] = {...prev[company['company_id']], ...company}
        return prev;
    }, current);

let jsonMap = transform(jsonA, {});
jsonMap = transform(jsonB, jsonMap);

let jsonC = Object.keys(jsonMap).map(companyId => jsonMap[companyId] );
console.log(jsonC);

nima · Accepted Answer · 2021-09-28T11:09:43.007

In common modern JavaScript, you can do this as you mentioned with higher-order functions like map, filter, and so on:

const arrayA = [
  {
    "order_name": "Foo",
    "company_id": "112233"
  },
  {
    "order_name": "Bar",
    "company_id": "123456"
  }
]

const arrayB = [
  {
     "company_id":"112233",
     "name":"ACME company",
  },
  {
     "company_id":"123456",
     "name":"John Doe Inc.",
  }
]

const mergeAB = arrayA.map( companyA => {
    const matched = arrayB.find(companyB => companyB.company_id === companyA.company_id)
    if(matched) {
      return {...companyA, ...matched}
    } else {
      // return companyA element or customize it with your case
    }
  }
)

console.log(mergeAB)

Note 1: Array.find() method complexity is O(n) and Array.map() method complexity is O(n)

Note 2: efficiency is an important thing but not in all situations. sometimes you need to do these types of iteration one time or for a small array size, so no need to worry about the performance.

Note 3: you could compare the answer and find out your best solution since we don't know about your whole code and application.

Tip: if you leave the else empty as this example, you can have "undefined" values. I suggest to return {...companyA} to have a complete array. — Andrea Bruno Brunato, Sep 28 '21 at 14:59
thanks for your explanation, as you can see I recommend returning compnayA in else block. — nima, Sep 29 '21 at 08:30

How to do a join from 2 JSON by a common ID field in Javascript

3 Answers3