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

[There is no such thing as a JSON object.](http://benalman.com/news/2010/03/theres-no-such-thing-as-a-json/) — nicovank, Dec 07 '16 at 22:02
@nicovank -- go here to find out WHAT IS JSON?? http://www.json.org/ — user1789573, Dec 07 '16 at 22:03
@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, Dec 07 '16 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, Dec 07 '16 at 22:06

score 8 · Accepted Answer · edited May 23 '17 at 10:30

8

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 '17 at 10:30

Community

1
1

answered Dec 07 '16 at 22:57

trincot

211,288
25
175
211

thanks much! I had no idea about the JS map functionality w/ the ellipses. – user1789573 Dec 07 '16 at 23:32
can we do the wise versa of the same – Vipin Yadav Sep 23 '19 at 13:50
@Yipin, sure. If you have an issue in doing that... look at the [many Q&A](https://www.google.com/search?q=csv+to+2d+array+javascript+site%3Astackoverflow.com&oq=csv+to+2d+array+javascript+site%3Astackoverflow.com) on the subject, and if that does not cover it, ask a new question. – trincot Sep 23 '19 at 13:59
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' – Googs Mar 24 '20 at 17:51
@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. – trincot Mar 24 '20 at 18:28

score 0 · Answer 2 · answered Dec 08 '16 at 07:55

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);

How to convert array of nested objects to CSV?

2 Answers2

Linked