How to convert JSON to CSV using Linux / Unix shell

Situatie

What are JSON and CSV formats?

JSON is an acronym for JavaScript Object Notation. Often used in web apps, but it is not limited to JavaScript. One can use any programming language such as Perl, Python or CLI tools such as jq. CSV is an acronym for Comma-separated values. It is a text file you can use in spreadsheet apps, programming languages and many other apps.

Example of JSON format

Here the outputs of the df command in JSON format:

[
  {
    "fs": "/dev/mapper/vgubuntu-root",
    "type": "ext4",
    "size": "915G",
    "used": "135G",
    "avail": "734G",
    "usedpercentage": "16%",
    "mounted": "/"
  },
  {
    "fs": "/dev/nvme0n1p2",
    "type": "ext4",
    "size": "1.4G",
    "used": "378M",
    "avail": "939M",
    "usedpercentage": "29%",
    "mounted": "/boot"
  },
  {
    "fs": "/dev/nvme0n1p1",
    "type": "vfat",
    "size": "511M",
    "used": "30M",
    "avail": "482M",
    "usedpercentage": "6%",
    "mounted": "/boot/efi"
  }
]

Example of CSV format

The JSON outputs in CSV format (all values are separated by a comma , value):

/dev/mapper/vgubuntu-root,ext4,915G,135G,734G,16%,/
/dev/nvme0n1p2,ext4,1.4G,378M,939M,29%,/boot
/dev/nvme0n1p1,vfat,511M,30M,482M,6%,/boot/efi

Converting JSON to CSV using Linux / Unix shell

So how do you convert such input? The answer is to use jq or dasel command-line utilities.

Installing jq

Since jq is available in most common repos, I will use that one for my needs. Here is how to install jq on a Debian or Ubuntu Linux using the apt command/apt-get command:
$ sudo apt install jq
RHEL/Fedora/CentOS/Alma/Rocky Linux user try the dnf command:
$ sudo dnf install jq
Alpine Linux user try the apk command:
$ sudo apk add jq
SUSE or OpenSUSE Linux user try the zypper command:
$ sudo zypper in jq
macOS / OS X user install homebrew and then use the brew command:
$ brew install jq

Example – convert JSON to CSV under Linux

To generate CSV file with , as separator use the following syntax:
$ cat df.json | jq '.[]| join(",")'
The join(",") joins the array of elements given as input, using the argument , as separator:

"/dev/mapper/vgubuntu-root,ext4,915G,135G,734G,16%,/"
"/dev/nvme0n1p2,ext4,1.4G,378M,939M,29%,/boot"
"/dev/nvme0n1p1,vfat,511M,30M,482M,6%,/boot/efi"

Pass the -r raw option to get rid of double quotes:
$ cat df.json | jq -r '.[]| join(",")'

How do I convert a JSON file to CSV?

Let us consider the following file displayed using the bat/cat command$ cat bingbot.json $ batcat bingbot.json
Sample config:

{
  "creationTime": "2021-11-10T10:00:00.121331",
  "prefixes": [
    {"ipv4Prefix": "157.55.39.0/24"},
    {"ipv4Prefix": "207.46.13.0/24"},
    {"ipv4Prefix": "40.77.167.0/24"},
    {"ipv4Prefix": "13.66.139.0/24"},
    {"ipv4Prefix": "13.66.144.0/24"},
    {"ipv4Prefix": "52.167.144.0/24"},
    {"ipv4Prefix": "13.67.10.16/28"},
    {"ipv4Prefix": "13.69.66.240/28"},
    {"ipv4Prefix": "13.71.172.224/28"},
    {"ipv4Prefix": "139.217.52.0/28"},
    {"ipv4Prefix": "191.233.204.224/28"},
    {"ipv4Prefix": "20.36.108.32/28"},
    {"ipv4Prefix": "20.43.120.16/28"},
    {"ipv4Prefix": "40.79.131.208/28"},
    {"ipv4Prefix": "40.79.186.176/28"},
    {"ipv4Prefix": "52.231.148.0/28"},
    {"ipv4Prefix": "51.8.235.176/28"},
    {"ipv4Prefix": "51.105.67.0/28"}
  ]
}

The final goal is to produce .CSV file as follows: for my WAF:

157.55.39.0/24,BingBot
207.46.13.0/24,BingBot
40.77.167.0/24,BingBot
13.66.139.0/24,BingBot
13.66.144.0/24,BingBot
52.167.144.0/24,BingBot
13.67.10.16/28,BingBot
13.69.66.240/28,BingBot
13.71.172.224/28,BingBot
139.217.52.0/28,BingBot
191.233.204.224/28,BingBot
20.36.108.32/28,BingBot
20.43.120.16/28,BingBot
40.79.131.208/28,BingBot
40.79.186.176/28,BingBot
52.231.148.0/28,BingBot
51.8.235.176/28,BingBot
51.105.67.0/28,BingBot

Commands

First type the following to get CIDR prefixes:
$ cat bingbot.json | jq '.prefixes[]'
Then pipe outputs to create new JSON to add a comment field:
$ cat bingbot.json | jq -r '.prefixes[] | {cidr: .ipv4Prefix, comment: "BingBot"}'
Finally, use the join() to make a .CSV file:
$ cat bingbot.json | jq -r '.prefixes[] | {cidr: .ipv4Prefix, comment: "BingBot"} | join(",")'
Save it:
$ cat bingbot.json | jq -r '.prefixes[] | {cidr: .ipv4Prefix, comment: "BingBot"} | join(",")' > bingbot.csv
Verify it:
$ cat bingbot.csv

Solutie

Tip solutie

Permanent

Follow Us