Group by/order by On JSON data using javascript/jq

Question

I have a JSON data and I need to do something like group by and i asked this question before here but i am not getting any satisfied answer so this time i would like to explain more in depth.

First, Can anybody explain me difference between groupby and orderby in javascript as in sql we do required aggregate function in order to use group by. But I don't want anything like aggregate function. Here I provide a very sample JSON data and Output that I was looking for.

All author names should be sortby alphanumeric order.

JSON data:

var myObject = {
    "Apps": [
        {
            "Name": "app1",
            "id": "1",
            "groups": [
                {
                    "id": "1",
                    "name": "test group 1",
                    "category": "clinical note",
                    "author": "RRP"
                }, {
                    "id": "2",
                    "name": "test group 2",
                    "category": "clinical image",
                    "author": "LKP"
                }, {
                    "id": "3",
                    "name": "test group 3",
                    "category": "clinical document",
                    "author": "RRP"
                }, {
                    "id": "4",
                    "name": "test group 4",
                    "category": "clinical note",
                    "author": "John"
                }
            ]
        }
    ]
}

Expected output:

John
  4 testgroup4 clinicalnote 
RRP
  1 testgroup1  clinicalnote
  3 testgroup3  clinicaldocument
LKP
  2 testgroup2 clinicalimage    

Any idea/suggestion/direction/thought would be great help.

Answer 1

Use first class functions to create generic group by key reducers and a sorters. I will take the reducer from a previous answer of mine. That way you can sort and group by any key you like, in a similar way that you would do in SQL.

const groupBy = key => (result,current) => {
  const item = Object.assign({},current);
  if (typeof result[current[key]] == 'undefined'){
    result[current[key]] = [item];
  }else{
    result[current[key]].push(item);
  }
  return result;
};

const stringSortBy = key => (a,b) => {
   const sa = a[key];
   const sb = b[key];
   return sa < sb ? -1 : +(sa > sb);
};

const myObject = {
    "Apps": [
        {
            "Name": "app1",
            "id": "1",
            "groups": [
                {
                    "id": "1",
                    "name": "test group 1",
                    "category": "clinical note",
                    "author": "RRP"
                }, {
                    "id": "2",
                    "name": "test group 2",
                    "category": "clinical image",
                    "author": "LKP"
                }, {
                    "id": "3",
                    "name": "test group 3",
                    "category": "clinical document",
                    "author": "RRP"
                }, {
                    "id": "4",
                    "name": "test group 4",
                    "category": "clinical note",
                    "author": "John"
                }
            ]
        }
    ]
}

const categories = myObject.Apps[0].groups.reduce(groupBy('category'),{});
console.log(categories);
const sorted = myObject.Apps[0].groups.sort(stringSortBy('author'));
console.log(sorted);

Answer 2

You can do that easily with Underscore.js:

_.chain(myObject.Apps[0].groups).sortBy("author").groupBy("author").value();

Outputs a JSON object:

{
 "John":[{"id":"4","name":"test group 4","category":"clinical note","author":"John"}],
 "LKP":[{"id":"2","name":"test group 2","category":"clinical image","author":"LKP"}],
 "RRP":[{"id":"1","name":"test group 1","category":"clinical note","author":"RRP"},{"id":"3","name":"test group 3","category":"clinical document","author":"RRP"}]
}

Answer 3

There is no built-in "group by" or "order by" in Javascript for this scenario. You're going to have to do this manually. Something like this might help:

var groups = myObject.Apps[0].groups;
var authors = {};
var authorNames = [];

for(var i = 0; i < groups.length; i++) {
    var group = groups[i];    

    if(typeof authors[group.author] === "undefined") {
        authors[group.author] = [];
        authorNames.push(group.author);
        authorNames.sort();
    }

    authors[group.author].push({
        id: group.id,
        name: group.name,
        category: group.category
    });       
}

Usually in associative arrays you don't really care about the order of the keys and while iterating the order is usually not guaranteed. What I'm doing here is maintaining a separate array of names in sorted order. I can then iterate over that array and use those values to grab the associated object out of the associative array.

Check out the fiddle.