Transform JSON in CSV format to table

Question

I have already extracted the string of JSON data from a url and stored this in a CSV file.

The problem I am facing now is that I would like to represent this data in a table format so that I can upload this to my DB.

An example of my raw JSON data below:

{"pizza":[{"url":"www.pizza.com/1.json","id":1,"via":{"channel":"web","source":{"from":{"john@xxx.com"},"to":{"mycompany@abc.com"},"rel":null}},"created_at":"2013-07-09T08:38:35Z","updated_at":"20132-08-13T09:33:00Z","type":"incident"}]

An example of my CSV formatted JSON:

http://s18.postimg.org/alwadflsp/my_Code.png

An example of my desired output:

http://s18.postimg.org/4md4niqe1/myoutput.png

Would really appreciate help regarding this.

Thank you

Answer 1

Here is a solution using jq. However this part of your data isn't legal JSON

"source":{"from":{"john@xxx.com"},"to":{"mycompany@abc.com"},

Assuming you remove the surrounding {} from the from and to members

"source":{"from":"john@xxx.com","to":"mycompany@abc.com",

then if filter.jq contains the following filter

[
    .pizza[]
  | {url, id} + (.via | {channel} + (.source | {from, to})) + {created_at, updated_at}
]
| (.[0] | keys_unsorted)
, (.[]  | [.[]])
| @csv  

and data.json contains your sample data then the command

$ jq -M -r -f filter.jq data.json

produces

"url","id","channel","from","to","created_at","updated_at"
"www.pizza.com/1.json",1,"web","john@xxx.com","mycompany@abc.com","2013-07-09T08:38:35Z","20132-08-13T09:33:00Z"

Answer 2

If your final goal is to save your data obtained from JSON in a database.

My approach would be - 1) Extract the relevant info from the JSON using JSONObject. JSONOBJECT

2) Create a bean with all the info.

3) Pass that bean to the Database class to store the respective values.

Answer 3

There are (numerous) small command line utilities written for that task, maybe you find one of those helpful:

• https://github.com/spullara/json2csv (java)
• https://github.com/zeMirco/json2csv (node)
• https://github.com/jehiah/json2csv (go)
• https://github.com/danmandle/JSON2CSV (php)
• https://github.com/vladikk/json2csv (python, blog post)