0

I have a CSV file that I am reading and outputting to a JSON Object array, I'm trying to pull data out of the array to display. I'm trying to replicate the following SQL query.

SELECT requestor, SUM(change) as Total_Amount FROM `bankdump` WHERE `type`= 'purchase' group by requestor

This is the Array

[
{
    "transaction_id": "44b36e13-ba02-42a6-8b3a-82f6096ec3ba",
    "timestamp": "2021-03-03 18:28:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Winston Bolt",
    "account_id": "61408386"
},
{
    "transaction_id": "6482c419-ff8c-4121-98ae-6f2437646ba8",
    "timestamp": "2021-03-03 17:46:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Winston Bolt",
    "account_id": "61453979"
},
{
    "transaction_id": "0ab6c0cd-4389-4848-92f2-60b54982d5c7",
    "timestamp": "2021-03-03 17:42:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Winston Bolt",
    "account_id": "61428024"
},
{
    "transaction_id": "11580df3-6403-4cf9-a8b0-38b8b1b1b25f",
    "timestamp": "2021-03-03 17:28:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Erin Cox",
    "account_id": "61342083"
},
{
    "transaction_id": "3c6f93e5-ce24-405b-9558-44b2bf3f3653",
    "timestamp": "2021-03-03 17:26:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Ai Musori",
    "account_id": "61712295"
},
{
    "transaction_id": "a14712a0-8808-4686-99b2-ed9639d61fbc",
    "timestamp": "2021-03-03 15:15:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Ai Musori",
    "account_id": "61977423"
},
{
    "transaction_id": "def6ac07-6a7a-48c4-8534-0c68e8ddc46b",
    "timestamp": "2021-03-03 09:06:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Sean Tinker",
    "account_id": "61747726"
},
{
    "transaction_id": "f57432d0-b744-41ef-b6b8-af77b81b17ec",
    "timestamp": "2021-03-03 08:59:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: renewal",
    "requestor": "Sarah Ableton",
    "account_id": "61171658"
},
{
    "transaction_id": "ddcd5fd0-78c2-4e80-a603-09acb589122d",
    "timestamp": "2021-03-03 07:22:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Sean Tinker",
    "account_id": "61111782"
},
{
    "transaction_id": "7e359364-666d-41b3-a2cd-86ffac82e0a1",
    "timestamp": "2021-03-03 03:53:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership Renewal",
    "requestor": "Donovan King",
    "account_id": "61252737"
},
{
    "transaction_id": "448fefc0-4c35-48e4-b9ee-e2ab4e31b8aa",
    "timestamp": "2021-03-03 02:30:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership Fee",
    "requestor": "Donovan King",
    "account_id": "61994863"
},
{
    "transaction_id": "a4417a9c-fbd6-4b2d-8294-244e35dd7774",
    "timestamp": "2021-03-03 02:27:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Renewal",
    "requestor": "Donovan King",
    "account_id": "61773626"
},
{
    "transaction_id": "77c6cb44-9234-47b2-bc6a-6c71770e5621",
    "timestamp": "2021-03-03 00:58:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Barry Benson - Membership",
    "requestor": "Aleksander Sazkaljovich",
    "account_id": "61645306"
},
{
    "transaction_id": "d2dda96e-e5fa-485e-a866-3691e8f98f47",
    "timestamp": "2021-03-02 23:38:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Marlo Stanfield",
    "account_id": "61645416"
},
{
    "transaction_id": "fc6440d0-c5e7-435e-96f6-4ebec660397a",
    "timestamp": "2021-03-02 19:56:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Sarah Ableton",
    "account_id": "61937982"
},
{
    "transaction_id": "7b9e9c3a-a470-435d-907f-e23bd2d264e6",
    "timestamp": "2021-03-02 19:43:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "1000",
    "comment": "Business Services Payment: Membership",
    "requestor": "Gomer Colton",
    "account_id": "61364056"
},
{
    "transaction_id": "99a6c30a-d655-4e2f-9854-608521926afa",
    "timestamp": "2021-03-02 08:11:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Cassie Cupcake",
    "account_id": "61702462"
},
{
    "transaction_id": "ca9c56aa-aecb-4709-bcad-c4549e6d6929",
    "timestamp": "2021-03-02 08:09:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Cassie Cupcake",
    "account_id": "61410116"
},
{
    "transaction_id": "351deafc-ee03-4ca5-ab7e-facb31527034",
    "timestamp": "2021-03-02 07:33:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Cassie Cupcake",
    "account_id": "61116177"
},
{
    "transaction_id": "2f990c57-a1e2-4e0f-a5e1-bba5728945dc",
    "timestamp": "2021-03-02 07:32:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Cassie Cupcake",
    "account_id": "61674804"
},
{
    "transaction_id": "750ffbbe-851a-4601-b20e-f5e1d92fff5a",
    "timestamp": "2021-03-01 23:44:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership Fee",
    "requestor": "Claire  Seducer",
    "account_id": "61578674"
},
{
    "transaction_id": "7d8bf70e-f99a-4326-99f0-0f25e37bb651",
    "timestamp": "2021-03-01 17:01:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "500",
    "comment": "Business Services Payment: Membership",
    "requestor": "Erin Cox",
    "account_id": "61901342"
},
{
    "transaction_id": "96f100ea-c3e1-4d6e-9b2e-5a14abf6e4c9",
    "timestamp": "2021-03-01 16:07:00",
    "direction": "incoming",
    "type": "withdraw",
    "change": "5000",
    "comment": "Expenses",
    "requestor": "Dean Watson",
    "account_id": "64397152"
},
{
    "transaction_id": "ac38020e-bedc-485d-a64a-a56698b80fb6",
    "timestamp": "2021-03-01 15:45:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "500",
    "comment": "Business Services Payment: PAYNE MEMBERSHIP RENEWAL",
    "requestor": "Erin Cox",
    "account_id": "61284625"
},
{
    "transaction_id": "2cd781db-af2f-4205-9b1b-2d3e5cf21767",
    "timestamp": "2021-03-01 15:40:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "500",
    "comment": "Business Services Payment: MEMBERSHIP RENEWAL",
    "requestor": "Erin Cox",
    "account_id": "61496578"
},
{
    "transaction_id": "ae1311df-2b94-44f5-a77f-af080344b487",
    "timestamp": "2021-03-00 09:01:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Sean Tinker",
    "account_id": "61167813"
},
{
    "transaction_id": "7a95a07d-7851-4818-bb65-dd2fbcf8e216",
    "timestamp": "2021-03-00 08:37:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Cassie Cupcake",
    "account_id": "61838391"
},
{
    "transaction_id": "5cd49da0-8099-450c-9aa3-216c8ea7992d",
    "timestamp": "2021-03-00 08:33:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Cassie Cupcake",
    "account_id": "61718997"
},
{
    "transaction_id": "3eadcc1a-58a3-43bf-8d04-23fab3e461a0",
    "timestamp": "2021-03-00 03:20:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Riley Carter",
    "account_id": "61538217"
},
{
    "transaction_id": "5c72dee8-1176-40c5-8a2b-2a4fcddb81a1",
    "timestamp": "2021-03-00 03:19:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Violet Van Housen",
    "account_id": "61282400"
},
{
    "transaction_id": "fa231345-cb1a-4e54-82df-f4c7ae3b2c8d",
    "timestamp": "2021-03-00 03:18:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Riley Carter",
    "account_id": "61128317"
},
{
    "transaction_id": "3ac2b6ff-aa17-437b-bdf7-f72d53045888",
    "timestamp": "2021-03-00 03:17:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Violet Van Housen",
    "account_id": "61724462"
},
{
    "transaction_id": "fe14857d-1eea-4340-8c5d-7b5a5f8129e1",
    "timestamp": "2021-03-00 03:16:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Riley Carter",
    "account_id": "61453100"
},
{
    "transaction_id": "780132a8-b9ee-4302-be70-8fc70e7d7296",
    "timestamp": "2021-03-00 03:11:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Violet Van Housen",
    "account_id": "61848251"
},
{
    "transaction_id": "c895ce22-79e0-4821-9fdc-491045b7f9ec",
    "timestamp": "2021-03-00 01:32:00",
    "direction": "outgoing",
    "type": "transfer",
    "change": "10000",
    "comment": "Employee Payment: Salary (advance)",
    "requestor": "Dean Watson",
    "account_id": "61857315"
},
{
    "transaction_id": "28a034f4-1229-43a8-bd0d-2c0d26429353",
    "timestamp": "2021-03-06 20:23:00",
    "direction": "outgoing",
    "type": "transfer",
    "change": "10000",
    "comment": "Shaq / Abe Vaughn - Advertising @ No3",
    "requestor": "Dean Watson",
    "account_id": "61201843"
},
{
    "transaction_id": "e78a2121-3295-4634-a28d-d427ea7b7900",
    "timestamp": "2021-03-06 19:58:00",
    "direction": "outgoing",
    "type": "transfer",
    "change": "30000",
    "comment": "Employee Payment: Salary",
    "requestor": "Dean Watson",
    "account_id": "61143341"
},
{
    "transaction_id": "545d4003-8f16-468e-afc3-efa00ae95ae6",
    "timestamp": "2021-03-06 19:57:00",
    "direction": "outgoing",
    "type": "transfer",
    "change": "20000",
    "comment": "Car down payment",
    "requestor": "Dean Watson",
    "account_id": "61143341"
},
{
    "transaction_id": "283c6664-687c-48e4-bb78-09051c0c37b9",
    "timestamp": "2021-03-06 18:59:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Ai Musori",
    "account_id": "61824658"
},
{
    "transaction_id": "d53edd06-2858-4f30-9609-acb599e51abe",
    "timestamp": "2021-03-06 18:11:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Ai Musori",
    "account_id": "61437362"
},
{
    "transaction_id": "9994db52-22e3-4c4b-b56b-ceab12ecb0d6",
    "timestamp": "2021-03-06 15:04:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Ai Musori",
    "account_id": "61656896"
},
{
    "transaction_id": "b1ff9085-0799-4b89-a822-cffcd8f761b0",
    "timestamp": "2021-03-06 15:02:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Sean Tinker",
    "account_id": "61682962"
},
{
    "transaction_id": "81d9c5fd-36ed-4594-901b-a8039f535eeb",
    "timestamp": "2021-03-06 15:00:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "1000",
    "comment": "Business Services Payment: Membership",
    "requestor": "Gomer Colton",
    "account_id": "61901644"
},
{
    "transaction_id": "6ad3a76b-b2ab-44a5-9233-f337c474b75e",
    "timestamp": "2021-03-06 12:27:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Gomer Colton",
    "account_id": "61420031"
},
{
    "transaction_id": "4b294212-9451-4bc8-8950-3810c7e59765",
    "timestamp": "2021-03-06 12:24:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Gomer Colton",
    "account_id": "61267568"
},
{
    "transaction_id": "e827c214-cab2-48e7-8367-8061ed633988",
    "timestamp": "2021-03-04 01:41:00",
    "direction": "outgoing",
    "type": "transfer",
    "change": "20000",
    "comment": "Cassie car down payment",
    "requestor": "Dean Watson",
    "account_id": "61209808"
},
{
    "transaction_id": "47c64f4b-3196-477d-a336-be4fca4903b1",
    "timestamp": "2021-03-04 01:41:00",
    "direction": "outgoing",
    "type": "transfer",
    "change": "20",
    "comment": "Cassie car down payment",
    "requestor": "Dean Watson",
    "account_id": "61209808"
},
{
    "transaction_id": "b8833152-c541-40f9-b7cc-cfb6dc51cd02",
    "timestamp": "2021-03-03 15:24:00",
    "direction": "outgoing",
    "type": "transfer",
    "change": "100",
    "comment": "Employee Payment: Expemses",
    "requestor": "Dean Watson",
    "account_id": "61762557"
},
{
    "transaction_id": "3bf5298b-7411-4180-adeb-72abfa2bb903",
    "timestamp": "2021-03-03 00:30:00",
    "direction": "incoming",
    "type": "withdraw",
    "change": "10000",
    "comment": "",
    "requestor": "Otto Delmar",
    "account_id": "64397152"
},
{
    "transaction_id": "cd394900-23c5-4130-86ca-a55671b3963c",
    "timestamp": "2021-03-02 20:26:00",
    "direction": "incoming",
    "type": "purchase",
    "change": "999",
    "comment": "Business Services Payment: Membership",
    "requestor": "Marlo Stanfield",
    "account_id": "61752944"
},
{
    "transaction_id": "d99042fa-ca26-4326-aa5d-5a8aafa1fce0",
    "timestamp": "2021-03-02 20:21:00",
    "direction": "outgoing",
    "type": "deposit",
    "change": "20000",
    "comment": "Business Payment: Bullshit fine coverage",
    "requestor": "Dean Watson",
    "account_id": "61762557"
}
]

I'm trying to end up with a list of requestors and their totals like so

example output

I want to keep it javascript or jquery strictly without using heavy libraries like node.js and others. I'm using jquery-csv to parse the CSV file on upload via jquery

JSFiddle

Manvaril
  • 160
  • 3
  • 15

1 Answers1

1

Most of this you can do with native array functions. But note that if you have big portion of data all will be cached..

Let's consider json to be the given array of json you provided:

  1. best we start by filtering the stuff we want:
const filtered = json.filter(o => o['type'] === 'purchase')

In the second step, simply pick the fields from the select clause via Array.map()

const picked = filtered .map(o => {
  return { 
    requestor: o['requestor'],
    change: o['change'] 
 }
}
  1. Now we have to apply the sum argument and also to group stuff togehter. This goes all under the name Array.reduce(). We start merging the data into one big object (make it grouped! , we reduce it!, ') , holding the sum of changes per entry:


const grouped_table = data.reduce((grouped, current_object) => {

    // get the requestor
    const requestor = current_object['requestor']

    // first we make sure to store the requestor atleasst once.
    // So if it DOES NOT exist, simply add it with number 0!, in case no
    // changes were found at all for this reqeustor.
    if(typeof grouped[requestor] !== 'number') {
        grouped[requestor] = 0
    }

    // Now that we are sure, the field is set, We add the 
    // change if there is one!

    // get the change value:
    const change = parseInt(current_object['change'])

    // If the change is not a valid number, resp. null or similar, we do not want
    // to add it. We return the state as we already havethe requestor group created.
    if(change === NaN) {
        return grouped
    }

    // change is number, and group is created. Sum it up!
    grouped[requestor] += change

    // make sure to return the grouped ofc!!
    return grouped

// note that the "{}" in the next line, is actually the start value for the "grouped" variable! 
}, {})

The data looks now like that:

enter image description here

  1. Now you have an object, Holding all requestor names as entries and all changes as it's values. You can now iterate over it with Object.keys()
const requestors = Object.keys(grouped_table).forEach(key => {
    console.log(`Requestor "${key}" has a change of ${grouped_table[key]} !`)
}

// Output:
// Requestor "Winston Bolt" has a change of  => 9990
// Requestor "Erin Cox" has a change of  => 10491
// etc.

Resume

That should give you an idea how you can adapt sql queries to javascript without big dependencies. You should focus on learning the native Array Methods. !

Full Code:

const filtered = json.filter(o => o['type'] === 'purchase')

const picked = filtered .map(o => {
  return { 
    requestor: o['requestor'],
    change: o['change'] 
 }
}

const grouped_table = data.reduce((grouped, current_object) => {

    // get the requestor
    const requestor = current_object['requestor']

    // first we make sure to store the requestor atleasst once.
    // So if it DOES NOT exist, simply add it with number 0!, in case no
    // changes were found at all for this reqeustor.
    if(typeof grouped[requestor] !== 'number') {
        grouped[requestor] = 0
    }

    // Now that we are sure, the field is set, We add the 
    // change if there is one!

    // get the change value:
    const change = parseInt(current_object['change'])

    // If the change is not a valid number, resp. null or similar, we do not want
    // to add it. We return the state as we already havethe requestor group created.
    if(change === NaN) {
        return grouped
    }

    // change is number, and group is created. Sum it up!
    grouped[requestor] += change

    // make sure to return the grouped ofc!!
    return grouped
// note that the "{}" in the next line, is actually the start value for the "grouped" variable! 
}, {})



// you can access final summed up data here and put names on it as you want.
const requestors = Object.keys(grouped_table).forEach(key => {
    console.log(`Requestor "${key}" has a change of ${grouped_table[key]} !`)
}

// Output:
// Requestor "Winston Bolt" has a change of  => 9990
// Requestor "Erin Cox" has a change of  => 10491
// etc.
Silvan Bregy
  • 2,544
  • 1
  • 8
  • 21
  • 1
    I had to figure out how to manipulate the data to get it into a table but I figured it out, this is a perfect answer, I will also look into Arrays more – Manvaril Jul 09 '21 at 06:34
  • Great! , If you also want a native csv parsing method have a look at this post => https://stackoverflow.com/questions/1293147/example-javascript-code-to-parse-csv-data Works like a harm! – Silvan Bregy Jul 09 '21 at 06:52
  • Ask a new question, someone will answer it – Silvan Bregy Feb 20 '23 at 10:56