Combining two data sets based on programme name-CodePudding

Hi I am trying to work out the best way to achieve something. I am essentially making two database calls

const [emails] = await dbConnection.execute('SELECT name, programme, timestamp FROM emails');
const [emailsCancelled] = await dbConnection.execute('SELECT data FROM emails where name = "email.cancelled"');

The reason I am making two calls is that I am processing over hundred thousand rows, and the data field contains quite a bit of JSON data, so don't want to retrieve that for all the rows.

So with the emails, I get data back in the following format

[
    {
    name: 'email.sent',
    programme: 'Email One',
    timestamp: 2022-03-24T18:06:02.000Z
    },
    {
    name: 'email.sent',
    programme: 'Email Two',
    timestamp: 2022-03-24T18:06:02.000Z
    },
    {
    name: 'email.sent',
    programme: 'Email One',
    timestamp: 2022-03-24T18:06:02.000Z
    },
    ...
]

So what I needed to do is group by programme, to identify how many were sent and the total count. I do obtain some other details but reduced for this post. To do this I do

const emailsReduced = await emails.reduce((acc, o) => {
  const name = o.name?.replace('email.', '');
  if (!acc[o.programme]) {
    acc[o.programme] = {
      count: 0,
      sent: 0,
    };
  }
  acc[o.programme].count = (acc[o.programme].count || 0)   1;
  acc[o.programme][name] = (acc[o.programme][name])   1;

  return acc;
}, {});

And that will return something like this

'Email One': {
    count: 2,
    sent: 2,
},
'Email Two': {
    count: 1,
    sent: 1,
},

Now emailsCancelled returns JSON data. So what I can do is loop it and show an example out the part I need

Object.entries(emailsCancelled).forEach(([key, value]) => {
  const data = JSON.parse(value.data);
  if (data?.payload?.body?.toUpperCase() === 'STOP') {
    console.log(data?.payload?.correlation?.metadata);
  }
});

And that will produce rows like this

[
    { customerId: '12345', programName: 'Email One' },
    { customerId: '2321', programName: 'Email Two' },
    { customerId: '33321', programName: 'Email Two' }
]

Now what I need to do is get that into the original array as a count. So you can see that there was 1 cancelled for Email One, and 2 for Two. So I need to add this in like so, matching it based on the programme name.

'Email One': {
    count: 2,
    sent: 2,
    cancelled: 1,
},
'Email Two': {
    count: 1,
    sent: 1,
    cancelled: 2,
},

How can I achieve something like this?

Thanks

Actual format

{
   "name":"email.cancelled",
   "payload":{
      "body":"STOP",
      "correlation":{
         "metadata":{
            "customerId":"232131232113",
            "programName":"Email One"
         }
      },
      "id":"123454323343232",
      "receivedOn":"2022-05-15T12:51:54.403Z"
   },
}

CodePudding user response：

From emailsCancelled, you can reduce your array to a lookup Map before your perform your .reduce() on on emails. The lookup will store the programName as the keys, and the count of that program as the values:

const emails = [
    { customerId: '12345', programName: 'Email One' },
    { customerId: '2321', programName: 'Email Two' },
    { customerId: '33321', programName: 'Email Two' }
];

const lut = emails.reduce((map, {programName}) => 
  map.set(programName, (map.get(programName) || 0)   1)
, new Map);

console.log(lut.get("Email One"));
console.log(lut.get("Email Two"));

You can build this Map directly from your .forEach() loop also, note that I'm using Object.values() instead of .entries() as you're only intrested in the values and not the keys:

const lut = new Map();
Object.values(emailsCancelled).forEach(value => {
  const data = JSON.parse(value.data);
  if (data?.payload?.body?.toUpperCase() === 'STOP') {
    const programName = data.payload.correlation?.metadata?.programName; // if `correcltation`, or `metadata` or `programName` don't exist, use optional chaining and an if-statement to check for `undefined` before updating the map.
    lut.set(programName, (map.get(programName) || 0)   1)
  }
});

You can then use this lookup lut Map when you use .reduce() on emails to work out the cancelled value, defaulting cancelled to 0 if the programme can't be found in the Map:

const emailsReduced = await emails.reduce((acc, o) => {
  const name = o.name?.replace('email.', '');
  if (!acc[o.programme]) {
    acc[o.programme] = {
      count: 0,
      sent: 0,
      cancelled: lut.get(o.programme) || 0 // default to zero if program can't be found
    };
  }
  acc[o.programme].count = acc[o.programme].count   1;
  acc[o.programme][name] = acc[o.programme][name]   1;

  return acc;
}, {});

CodePudding user response：

Assuming your data structures look like these, you can map and filter according to emails keys:

const emails = [
    { 'Email One': {
    count: 2,
    sent: 2,
    }},
    {'Email Two': {
    count: 1,
    sent: 1,
    }}
]

const canceled = [
  { customerId: '12345', programName: 'Email One' },
  { customerId: '2321', programName: 'Email Two' },
  { customerId: '33321', programName: 'Email Two' }
]

const newmails = emails.map(mail => {
  let strmail = Object.keys(mail)
  let ncanceled = canceled.filter(item => {
    return item.programName == strmail
  }).length
  mail[strmail].canceled = ncanceled
  return mail
})

console.log(newmails)

CodePudding user response：

Try this!

const emails = [{
    'Email One': {
      count: 2,
      sent: 2,
      cancelled: 0,
    },
  },
  {
    'Email Two': {
      count: 1,
      sent: 1,
      cancelled: 0,
    },
  },
];

const cancelled_emails = [{
    customerId: '12345',
    programName: 'Email One'
  },
  {
    customerId: '2321',
    programName: 'Email Two'
  },
  {
    customerId: '33321',
    programName: 'Email Two'
  },
];



for (let cancelled_email of cancelled_emails) {
  let prg_name = cancelled_email.programName;

  for (email of emails) {

    if (Object.keys(email)[0] === prg_name) {
      email[prg_name].cancelled  = 1;
    }
  }
}

console.log(emails);