merge array JSON return from API in app script

Question

I using app script to get JSON array from API the array:

var a = '[{numbers=228, id=4152, gendar=female}, {numbers=978, id=8479, gendar=male}, {numbers=101, id=8479, gendar=male}, {numbers=356, id=4152, gendar=female}]';

I want to merge every array has same id. the return need like that:

[{numbers=584, id=4152, gendar=female}, {numbers=779, id=8479, gendar=male}]

Your string is not a valid JSON. Even more, the return example is not a valid javascript code. — B_Joker, Apr 25 '22 at 19:03
@B_Joker I don't know why ?? I using it and work fin for me I add this JSON return to sheet as easy — john, Apr 25 '22 at 19:07

Cooper · Answer 1 · 2022-04-26T14:13:52.763

Merge Objects

function merge() {
  const arr = JSON.parse(Your JSON goes here);
  const x = arr.reduce((a, c, i) => {
    let p = `${c.id}/${c.gendar}`;//name of each object is a string made  up off the id and the gendar
    if (!a.obj.hasOwnProperty(p)) {
      a.obj[p] = c;
      a.obj.pA.push(p);//collects a list of all new object names so that I can iterate through them after adding all of the numbers together for identical property names
    } else {
      a.obj[p].numbers += Number(c.numbers);//If there are properties with identical names id/gendar then I add the numbers together the final array is placed in a.oA by the function getArray() which is inside of a.
    }
    return a;
  }, { obj: { pA: [] }, oA: [], getArray: function () { this.obj.pA.forEach(p => { this.oA.push(this.obj[p]) });return this.oA; } }).getArray();
  Logger.log(JSON.stringify(x));
}

Generate data

function lfunkdata() {
  let arr = [...Array.from(new Array(Math.floor((Math.random() * 20) + 5)).keys(), x => {
    let a = Math.floor((Math.random() * 9000) + 10);//c.numbers
    let b = Math.floor((Math.random() * 10) + 1);//c.id
    let c = Math.round(Math.random() * 1) ? "Male" : "Female";//c.gendar
    return { numbers: a, id: b, gendar: c };
  })];
  let r = JSON.stringify(arr)
  Logger.log(r);
  return r;
}

Simulated Data:

[{"numbers":5115,"id":9,"gendar":"Female"},{"numbers":6598,"id":8,"gendar":"Female"},{"numbers":162,"id":10,"gendar":"Female"},{"numbers":691,"id":8,"gendar":"Male"},{"numbers":684,"id":8,"gendar":"Female"},{"numbers":6941,"id":6,"gendar":"Male"},{"numbers":5822,"id":10,"gendar":"Male"},{"numbers":7453,"id":9,"gendar":"Female"},{"numbers":2663,"id":7,"gendar":"Male"},{"numbers":4031,"id":7,"gendar":"Female"},{"numbers":8581,"id":7,"gendar":"Male"},{"numbers":8940,"id":3,"gendar":"Male"},{"numbers":3622,"id":4,"gendar":"Male"},{"numbers":1842,"id":8,"gendar":"Female"},{"numbers":1653,"id":9,"gendar":"Male"},{"numbers":2085,"id":3,"gendar":"Female"},{"numbers":918,"id":5,"gendar":"Female"},{"numbers":2470,"id":7,"gendar":"Female"},{"numbers":8169,"id":4,"gendar":"Male"},{"numbers":754,"id":6,"gendar":"Female"},{"numbers":7444,"id":1,"gendar":"Male"},{"numbers":6094,"id":1,"gendar":"Male"}]

results:

[{"numbers":12568,"id":9,"gendar":"Female"},{"numbers":9124,"id":8,"gendar":"Female"},{"numbers":162,"id":10,"gendar":"Female"},{"numbers":691,"id":8,"gendar":"Male"},{"numbers":6941,"id":6,"gendar":"Male"},{"numbers":5822,"id":10,"gendar":"Male"},{"numbers":11244,"id":7,"gendar":"Male"},{"numbers":6501,"id":7,"gendar":"Female"},{"numbers":8940,"id":3,"gendar":"Male"},{"numbers":11791,"id":4,"gendar":"Male"},{"numbers":1653,"id":9,"gendar":"Male"},{"numbers":2085,"id":3,"gendar":"Female"},{"numbers":918,"id":5,"gendar":"Female"},{"numbers":754,"id":6,"gendar":"Female"},{"numbers":13538,"id":1,"gendar":"Male"}]

Thanks brother but you code is hard to understand, do you have another comfortable way or add explain for any part to understand how it work — john, Apr 26 '22 at 10:32
I put comments in the code. It's fairly easy to read once you understand how to use the reduce method of javascript arrays. a is the accumlator , c is the current value wihich is an object in the form {c.numbers, c.id, c.gendar}. I chose to name the properties/keys of the object with the string c.id/c.gendar and I keep a list of them in a.obj.pA which is an array. After collecting all of the data and summing all of the numbers of similar objects of the array of objects created by lfunkdata, I used a.obj.pA to iterate over in function getArray to generate my final output. — Cooper, Apr 26 '22 at 14:21
If you look at the generated data and the output data you'll generally see that the output has less objects because similar objects are merged as you put it into each other. I definedd identical objects to be those that had the same id and gendar. By adding up the numbers of the merged elements one can check to see that the numbers in the input data are equal to the sums in the merged elements and I used that to check that the operation is being performed properly. This technique is a very common one in spreadsheets and it is the basis for all pivot tables. — Cooper, Apr 26 '22 at 14:27
lfunkdata is just a simple way of generating random numbers and id's and random occurences of male and female. [...Array.from(new Array(n).keys()] is a simple way of generating random arrays of sequential numbers of the form [0,1,2,3....] and by using Array.from() the second parameter is a map function which is used to creat multiple random objects for each iteration of the sequential array. [...Array.from(new Array(length).keys())] is an easy way to create a for loop for any length you want. In this case I allowed that length to be (Math.floor(Match.random() * K0) + K1) Ks are chosen. — Cooper, Apr 26 '22 at 14:40

Yuri Khristich · Accepted Answer · 2022-04-27T13:43:17.547

0

Probably something like this:

var jsn = [
    { numbers: '228', id: '4152', gendar: 'female' },
    { numbers: '356', id: '4152', gendar: 'female' },
    { numbers: '978', id: '8479', gendar: 'male' },
    { numbers: '101', id: '8479', gendar: 'male' },
];

var obj = {};
for (let o of jsn)
  try { obj[o.id].numbers = +obj[o.id].numbers + +o.numbers }
  catch(e) { obj[o.id] = o }

var arr = Object.values(obj);
console.log(arr);

Output:

[
  { numbers: 584,  id: '4152', gendar: 'female' },
  { numbers: 1079, id: '8479', gendar: 'male' }
]

Update

Here is the variant of the code that changes 'female' --> 'woman' and calculate an average number for each ID:

var jsn = [
  { numbers: '228', id: '4152', gendar: 'female' },
  { numbers: '356', id: '4152', gendar: 'female' },
  { numbers: '978', id: '8479', gendar: 'male' },
  { numbers: '101', id: '8479', gendar: 'male' },
];

// change 'female' --> 'woman'
// jsn = JSON.parse(JSON.stringify(jsn).replace(/female/g, 'woman'));

// make the object {id: {id, numbers: [array], gendar}, id:{}, ... }
var obj = {};
for (let o of jsn) {
  try {
    try { obj[o.id].numbers.push(o.numbers) }
    catch (e) { obj[o.id].numbers = [o.numbers] }
  }
  catch (e) {
    o.numbers = [o.numbers];
    obj[o.id] = o;
  }
}

// https://stackoverflow.com/a/18234568/14265469
const average = arr => arr.reduce((p, c) => +p + +c, 0) / arr.length;

// replace the arrays with its average number within the object
for (let id in obj) obj[id].numbers = average(obj[id].numbers);

// replace 'female' --> 'woman' in every 'id' of the 'obj'
for (let id in obj) if (obj[id].gendar == 'female') obj[id].gendar = 'woman';

var arr = Object.values(obj);
console.log(arr);

Expected output:

[
  { numbers: 292, id: '4152', gendar: 'woman' },
  { numbers: 539.5, id: '8479', gendar: 'male' }
]

Update 2

Alternatively you can change 'female' to 'woman' with this simply loop:

for (let id in obj) if (obj[id].gendar == 'female') obj[id].gendar = 'woman';

See the updated code.

edited Apr 27 '22 at 13:43

answered Apr 25 '22 at 19:14

Yuri Khristich

13,448
2
8
23

I see you code easy to read and simple but after using to another array don't sum the result is add first number beside second number: I have in first array numbers= 228933 and in second numbers= 2677212. after try this code give me 228933.002677212.00?. my JSON return has = not : – john Apr 25 '22 at 23:23
Probably it's because the value `o.numbers` is a string. It should be converted into number. I used to do it with the unary operator `+`. I just added this trick in my code. Try it. – Yuri Khristich Apr 25 '22 at 23:29
I try to average numbers= 228933 and in second numbers= 2677212 after sum but get error: { obj[o.id].numbers = +obj[o.id].numbers + +o.numbers / obj[o.id].numbers.length} – john Apr 26 '22 at 01:16
Not sure if I understand you right. So, you got the `arr` with two elements and you want to get an average from its numbers? In this case it could be something like this: `var average = (arr[0].numbers + arr[1].numbers) / 2;` Which means `(588+1079)/2 = 833.5` – Yuri Khristich Apr 26 '22 at 07:25
I using `jsn = JSON.parse(JSON.stringify(jsn).replace(/female/g, 'woman'));` in another array called from API but give me error: SyntaxError: Unexpected token B in JSON at position 216 – john Apr 26 '22 at 18:46
I suspect you're getting a string from the AP. Not a really JSON data like in the snippets. In this case you can try to exclude the stringify part. This way: `jsn = JSON.parse(jsn.replace(/female/g, 'woman');` – Yuri Khristich Apr 26 '22 at 22:06
I find another solution work good and now I try your solution is fine. can you tell me about code why your are using Try twice. can you add describe in every line inside try to understand. – john Apr 27 '22 at 17:16
I know one thing inside try `push()` // adds new items to the end of an array – john Apr 27 '22 at 17:20
About `try/catch` pattern. First it tries to add (push) an element to the array `try {arr.push(element)}`. If there is no array it catches the error and creates a new array (with the element inside) `catch(e) {arr = [element]}`. The same pattern for creating/filling object's. It tries to assign some value to some property `try parent.obj.prop = value}`, if there is no such obj the error happens. It catches the error, creates the obj, and assign the value `catch(e) {parent.obj = {}; parent.obj.prop = value}`. If there already is the obj it just changes the existed property. – Yuri Khristich Apr 27 '22 at 18:38

score 0 · Answer 3 · answered Apr 25 '22 at 21:00

0

You can use Array.prototype.reduce() to group by id and then
use Object.values on the result like so:

const arr = [
  {numbers: 228, id: 4152, gendar: 'female'},
  {numbers: 978, id: 8479, gendar: 'male'},
  {numbers: 101, id: 8479, gendar: 'male'}, 
  {numbers: 356, id: 4152, gendar: 'female'}
];


const res = arr.reduce((acc, curr) => {
  if(acc[curr.id]) {
    acc[curr.id].numbers += curr.numbers;
  } else {
    acc[curr.id] = curr
  }

  return acc;
}, {});

console.log(Object.values(res));

answered Apr 25 '22 at 21:00

zb22

3,126
3
19
34

I try your code and work fine to merge any id but after sum "numbers" sum as string '4152.4152' I want to sum it as number and then get average remember the the count of "numbers" it's changed for every call array – john Apr 26 '22 at 10:43

merge array JSON return from API in app script

3 Answers3

Merge Objects

Generate data