Introduction:-

We can use the following bash program to convert a JOSN array input to CSV format. This program can handle both flat and deeply nested json files. One use case would be to convert mongo DB json array output to CSV format.

Prerequisites:-

This program uses a command line utility named jq underneath which helps in parsing and manipulating JSON documents. we can download & install the jq utility for MacOS with the following command.

brew install jq

Usage:-

Copy the program to a file say jsonToCsv.sh and give executable permissions to the file using the below command.

copy the json input(mongo array output) to file and the file name is to be supplied to the script as argument.

Invoke the script and supply the json file name as argument. Do note that file name argument is mandatory.

chmod 755 jsonToCsv.sh # giving executable permissions to the script. ./jsonToCsv.sh input.json # script invocation

output:-

A new directory named working_dir_* will be created with the following two files.

flat_input # flattened version of the given input json file

output.csv # csv file

Program:-

#!/bin/bash


function json_to_csv() {

    cat input | jq '.data[] | [paths(scalars) as $path | {"key": $path | join("."), "value": getpath($path)}] | from_entries' >> flat.json
    sed -i -n 's/^}/},/g' flat.json
    sed -i -n '$d' flat.json
    echo "}" >> flat.json
    touch final_input
    echo "[" >> final_input
    cat flat.json >> final_input
    echo "]" >> final_input
    cat final_input | jq -r '(map(keys) | add | unique) as $cols |      map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' >> output.csv

}


function input_file_prep() {

    echo "{" > input
    echo "\"data\": " >> input
    cat ${file_name} | grep -v "\/\*" >> input
    echo "}" >> input
    sed -i -n 's/ISODate(//g' input
    sed -i -n 's/)//g' input
    rm -r *-n
}


file_name=${1}
mkdir working_dir_$(date +%Y%m%d%H%M%S)
cd working_dir*
cp ../${file_name} .
input_file_prep
json_to_csv
rm -rf final_input flat.json-n input
cd ../
echo "Done"