Transposing JSON Data in Javascript

Question

I'm attempting to transpose a query result into a matrix. Essentially if I had this result:

Attribute1	Val	Count
Apples	Val 1	100
Bananas	Val 1	200
Apples	Val 2	300
Bananas	Val 2	400

I want to transpose it to this:

Attribute1	Val 1	Val 2
Apples	100	300
Bananas	200	400

However the number of values in the Attribute field & Val field are dynamic. How would I therefore go about transposing this in JS so my output JSON array is a dynamic structure dependent on the query result?

I've got a code snippet below where I've listed out the distinct [Attribute1] & [Val], but how do I build a json array where the definition is dynamic? I've made a stab at it at the end, but I know it's not right yet...

var res = [{
    "Attribute1": 'Apples',
    "Val": 'Val 1',
    "Count": 100
  },
  {
    "Attribute1": 'Bananas',
    "Val": 'Val 1',
    "Count": 200
  },
  {
    "Attribute1": 'Apples',
    "Val": 'Val 2',
    "Count": 300
  },
  {
    "Attribute1": 'Bananas',
    "Val": 'Val 2',
    "Count": 400
  }
]


const y_result = [];
const map = new Map();
for (const item of res) {
  if (!map.has(item.Attribute1)) {
    map.set(item.Attribute1, true); // set any value to Map
    y_result.push({
      Attribute1: item.Attribute1,
    });
  }
}
console.log(y_result)

const x_result = [];
const x_map = new Map();
for (const item of res) {
  if (!x_map.has(item.Val)) {
    x_map.set(item.Val, true); // set any value to Map
    x_result.push({
      Attribute1: item.Val,
    });
  }
}
console.log(x_result)

let tmp
let new_res

console.log(y_result[0].Attribute1)
for (const item of y_result) {
    tmp = { y_result[0].Attribute1 : item.Count };
  new_res.push(tmp)
}
console.log(new_res)

wow, 2 other great answers while I was writing up mine :) upvoting both! :) — Dmitry Kostyuk
– Dmitry Kostyuk, Commented Aug 5, 2021 at 17:55

Mister Jojo · Accepted Answer · 2021-08-05 17:46:39Z

1

this way..

const res = 
  [ { Attribute1: 'Apples',  Val: 'Val 1', Count: 100 } 
  , { Attribute1: 'Bananas', Val: 'Val 1', Count: 200 } 
  , { Attribute1: 'Apples',  Val: 'Val 2', Count: 300 } 
  , { Attribute1: 'Bananas', Val: 'Val 2', Count: 400 } 
  ] 

const y_result = Object.entries(res.reduce((a,{Attribute1,Val,Count})=>
    {
    a[Attribute1]      = a[Attribute1] ?? {}
    a[Attribute1][Val] = Count
    return a
    },{})).map(([Attribute1,vals])=>({Attribute1, ...vals}))
    
console.log( y_result )

.as-console-wrapper { max-height: 100% !important; top: 0 }

edited Aug 5, 2021 at 17:46

answered Aug 5, 2021 at 17:33

Mister Jojo

22.9k6 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmitry Kostyuk Over a year ago

Nice! My approach was to use a global variable, you avoide that by building an object and then mapping its entries into an array, love it.

lejlun · Accepted Answer · 2021-08-05 17:47:38Z

1

My approach, using some destructuring, and Array#forEach.

Go through every item in the array
Check its Attribute1 value and check if its been seen before.
If it has read the Val property add it to the existing object
If not create the object, read the Val property and set it.

Code:

var res = [
    { Attribute1: "Apples", Val: "Val 1", Count: 100  },
    { Attribute1: "Bananas", Val: "Val 1", Count: 200 },
    { Attribute1: "Apples", Val: "Val 2", Count: 300  },
    { Attribute1: "Bananas", Val: "Val 2", Count: 400 },
];

let result = [], seenBefore = [];

res.forEach(({ Attribute1, Count, Val }) => {
    let foundHere = seenBefore.indexOf(Attribute1)
    
    if (foundHere !== -1){
      result[foundHere][Val] = Count;
    }
    else {
      result.push({ Attribute1, [Val]: Count });
      seenBefore.push(Attribute1)
    }
}) 

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0}

edited Aug 5, 2021 at 17:47

answered Aug 5, 2021 at 17:30

lejlun

4,4312 gold badges18 silver badges31 bronze badges

2 Comments

Dmitry Kostyuk Over a year ago

Not bad at all :)

user10963024 Over a year ago

Thank you for your help - also a nice approach.

Dmitry Kostyuk · Accepted Answer · 2021-08-05 17:54:16Z

Is this what you're looking for?

The snippet below does the job in 2 stages: first the value of Val becomes the key and takes the value of Count as its own value, and then it merges all values together. The second step is done with the help of the registry object that allows to add all keys that are not Attribute1 to an already existing entry if it had been pre-recorded there.

const res = [
  {
    Attribute1: 'Apples',
    Val: 'Val 1',
    Count: 100,
  },
  {
    Attribute1: 'Bananas',
    Val: 'Val 1',
    Count: 200,
  },
  {
    Attribute1: 'Apples',
    Val: 'Val 2',
    Count: 300,
  },
  {
    Attribute1: 'Bananas',
    Val: 'Val 2',
    Count: 400,
  },
];

const registry = {};

const result = res
  .map(
    ({ Attribute1, Val, Count }) => ({
      Attribute1,
      [Val]: Count,
    }),
    []
  )
  .reduce((acc, row, i) => {
    const at = row.Attribute1;

    if (!(at in registry)) {
      registry[at] = i;
      acc.push(row);
      return acc;
    }

    Object.entries(row).forEach(([key, value]) => {
      if (key !== 'Attribute1') {
        acc[registry[at]][key] = value;
      }
    });

    return acc;
  }, []);

console.log(result);

Collectives™ on Stack Overflow

Transposing JSON Data in Javascript

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related