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"