How to convert array of nested objects to CSV?

Question

I have an array with nested objects, such as this one:

[
    {"name": "1", "children": [{"name": "1.1", "children":"1.2"}]},
    {"id": "2", "thing": [{"name": "2.1", "children":"2.2"}]},
    {"name": "3", "stuff": [{"name": "3.1", "children":"3.2"}]},
]

The objects can contain values of different types, including other, nested objects.

I want to convert this array to CSV format.

I've tried to iterate with for .. in loops, regular nested for loops, .map() and recursion. I think recursion might be the only way to solve this particular problem, though. For the CSV field names I want to use the sequence of keys that lead to the value.

For the given example, the CSV result I'm looking for is:

name, children.name, children.children,id, thing.name, thing.children,  stuff.name, stuff.children
1, 1.1, 1.2,
,,,2,2.1,2.2
3,,,,3,3.1,3.2

@zerkms -- exactly what i'm trying to figure out. The issue here is really the difference of multiple dimensions within an array and a much greater depth of nested objects within the JSON, possibly. — user1789573
– user1789573, Commented Dec 7, 2016 at 22:04
@user1789573 well, ask whoever assigned this task to you on how the nested structures should be represented. It's not a technological problem, but a business one. If it was me who makes a final decision on this business logic problem I'd say "It makes very little sense, provide data in a more structured form". — zerkms
– zerkms, Commented Dec 7, 2016 at 22:06

Community · Accepted Answer · 2017-05-23 10:30:34Z

17

You could use this ES6 function to create the 2D array you are looking for, which you can then easily transform to CSV:

function pivot(arr) {
    var mp = new Map();
    
    function setValue(a, path, val) {
        if (Object(val) !== val) { // primitive value
            var pathStr = path.join('.');
            var i = (mp.has(pathStr) ? mp : mp.set(pathStr, mp.size)).get(pathStr);
            a[i] = val;
        } else {
            for (var key in val) {
                setValue(a, key == '0' ? path : path.concat(key), val[key]);
            }
        }
        return a;
    }
    
    var result = arr.map( obj => setValue([], [], obj) );
    return [[...mp.keys()], ...result];
}

function toCsv(arr) {
    return arr.map( row => 
        row.map ( val => isNaN(val) ? JSON.stringify(val) : +val ).join(',')
    ).join('\n');
}

// Sample data
var arr = [
    {"name": "1", "children": [{"name": "1.1", "children":"1.2"}]},
    {"id": "2", "thing": [{"name": "2.1", "children":"2.2"}]},
    {"name": "3", "stuff": [{"name": "3.1", "children":"3.2"}]},
];

// Conversion to 2D array and then to CSV:
console.log(toCsv(pivot(arr)));

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

For other ways to convert a 2D array to CSV, see this Q&A.

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Dec 7, 2016 at 22:57

trincot

357k38 gold badges282 silver badges338 bronze badges

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

5 Comments

user1789573 Over a year ago

thanks much! I had no idea about the JS map functionality w/ the ellipses.

Vipin Yadav Over a year ago

can we do the wise versa of the same

trincot Over a year ago

@Yipin, sure. If you have an issue in doing that... look at the many Q&A on the subject, and if that does not cover it, ask a new question.

Googs Over a year ago

please note - Date objects will not be treated correctly, and will fail 'Object(val) !== val' therefore should also add something along the lines of '|| val instanceof Date'

trincot Over a year ago

@Googs, this function is essentially for JSON compatible objects. Any object that does not expose the enumerable properties you need will need translation. If dates should be translated to strings, you need to do this yourself to your desired format.

Nina Scholz · Accepted Answer · 2016-12-08 07:55:28Z

This is an ES5 proposal.

You could iterate the array and collect the keys in a hash table and store the data later with the gaps.

var data = [{ name: "1", children: [{ name: "1.1", children: "1.2" }] }, { id: "2", thing: [{ name: "2.1", children: "2.2" }] }, { name: "3", stuff: [{ name: "3.1", children: "3.2" }] }],
    csv = function (array) {
        var cols = [],
            collection = Object.create(null),
            i = -1,
            toCSV = function (v) { return isNaN(v)? JSON.stringify(v): v; },
            csv;

        array.forEach(function iter(path) {
            return function (o) {
                path.length || i++;
                Object.keys(o).forEach(function (k) {
                    if (Array.isArray(o[k])) {
                        o[k].forEach(iter(path.concat(k)));
                        return;
                    }
                    var key = path.concat(k).join('.');
                    if (!collection[key]) {
                        cols.push(key);
                        collection[key] = [];
                    }
                    collection[key][i] = o[k];
                });
            };
        }([]));

        csv = cols.map(toCSV).join() + '\n';
        for (i = 0; i < array.length; i++) {
            csv += cols.map(function (k) { return toCSV(collection[k][i]); }).join() + '\n';
        }
        return csv;
    }(data);

console.log(csv);

Collectives™ on Stack Overflow

How to convert array of nested objects to CSV?

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related