COALESCE Joins in JAVASCRIPT

Question

I am trying to join several arrays in Javascript with some kind of "COALESCE" function like in SQL.

Below are my arrays:

var items = [
    {itemid: 'A'},
    {itemid: 'B'},
    {itemid: 'C'},
    {itemid: 'D'},
    {itemid: 'E'}
];

var specials = [
    {itemid: 'A', price: '5.00'},
    {itemid: 'C', price: '5.00'},
    {itemid: 'E', price: '5.00'}
];

var mainprices = [
    {itemid: 'A', price: '10.00'},
    {itemid: 'B', price: '10.00'},
    {itemid: 'C', price: '10.00'},
    {itemid: 'D', price: '10.00'},
    {itemid: 'E', price: '10.00'}
];

What I want is : My first column to be itemid
My second column to be prices

I want the price column to retrieve prices from my 'specials' array first, and then for the rows that have no values to return prices from my 'mainprices' array.

Here is what I have tried so far :

var results = [];
for (var i=0; i<items.length; i++) {
    var found = false;
    for (var j=0; j<specials.length; j++) {
        if (items[i].itemid === specials[j].itemid) {
            results.push({
                item_id: items[i].itemid, 
                price_value: specials[j].price
            });
            found = true;
            break;
        }
    }
    if (found === false) {
        results.push({
            item_id: items[i].itemid, 
            price_value: null
        });    
    }
}

console.log(results);

This outputs :

[{item_id: "A", price_value: "5.00"}, {item_id: "B", price_value: null}, {item_id: "C", price_value: "5.00"}, {item_id: "D", price_value: null}, {item_id: "E", price_value: "5.00"}]

What I would like to do now is to replace null values with 'mainprices.price' values

I would really appreciate if someone could help me !!

Cheers

Answer 1

You can .map the mainPrices array, and search for a matching element in specials:

var items = [
    {itemid: 'A'},
    {itemid: 'B'},
    {itemid: 'C'},
    {itemid: 'D'},
    {itemid: 'E'}
];

var specials = [
    {itemid: 'A', price: '5.00'},
    {itemid: 'C', price: '5.00'},
    {itemid: 'E', price: '5.00'}
];

var mainprices = [
    {itemid: 'A', price: '10.00'},
    {itemid: 'B', price: '10.00'},
    {itemid: 'C', price: '10.00'},
    {itemid: 'D', price: '10.00'},
    {itemid: 'E', price: '10.00'}
];

const pricesWithSpecials = mainprices.map(({ itemid, price }) => {
  const found = specials.find((e) => e.itemid === itemid);
  return { itemid, price: found ? found.price : price };
});
console.log(pricesWithSpecials);

Or, for O(N) complexity instead of O(N^2) complexity, map the specials to their itemids first:

var items = [
    {itemid: 'A'},
    {itemid: 'B'},
    {itemid: 'C'},
    {itemid: 'D'},
    {itemid: 'E'}
];

var specials = [
    {itemid: 'A', price: '5.00'},
    {itemid: 'C', price: '5.00'},
    {itemid: 'E', price: '5.00'}
];

var mainprices = [
    {itemid: 'A', price: '10.00'},
    {itemid: 'B', price: '10.00'},
    {itemid: 'C', price: '10.00'},
    {itemid: 'D', price: '10.00'},
    {itemid: 'E', price: '10.00'}
];

const specialsByItemid = specials.reduce((a, item) => {
  a[item.itemid] = item;
  return a;
}, {});

const pricesWithSpecials = mainprices.map(({ itemid, price }) => {
  return { itemid, price: specialsByItemid[itemid] ? specialsByItemid[itemid].price : price };
});
console.log(pricesWithSpecials);