Convert JSON to CSV

This example specializes in converting JSON data into CSV format, allowing users to seamlessly transform structured JSON data into a tabular CSV representation.

Real-life use cases for this library include scenarios where users need to extract data from JSON-based sources such as APIs, log files, or databases, and convert it into a format that is easily readable and compatible with various data analysis tools and systems. By offering a straightforward conversion process, this example enables users to efficiently process and analyze JSON data using popular spreadsheet applications or import it into databases for further analysis.

Input JSON file

[
   {
      "id":"358464",
      "t":"MSFT",
      "e":"NASDAQ",
      "l":"31.67",
      "l_cur":"31.67",
      "s":"2",
      "ltt":"4:00PM EDT",
      "lt":"Aug 1, 4:00PM EDT",
      "c":"-0.17",
      "cp":"-0.53",
      "ccol":"chr",
      "el":"31.69",
      "el_cur":"31.69",
      "elt":"Aug 1, 7:54PM EDT",
      "ec":"+0.02",
      "ecp":"0.06",
      "eccol":"chg",
      "div":"0.23",
      "yld":"2.90"
   },
   {
      "id":"419344",
      "t":"ORCL",
      "e":"NYSE",
      "l":"32.75",
      "l_cur":"32.75",
      "s":"2",
      "ltt":"4:00PM EDT",
      "lt":"Aug 1, 4:00PM EDT",
      "c":"+0.40",
      "cp":"1.24",
      "ccol":"chg",
      "el":"32.70",
      "el_cur":"32.70",
      "elt":"Aug 1, 7:15PM EDT",
      "ec":"-0.05",
      "ecp":"-0.15",
      "eccol":"chr",
      "div":"",
      "yld":"1.47"
   },
   {
      "id":"4112",
      "t":"ADBE",
      "e":"NASDAQ",
      "l":"47.70",
      "l_cur":"47.70",
      "s":"0",
      "ltt":"4:00PM EDT",
      "lt":"Aug 1, 4:00PM EDT",
      "c":"+0.42",
      "cp":"0.89",
      "ccol":"chg"
   }
]

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.BufferedReader;
import java.io.File;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.net.URL;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.csv.CSVWriter;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.json.JsonReader;
import com.northconcepts.datapipeline.json.SimpleJsonReader;

public class ConvertJsonToCsv {

/*   Consumes the following JSON (line breaks added for clarity).  Yes, it starts with a new line an two slashes.    

// [
   {
      "id":"358464",
      "t":"MSFT",
      "e":"NASDAQ",
      "l":"31.67",
      "l_cur":"31.67",
      "s":"2",
      "ltt":"4:00PM EDT",
      "lt":"Aug 1, 4:00PM EDT",
      "c":"-0.17",
      "cp":"-0.53",
      "ccol":"chr",
      "el":"31.69",
      "el_cur":"31.69",
      "elt":"Aug 1, 7:54PM EDT",
      "ec":"+0.02",
      "ecp":"0.06",
      "eccol":"chg",
      "div":"0.23",
      "yld":"2.90"
   },
   {
      "id":"419344",
      "t":"ORCL",
      "e":"NYSE",
      "l":"32.75",
      "l_cur":"32.75",
      "s":"2",
      "ltt":"4:00PM EDT",
      "lt":"Aug 1, 4:00PM EDT",
      "c":"+0.40",
      "cp":"1.24",
      "ccol":"chg",
      "el":"32.70",
      "el_cur":"32.70",
      "elt":"Aug 1, 7:15PM EDT",
      "ec":"-0.05",
      "ecp":"-0.15",
      "eccol":"chr",
      "div":"",
      "yld":"1.47"
   },
   {
      "id":"4112",
      "t":"ADBE",
      "e":"NASDAQ",
      "l":"47.70",
      "l_cur":"47.70",
      "s":"0",
      "ltt":"4:00PM EDT",
      "lt":"Aug 1, 4:00PM EDT",
      "c":"+0.42",
      "cp":"0.89",
      "ccol":"chg"
   }
]

*/
    
    public static void main(String[] args) throws Throwable {

    	DataReader reader = new JsonReader(new File("example/data/input/finance.json"))
    	    .addField("symbol", "//array/object/t")
    	    .addField("exchange", "//array/object/e")
    	    .addField("price", "//array/object/l")
    	    .addField("change", "//array/object/c")
    	    .addRecordBreak("//array/object");

		DataWriter writer = new CSVWriter(new OutputStreamWriter(System.out));

        Job.run(reader, writer);
    }

}

Code Walkthrough

1. A new JsonReader is created to specify the input file finance.json .
2. The fields to be read from the JSON file are declared via the addField() method. 
3. Since the JSON file is an array of objects, to access data in each field we must first traverse the array: addField("exchange", "//array/object/e"). This method traverses through the objects and searches for a field with the name e, and returns the value to the field exchange.
4. The JsonReader.addRecordBreak method is invoked to demarcate records.
5. A CSVWriter is created to write in the CSV format. In the given example, standard console output is used as an argument.
6. Data is transferred from the JSON stream to the CSVWriter via Job.run() method.

JsonReader

JsonReader is an input reader that can read a JSON stream. It can be created using a Reader object or using a File corresponding to the JSON stream. The main method in this class is the JsonReader.addField method which is used to add the fields to be read from the JSON stream. This method uses the field name and a subset of the XPath 1.0 location paths notation to identify field values. You can selectively add whichever fields you wish to be read from the input JSON stream. Another important method is the JsonReader.addRecordBreak, which tells the reader to return a new record using whatever fields have been assigned. This method is basically used to demarcate records.

Console Output

symbol,exchange,price,change
MSFT,NASDAQ,31.67,-0.17
ORCL,NYSE,32.75,+0.40
ADBE,NASDAQ,47.70,+0.42