Unwinding array horizontally

[PawelSliski]

Hello. Is there any possibility to unwind an array horizontally (instead of adding new rows, fields from this array would appear in new columns in the same row). Say i have a json file like this one:

[
  {
    "person": "Test",
    "evaluation": [
      {
        "name": "Name1",
        "score": 2.3
      },
      {
        "name": "Name2",
        "score": 3
      },
      {
        "name": "Name3",
        "score": 1.7
      },
      {
        "name": "Name4",
        "score": 2.3
      }
    ],
    "status": "finished-success",
    "result": 10
  },
  {
    "person": "Test2",
    "evaluation": [
      {
        "name": "Name2",
        "score": 2.3
      },
      {
        "name": "Name5",
        "score": 3
      },
      {
        "name": "Name6",
        "score": 1.7
      },
      {
        "name": "Name7",
        "score": 2.3
      }
    ],
    "status": "finished-success",
    "result": 7
  }
] 

In the array I want to unwind there are tests that each person attended and I'd like the test name to be the key and the score to be the value. So my desired CSV would look like this:

person;result;Test1;Test2;Test3;Test4;Test5;Test6;Test7;status
Test, 10, 2.3, 3, 1.7, 2.3, 0, 0, 0, finished-success
Test2, 7, 0, 2.3, 0, 3, 0, 3, 1.7, 2.3, finished-success

Is there any possibility to achieve that using json2csv?

[juanjoDiaz]

Sure thing.

You want to flatten your object:

const { Parser, transforms: { flatten } } = require('json2csv');

const data = [
  {
    "person": "Test",
    "evaluation": [
      {
        "name": "Name1",
        "score": 2.3
      },
      {
        "name": "Name2",
        "score": 3
      },
      {
        "name": "Name3",
        "score": 1.7
      },
      {
        "name": "Name4",
        "score": 2.3
      }
    ],
    "status": "finished-success",
    "result": 10
  },
  {
    "person": "Test2",
    "evaluation": [
      {
        "name": "Name2",
        "score": 2.3
      },
      {
        "name": "Name5",
        "score": 3
      },
      {
        "name": "Name6",
        "score": 1.7
      },
      {
        "name": "Name7",
        "score": 2.3
      }
    ],
    "status": "finished-success",
    "result": 7
  }
];

const transforms = [flatten({ objects: true, arrays: true })];
const json2csvParser = new Parser({ transforms });
const csv = json2csvParser.parse(data);
console.log(csv);

resulting in:

"person","evaluation.0.name","evaluation.0.score","evaluation.1.name","evaluation.1.score","evaluation.2.name","evaluation.2.score","evaluation.3.name","evaluation.3.score","status","result"
"Test","Name1",2.3,"Name2",3,"Name3",1.7,"Name4",2.3,"finished-success",10
"Test2","Name2",2.3,"Name5",3,"Name6",1.7,"Name7",2.3,"finished-success",7

[PawelSliski]

Thank you for your answer @juanjoDiaz. I've been there, but you see, I need to have "Name" fields in the first row as keys, instead of evaluation.0.name etc. And thus the evaluation.0.score should be under (or refer to) the "Name" from its object in the array or null if test with that name wasn't attended by the person.

[juanjoDiaz]

In that case, I'd use a custom transform to format the object.

const { Parser } = require('json2csv');

const data = [
  {
    "person": "Test",
    "evaluation": [
      {
        "name": "Name1",
        "score": 2.3
      },
      {
        "name": "Name2",
        "score": 3
      },
      {
        "name": "Name3",
        "score": 1.7
      },
      {
        "name": "Name4",
        "score": 2.3
      }
    ],
    "status": "finished-success",
    "result": 10
  },
  {
    "person": "Test2",
    "evaluation": [
      {
        "name": "Name2",
        "score": 2.3
      },
      {
        "name": "Name5",
        "score": 3
      },
      {
        "name": "Name6",
        "score": 1.7
      },
      {
        "name": "Name7",
        "score": 2.3
      }
    ],
    "status": "finished-success",
    "result": 7
  }
];

function transformEvalutation(src) {
  return {
    person: src.person,
    result: src.result,
    status: src.status,
    ...src.evaluation.reduce((acc, {name, score}) => ({ ...acc, [name]: score }), {}),
  };
}

const transforms = [transformEvalutation];
const json2csvParser = new Parser({ transforms });
const csv = json2csvParser.parse(data);
console.log(csv);

resulting in

"person","result","status","Name1","Name2","Name3","Name4","Name5","Name6","Name7"
"Test",10,"finished-success",2.3,3,1.7,2.3,,,
"Test2",7,"finished-success",,2.3,,,3,1.7,2.3

if you want the empty spaces to be defaulted to zero you can modify the transform or use the fields property.

[PawelSliski]

That sets me. Thank you very much!

[wstoettinger]

would be great to have both examples in the docs! took me some time to find them.

[knownasilya]

@wstoettinger feel free to submit a PR! I'm a bit overtaxed with a new baby atm so probably won't get to it for a while.

[wstoettinger]

@knownasilya congrats for your baby! we have recently had our first baby as well! but I might have a look at it