Convert JSON to CSV and have the Headers be concatenations of the parents

Question

I want to convert JSON responses to CSV format. Since the JSON response may differ, I can't manually create my headers.

I looked around and found a programmatic solution on stack overflow. But this solution uses the keys of the JSON as headers. My test data is sensitive so I'll post a similar JSON response that gives a better idea of what I'm trying to do.

{
    "response":[{
    "_id": "5cfe7d3c6deeeef08ce0444b",
    "name": "Debra Milligain",
    "phone": "+1 (906) 432-2182",
    "address": "676 Merit Court, Steinhatchee, Oregon, 5491",
    "tags": [
        "consequat",
        "reprehenderit",
        "amet"
    ],
    "Work": {
        "skills": [{
                "id": 0,
                "name": "Programming"
            },
            {
                "id": 1,
                "name": "Business"
            }
        ]
    },
    "friends": [{
            "id": 0,
            "name": "Stafford Hernandez"
        },
        {
            "id": 1,
            "name": "Colleen Christensen"
        },
        {
            "id": 2,
            "name": "Barker Keith"
        }
    ],
    "greeting": [],
    "favoriteFruit": "banana"
    }]}

Solution found from hereChristian Landgren 's Solution

function json2csv(json) {
    const items = json.response;
    const replacer = (key, value) => value === null ? '' : value // specify how you want to handle null values here
    const header = Object.keys(items[0])
    let csv = items.map(row => header.map(fieldName => JSON.stringify(row[fieldName], replacer)).join(','))
    csv.unshift(header.join(','))
    csv = csv.join('\r\n')
    //console.log(csv)
}

This is the actual response:

_id,name,phone,address,tags,Work,friends,greeting,favoriteFruit
"5cfe7d3c6deeeef08ce0444b","Debra Milligain","+1 (906) 432-2182","676 Merit Court, Steinhatchee, Oregon, 5491",["consequat","reprehenderit","amet"],{"skills":[{"id":0,"name":"Programming"},{"id":1,"name":"Business"}]},[{"id":0,"name":"Stafford Hernandez"},{"id":1,"name":"Colleen Christensen"},{"id":2,"name":"Barker Keith"}],[],"banana"

The output as the headers but doesn't include the sub headers. The actual output should be this more or less.

"_id","name","phone","address","tags","Work__skills__id","Work__skills__name","friends__id","friends__name","favoriteFruit"
"5cfe7d3c6deeeef08ce0444b","Debra Milligain","+1 (906) 432-2182","676 Merit Court, Steinhatchee, Oregon, 5491","consequat","0","Programming","0","Stafford Hernandez","banana"
"","","","","reprehenderit","1","Business","1","Colleen Christensen",""
"","","","","amet","","","2","Barker Keith",""

Jan · Accepted Answer · 2019-07-01T13:37:09.243

What about more object oriented approach with recursion hidden in constructor ?

Made iterator - you can iterate 4 directions (next, prev, node, parent) and also 2 demo traverse of your sample data (log is too long for single output).

And in case you get private Current JNode out of iterator, you can list that container structure in any debugger where you can inspect variables (example VSC & node.js).

Separate IIFE code is in 3rd box.

DepthFirst demo:

'use strict';
function test(json) {
var it = new JIterator(json);
var i = 0;
var htmlTable = "<table border=1><tr><th>#</th><th>Level</th><th>Path</th><th>Key</th><th>Value or Type</th></tr>";
do {
  htmlTable += "<tr><td>";
  htmlTable += [i++, it.Level, it.Path().join('.'), it.KeyDots(), (it.Value() instanceof Object) ? (it.Value() instanceof Array ? "[]" : "{}") : it.Value()].join("</td><td>");
  htmlTable += "</td></tr>";
} while (it.DepthFirst());
htmlTable += "</table>";
document.body.innerHTML = htmlTable;
}
var JNode = (function (jsNode) {

    function JNode(_parent, _pred, _key, _value) {
        this.parent = _parent;
        this.pred = _pred;
        this.node = null;
        this.next = null;
        this.key = _key;
        this.value = _value;
    }

    return JNode;
})();

var JIterator = (function (json) {
    var root, current, maxLevel = -1;

    function JIterator(json, parent) {
        if (parent === undefined) parent = null;
        var pred = null, localCurrent;
        for (var child in json) {
            var obj = json[child] instanceof Object;
            if(json instanceof Array) child = parseInt(child); // non-associative array
            if (!root) root = localCurrent = new JNode(parent, null, child, json[child]);
            else {
                localCurrent = new JNode(parent, pred, child, obj ? ((json[child] instanceof Array) ? [] : {}) : json[child]);
            }
            if (pred) pred.next = localCurrent;
            if (parent && parent.node == null) parent.node = localCurrent;
            pred = localCurrent;
            if (obj) {
                var memPred = pred;
                JIterator(json[child], pred);
                pred = memPred;
            }
        }
        if (this) {
            current = root;
            this.Level = 0;
        }
    }

    JIterator.prototype.Current = function () { return current; }
    JIterator.prototype.Parent = function () {
        var retVal = current.parent;
        if (retVal == null) return false;
        this.Level--;
        return current = retVal;
    }
    JIterator.prototype.Pred = function () {
        var retVal = current.pred;
        if (retVal == null) return false;
        return current = retVal;
    }
    JIterator.prototype.Node = function () {
        var retVal = current.node;
        if (retVal == null) return false;
        this.Level++;
        return current = retVal;
    }
    JIterator.prototype.Next = function () {
        var retVal = current.next;
        if (retVal == null) return false;
        return current = retVal;
    }
    JIterator.prototype.Key = function () { return current.key; }
    JIterator.prototype.KeyDots = function () { return (typeof(current.key) == "number")?"":(current.key+':'); }
    JIterator.prototype.Value = function () { return current.value; }
    JIterator.prototype.Reset = function () {
        current = root;
        this.Level = 0;
    }
    JIterator.prototype.RawPath = function () {
        var steps = [], level = current;
        do {
            if (level != null && level.value instanceof Object) {
                steps.push(level.key + (level.value instanceof Array ? "[]" : "{}"));
            } else {
                if (level != null) steps.push(level.key);
                else break;
            }
            level = level.parent;
        } while (level != null);
        var retVal = "";
        retVal = steps.reverse();
        return retVal;
    }
    JIterator.prototype.Path = function () {
        var steps = [], level = current;
        do {
            if (level != null && level.value instanceof Object) {
                var size = 0;
                var items = level.node;
                if(typeof(level.key) == "number") steps.push('[' + level.key + ']');
                else {
                    while(items) {
                        size++;
                        items = items.next;
                    }
                    var type = (level.value instanceof Array ? "[]" : "{}");
                    var prev = steps[steps.length-1];
                    if(prev && prev[0] == '[') {
                        var last = prev.length-1;
                        if(prev[last] == ']') {
                            last--;
                            if(!isNaN(prev.substr(1, last))) {
                                steps.pop();
                                size += '.' + prev.substr(1, last);
                            }
                        }
                    }
                    steps.push(level.key + type[0] + size + type[1]);
                }
            } else {
                if (level != null) {
                    if(typeof(level.key) == "number") steps.push('[' + level.key + ']');
                    else steps.push(level.key);
                }
                else break;
            }
            level = level.parent;
        } while (level != null);
        var retVal = "";
        retVal = steps.reverse();
        return retVal;
    }
    JIterator.prototype.DepthFirst = function () {
        if (current == null) return 0; // exit sign
        if (current.node != null) {
            current = current.node;
            this.Level++;
            if (maxLevel < this.Level) maxLevel = this.Level;
            return 1; // moved down
        } else if (current.next != null) {
            current = current.next;
            return 2; // moved right
        } else {
            while (current != null) {
                if (current.next != null) {
                    current = current.next;
                    return 3; // returned up & moved next
                }
                this.Level--;
                current = current.parent;
            }
        }
        return 0; // exit sign
    }
    JIterator.prototype.BreadthFirst = function () {
        if (current == null) return 0; // exit sign
        if (current.next) {
            current = current.next;
            return 1; // moved right
        } else if (current.parent) {
            var level = this.Level, point = current;
            while (this.DepthFirst() && level != this.Level);
            if (current) return 2; // returned up & moved next
            do {
                this.Reset();
                level++;
                while (this.DepthFirst() && level != this.Level);
                if (current) return 3; // returned up & moved next
            } while (maxLevel >= level);
            return current != null ? 3 : 0;
        } else if (current.node) {
            current = current.node;
            return 3;
        } else if (current.pred) {
            while (current.pred) current = current.pred;
            while (current && !current.node) current = current.next;
            if (!current) return null;
            else return this.DepthFirst();
        }
    }

    return JIterator;
})();
var json = {
    "_id": "5cfe7d3c6deeeef08ce0444b",
    "name": "Debra Milligain",
    "phone": "+1 (906) 432-2182",
    "address": "676 Merit Court, Steinhatchee, Oregon, 5491",
    "tags": [
        "consequat",
        "reprehenderit",
        "amet"
    ],
    "Work": {
        "skills": [
            {
                "id": 0,
                "name": "Programming"
            },
            {
                "id": 1,
                "name": "Business"
            }
        ]
    },
    "friends": [
        {
            "id": 0,
            "name": "Stafford Hernandez"
        },
        {
            "id": 1,
            "name": "Colleen Christensen"
        },
        {
            "id": 2,
            "name": "Barker Keith"
        }
    ],
    "greeting": [],
    "favoriteFruit": "banana"
}
test(json);

table {
  border-spacing: 0px; /* small tricks 2 make rounded table simply or */
}
th {
  text-align:left; /* centered looks ugly */
}
td.empty {
  background-color:lightgray; /* mark null cells */
}

BreadthFirst demo:

'use strict';
function test(json) {
var it = new JIterator(json);
var i = 0;
var htmlTable = "<table border=1><tr><th>#</th><th>Level</th><th>Path</th><th>Key</th><th>Value or Type</th></tr>";
do {
  htmlTable += "<tr><td>";
  htmlTable += [i++, it.Level, it.Path().join('.'), it.KeyDots(), (it.Value() instanceof Object) ? (it.Value() instanceof Array ? "[]" : "{}") : it.Value()].join("</td><td>");
  htmlTable += "</td></tr>";
} while (it.BreadthFirst())
htmlTable += "</table>";
document.body.innerHTML = htmlTable;
}
var JNode = (function (jsNode) {

    function JNode(_parent, _pred, _key, _value) {
        this.parent = _parent;
        this.pred = _pred;
        this.node = null;
        this.next = null;
        this.key = _key;
        this.value = _value;
    }

    return JNode;
})();

var JIterator = (function (json) {
    var root, current, maxLevel = -1;

    function JIterator(json, parent) {
        if (parent === undefined) parent = null;
        var pred = null, localCurrent;
        for (var child in json) {
            var obj = json[child] instanceof Object;
            if(json instanceof Array) child = parseInt(child); // non-associative array
            if (!root) root = localCurrent = new JNode(parent, null, child, json[child]);
            else {
                localCurrent = new JNode(parent, pred, child, obj ? ((json[child] instanceof Array) ? [] : {}) : json[child]);
            }
            if (pred) pred.next = localCurrent;
            if (parent && parent.node == null) parent.node = localCurrent;
            pred = localCurrent;
            if (obj) {
                var memPred = pred;
                JIterator(json[child], pred);
                pred = memPred;
            }
        }
        if (this) {
            current = root;
            this.Level = 0;
        }
    }

    JIterator.prototype.Current = function () { return current; }
    JIterator.prototype.Parent = function () {
        var retVal = current.parent;
        if (retVal == null) return false;
        this.Level--;
        return current = retVal;
    }
    JIterator.prototype.Pred = function () {
        var retVal = current.pred;
        if (retVal == null) return false;
        return current = retVal;
    }
    JIterator.prototype.Node = function () {
        var retVal = current.node;
        if (retVal == null) return false;
        this.Level++;
        return current = retVal;
    }
    JIterator.prototype.Next = function () {
        var retVal = current.next;
        if (retVal == null) return false;
        return current = retVal;
    }
    JIterator.prototype.Key = function () { return current.key; }
    JIterator.prototype.KeyDots = function () { return (typeof(current.key) == "number")?"":(current.key+':'); }
    JIterator.prototype.Value = function () { return current.value; }
    JIterator.prototype.Reset = function () {
        current = root;
        this.Level = 0;
    }
    JIterator.prototype.RawPath = function () {
        var steps = [], level = current;
        do {
            if (level != null && level.value instanceof Object) {
                steps.push(level.key + (level.value instanceof Array ? "[]" : "{}"));
            } else {
                if (level != null) steps.push(level.key);
                else break;
            }
            level = level.parent;
        } while (level != null);
        var retVal = "";
        retVal = steps.reverse();
        return retVal;
    }
    JIterator.prototype.Path = function () {
        var steps = [], level = current;
        do {
            if (level != null && level.value instanceof Object) {
                var size = 0;
                var items = level.node;
                if(typeof(level.key) == "number") steps.push('[' + level.key + ']');
                else {
                    while(items) {
                        size++;
                        items = items.next;
                    }
                    var type = (level.value instanceof Array ? "[]" : "{}");
                    var prev = steps[steps.length-1];
                    if(prev && prev[0] == '[') {
                        var last = prev.length-1;
                        if(prev[last] == ']') {
                            last--;
                            if(!isNaN(prev.substr(1, last))) {
                                steps.pop();
                                size += '.' + prev.substr(1, last);
                            }
                        }
                    }
                    steps.push(level.key + type[0] + size + type[1]);
                }
            } else {
                if (level != null) {
                    if(typeof(level.key) == "number") steps.push('[' + level.key + ']');
                    else steps.push(level.key);
                }
                else break;
            }
            level = level.parent;
        } while (level != null);
        var retVal = "";
        retVal = steps.reverse();
        return retVal;
    }
    JIterator.prototype.DepthFirst = function () {
        if (current == null) return 0; // exit sign
        if (current.node != null) {
            current = current.node;
            this.Level++;
            if (maxLevel < this.Level) maxLevel = this.Level;
            return 1; // moved down
        } else if (current.next != null) {
            current = current.next;
            return 2; // moved right
        } else {
            while (current != null) {
                if (current.next != null) {
                    current = current.next;
                    return 3; // returned up & moved next
                }
                this.Level--;
                current = current.parent;
            }
        }
        return 0; // exit sign
    }
    JIterator.prototype.BreadthFirst = function () {
        if (current == null) return 0; // exit sign
        if (current.next) {
            current = current.next;
            return 1; // moved right
        } else if (current.parent) {
            var level = this.Level, point = current;
            while (this.DepthFirst() && level != this.Level);
            if (current) return 2; // returned up & moved next
            do {
                this.Reset();
                level++;
                while (this.DepthFirst() && level != this.Level);
                if (current) return 3; // returned up & moved next
            } while (maxLevel >= level);
            return current != null ? 3 : 0;
        } else if (current.node) {
            current = current.node;
            return 3;
        } else if (current.pred) {
            while (current.pred) current = current.pred;
            while (current && !current.node) current = current.next;
            if (!current) return null;
            else return this.DepthFirst();
        }
    }

    return JIterator;
})();
var json = {
    "_id": "5cfe7d3c6deeeef08ce0444b",
    "name": "Debra Milligain",
    "phone": "+1 (906) 432-2182",
    "address": "676 Merit Court, Steinhatchee, Oregon, 5491",
    "tags": [
        "consequat",
        "reprehenderit",
        "amet"
    ],
    "Work": {
        "skills": [
            {
                "id": 0,
                "name": "Programming"
            },
            {
                "id": 1,
                "name": "Business"
            }
        ]
    },
    "friends": [
        {
            "id": 0,
            "name": "Stafford Hernandez"
        },
        {
            "id": 1,
            "name": "Colleen Christensen"
        },
        {
            "id": 2,
            "name": "Barker Keith"
        }
    ],
    "greeting": [],
    "favoriteFruit": "banana"
}
test(json);

table {
  border-spacing: 0px; /* small tricks 2 make rounded table simply or */
}
th {
  text-align:left; /* centered looks ugly */
}
td.empty {
  background-color:lightgray; /* mark null cells */
}

My JScript iterators moved to GitHub

Also 2 path display method variants included. Public member variables found here https://www.roelvanlisdonk.nl/2014/05/14/javascript-private-public-shared-and-instance-variables/ — Jan, Jun 18 '19 at 15:25

score 0 · Answer 2 · answered Jun 12 '19 at 14:53

I ended up using my own solution and just recursively going down the JSON object. I made sure to keep track of key-value pairs that were at the root of the object vs those that weren't (since I would need to append their parent names to them). Furthermore, I used a hash table to make sure i don't have duplicate headers by making them keys and made their values just be actual values of that header append as a string separated by a , to later be parsed. Here's the code, I decided to leave the console logs just for others to debug and understand the process.

var arrayOfHeaders = {};
var headerDirectory = "";
var rootLevel = true;
var temp = ""
function traverseJSON(obj){
    for (var o in obj) {
        if (typeof obj[o] == "object") {
            //console.log("Before traversal ", o)
            //console.log("Traversing the object: ", obj[o])
            if(!isNaN(o)){
            //console.log("Current position is a number ", o)
            }else{
                //console.log("Adding to directory... " , o)
                headerDirectory += (headerDirectory == "") ? o : "_" + o;
            }
            rootLevel = false;
            traverseJSON(obj[o]);
            rootLevel = true;
            temp = headerDirectory;
            headerDirectory = "";
        } else {
            if (rootLevel) {
                headerDirectory = "";
                //console.log("Has value and is root ", o)
                arrayOfHeaders[headerDirectory];
            }
            else {
                //console.log("Has value and is not root ", o)
                //console.log("Current Header Directory " + headerDirectory)
                //console.log("Saved temp : ", temp)
                if(isNaN(o)){ 
                    if(headerDirectory == "") headerDirectory = temp; 
                    //arrayOfHeaders.push(headerDirectory + "_" + o)
                    arrayOfHeaders[headerDirectory + "_" + o] += ",\"" + obj[o] + "\"";
                }              
            }
        }
    }
    // console.log("Array of Headers : ", arrayOfHeaders)
}

I used the same sample data provided in the question and the here's the contents of arrayOfHeaders after running the method.

Array of Headers :  { 'Work_skills_id-skill': 'undefined,"0","Business"',
  'Work_skills_name-skill': 'undefined,"Programming"',
  'friends_id-friends': 'undefined,"0","1","2"',
  'friends_name-friends':'undefined,"Stafford Hernandez","Colleen Christensen","Barker Keith"' }

Hopefully this helps others with a similar problem.

Done bugfixing & solution in your older question here (https://stackoverflow.com/questions/56567693/json-to-csv-conversion-javascript-how-to-properly-format-csv-conversion) — Jan, Jun 18 '19 at 12:25

Convert JSON to CSV and have the Headers be concatenations of the parents

2 Answers2

Linked