How to solve repetition on a particular set on mongodb

Question

Here this tone is my attached Problem set in a form of an image. Now what I am trying to solve is :

This One is My Problem Set. In this Picture, There are 5 Columns. Out of this 1 to 4th column have some alphabets. Every Alphabets Have its own Weighted Value. So By checking the repetition of alphabets with the weighted value we have to store it in a sorted order on 5th Table i.e Final Value. So what's the procedure for doing this task on node-js / MongoDB?

Actually, this Data is on MongoDB and have looked like this:

{
    "_id" : ObjectId("5a337aab17030d133c11fa2f"),
    "sequenceid" : 1,
    "column1" : [//This column has weighted value 2 
        {"comodity": "a"},
        {"comodity": "d"},
        {"comodity": "c"},
        {"comodity": "b"},
   ],

    "column2" : [//This column has weighted value 2.5 
        {"comodity": "d"},
        {"comodity": "a"},
        {"comodity": "e"},
        {"comodity": "f"},
   ],

    "column3" : [//This column has weighted value 2.5
        {"comodity": "a"},
        {"comodity": "g"},
        {"comodity": "k"},
        {"comodity": "c"},
   ],
    "column4" : [//This column has weighted value 3
        {"comodity": "i"},
        {"comodity": "d"},
        {"comodity": "h"},
        {"comodity": "j"},
   ]
    "__v" : 0
}

What I want is something look like this i.e

d  // d = 7.5 (as d appear in column 1, column 2 and column 4. so Sum its weighted value we get 7.5).
a  //a = 7 (same usecase)
c  // c = 4.5 (same usecase)
i  // i = 3 (same usecase)
h  // h = 3 (same usecase)
j  //j = 3 (same usecase)
e  //e = 2.5 (same usecase)
f  // f = 2.5 (same usecase)
g  // g = 2.5 (same usecase)
k  // k = 2.5 (same usecase)
b // b = 2 (same usecase)

Any Help is appreciated

Answer 1

As far as you have to treat with all data, I would extract old the information with only one request to the database

collection.find({}, ...);

and after that you can loop through the values of each column. In an object, I'd check if the alphabet is already in the object, if it is add the value of the column to its value, and if it is not, add the new alphabet to the object

var result = {
    "sequenceid" : 1,
    "column1" : [//This column has weighted value 2 
        {"comodity": "a"},
        {"comodity": "d"},
        {"comodity": "c"},
        {"comodity": "b"},
   ],

    "column2" : [//This column has weighted value 2.5 
        {"comodity": "d"},
        {"comodity": "a"},
        {"comodity": "e"},
        {"comodity": "f"},
   ],

    "column3" : [//This column has weighted value 2.5
        {"comodity": "a"},
        {"comodity": "g"},
        {"comodity": "k"},
        {"comodity": "c"},
   ],
    "column4" : [//This column has weighted value 3
        {"comodity": "i"},
        {"comodity": "d"},
        {"comodity": "h"},
        {"comodity": "j"},
   ]
};

var columns = [
    {name: "column1", value: 2}, 
    {name: "column2", value: 2.5}, 
    {name: "column3", value: 2.5}, 
    {name: "column4", value: 3}, 
];

var total = {};

for(let column of columns) {
    for(let comodity of result[column.name]) {
        var alphabet = comodity.comodity;
        if(total[alphabet]) {
            total[alphabet] += column.value;
        } else {
            total[alphabet] = column.value;
        }
    }
}


console.log(total);

This gets result you are looking for:

{ a: 7, d: 7.5, c: 4.5, b: 2, e: 2.5, f: 2.5, g: 2.5, k: 2.5, i: 3, h: 3, j: 3 }