flattening json to csv format

Question

i am trying to convert a json value to a flat csv based on the field that is selected by user . My json looks like

var data = {
"_index": "test",
"_type": "news",
"_source": {
    "partnerName": "propertyFile 9",
    "relatedSources": "null",
    "entityCount": "50",
    "Categories": {
        "Types": {
            "Events": [{
                "count": 1,
                "term": "Time",
                "Time": [{
                    "term": "Dec 9",
                    "Dec_9": [{
                        "count": 1,
                        "term": "2012"
                    }]
                    }]
                }, {
                "count": 4,
                "term": "News",
                "News": [{
                    "term": "Germany",
                    "Germany": [{
                        "count": 1,
                        "term": "Election"
                    }],
                    "currency": "Euro (EUR)"
                }, {
                    "term": "Egypt",
                    "Egypt": [{
                        "count": 1,
                        "term": "Revolution"
                    }]
                    }]
                }]
            }
    }
}};

Ive been able to collect the values of all occurences and store it as a csv, but I want to save the details from the root itself..

If I select Time, the csv output should look like,

"test", "news", "propertyFile 9","null", "50", "Events": "Time", "Dec 9", "2012"

Is it possible to flatten the json.. I will add the json fiddle link to show where Ive reached with this thing.. http://jsfiddle.net/JHCwM/

json is just a javascript data structure in string form. you don't deal with json directly - you deal with native javascript data and work from there. — Marc B, Jul 04 '12 at 16:17
Possible duplicate of [Fastest way to flatten / un-flatten nested JSON objects](https://stackoverflow.com/questions/19098797/fastest-way-to-flatten-un-flatten-nested-json-objects) — DavidTaubmann, Jun 21 '19 at 21:12

score 3 · Answer 1 · answered May 10 '17 at 17:32

Here is an alternative way to flatten an object into key/value pairs, where the key is the complete path of the property.

let data = {
  pc: "Future Crew",
  retro: {
    c64: "Censor Design",
    amiga: "Kefrens"
  }
};

let flatten = (obj, path = []) => {
  return Object.keys(obj).reduce((result, prop) => {
    if (typeof obj[prop] !== "object") {
      result[path.concat(prop).join(".")] = obj[prop];
      return result;
    }
    return Object.assign(result, flatten(obj[prop], path.concat(prop), result));
  }, {});
}

console.log(
  flatten(data)
);

score 2 · Answer 2 · answered Jul 04 '12 at 16:20

2

Your data value is not a JSON (string) - it's an object. There are many ways to 'flatten' this object, may be this little function might be helpful:

var recMap = function(obj) {
  return $.map(obj, function(val) { 
    return typeof val !== 'object' ? val : recMap(val); 
  });
}

And here's how it can be used. )

answered Jul 04 '12 at 16:20

raina77ow

103,633
15
192
229

the jsfiddle example is nt wrking.. and is that jquery? – user1371896 Jul 04 '12 at 16:25
Define 'not working', please. You mean you see nothing in `console`? – raina77ow Jul 04 '12 at 16:26
Yes, `jQuery.map` method is used. – raina77ow Jul 04 '12 at 16:27
Could it be because the console.log function is running in an Iframe? Just a guess – starbeamrainbowlabs Jul 04 '12 at 16:32
Its chrome and alert did wrk.. bt I dont want to print the whole object..I wana make the selection based on the key value that I choose.. like If I select Time.. I wanna display items which lead up to Time and the array that is connected to Time.. – user1371896 Jul 04 '12 at 16:32
Then I suggest using this function as a starting point, taken into account that you need to `prune` its work at some depth (if I got your requirements right). – raina77ow Jul 04 '12 at 16:34
Is it possible to get the key name attached to the values?? like "_index": "test" – user1371896 Jul 04 '12 at 16:39
1

Check the [updated](http://jsfiddle.net/ybXTQ/1/) jsFiddle - is it that what you want? – raina77ow Jul 04 '12 at 16:54
Im looking to add this to an excel file... so the next json will not have the fields in the same order.. thats my concern.. – user1371896 Jul 04 '12 at 17:04

score 1 · Answer 3 · answered Jan 05 '21 at 16:04

1

There is a npm lib just for this with a lot of options: https://mircozeiss.com/json2csv/

# Global install so it can be called from anywhere
$ npm install -g json2csv

## Generate CSV file
$ json2csv -i data.json -o out.csv --flatten-objects

answered Jan 05 '21 at 16:04

lblo

11
1

score 0 · Answer 4 · edited May 23 '17 at 12:02

0

Try looking here:

http://www.zachhunter.com/2011/06/json-to-csv/

and here:

How to convert JSON to CSV format and store in a variable

edited May 23 '17 at 12:02

Community

1
1

answered Jul 04 '12 at 16:17

starbeamrainbowlabs

5,692
8
42
73

this wont wrk when inner array or hash comes.. its works for flat json – user1371896 Jul 04 '12 at 16:20
If you have the JSON in a string, can't you just detect the brackets and replace them with commas? – starbeamrainbowlabs Jul 04 '12 at 16:25
Upon seeing the answer given by @raina77ow, you could loop over your javascript object recursively and append to a string – starbeamrainbowlabs Jul 04 '12 at 16:29

score 0 · Answer 5 · answered May 22 '14 at 12:45

0

Try the following :

http://codebeautify.org/view/jsonviewer

Use Export to CSV button

answered May 22 '14 at 12:45

Mohamed Zaghloul

345
2
9

score 0 · Answer 6 · answered Sep 08 '15 at 11:46

Check this out to flatten the Json

// Convert Nested Json to Flat Json
// Check the final json in firebug console.
var fullData = {"data":[{"Vehicle":"BMW","Date":"30, Jul 2013 09:24 AM","Location":"Hauz Khas, Enclave, New Delhi, Delhi, India","Speed":42,"Children":[{"Vehicle":"BMW","Date":"30, Jul 2013 09:24 AM","Location":"Hauz Khas, Enclave, New Delhi, Delhi, India","Speed":42,"Children":[{"Vehicle":"BMW","Date":"30, Jul 2013 09:24 AM","Location":"Hauz Khas, Enclave, New Delhi, Delhi, India","Speed":42,"Children":[]}]},{"Vehicle":"Honda CBR","Date":"30, Jul 2013 12:00 AM","Location":"Military Road,  West Bengal 734013,  India","Speed":0,"Children":[]}]},{"Vehicle":"Honda CBR","Date":"30, Jul 2013 12:00 AM","Location":"Military Road,  West Bengal 734013,  India","Speed":0,"Children":[]},{"Vehicle":"Supra","Date":"30, Jul 2013 07:53 AM","Location":"Sec-45, St. Angel's School, Gurgaon, Haryana, India","Speed":58,"Children":[]},{"Vehicle":"Land Cruiser","Date":"30, Jul 2013 09:35 AM","Location":"DLF Phase I, Marble Market, Gurgaon, Haryana, India","Speed":83,"Children":[]},{"Vehicle":"Suzuki Swift","Date":"30, Jul 2013 12:02 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0,"Children":[]},{"Vehicle":"Honda Civic","Date":"30, Jul 2013 12:00 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0,"Children":[]},{"Vehicle":"Honda Accord","Date":"30, Jul 2013 11:05 AM","Location":"DLF Phase IV, Super Mart 1, Gurgaon, Haryana, India","Speed":71,"Children":[]}]}
var finalData = [];
loopJson(fullData.data);
function loopJson(data) {
    $.each(data, function(i, e) {
        if (e.Children.length>0) {
            var ccd = e.Children;
            delete e.Children;
            finalData.push(e);
            loopJson(ccd);
        } else {
            delete e.Children;
            finalData.push(e);
        }
    });
}
console.log(finalData);

Here is Js fiddle link http://jsfiddle.net/2nwm43yc/

flattening json to csv format

6 Answers6

Linked