Convert JSON to CSV with JQ

Mar 5, 2021 linux jq json csv
Share on:

Convert JSON to CSV with JQ.

jq is like sed for JSON data - you can use it to slice and filter and map and transform structured data with the same ease that sed, awk, grep and friends let you play with text.

Being able to move between different file format is quite a common task, so I was delighted to find a quick and easy method for JSON/CSV. In the following blog post learn how to use jq to transform JSON data into the CSV format. Once the data is in CSV format, it should be a simple matter to import into other applications (e.g. databases, spreadsheets, etc).

  • The example assumes you have a linux environment.
  • An example JSON file is provided to illustrate the commands required.

System Requirements

Installation

  1. Install the jq utility
1sudo apt install -y jq

Working with JSON and JQ

Working with jq takes all the hardwork out of handling json. Lets look at an example

  1. Create a new JSON file named test.json. Use the content below:
 1{
 2  "stock": [
 3    {
 4      "id": "ABC0001",
 5      "item": "Cable",
 6      "description": "USB-A Cable"
 7    },
 8    {
 9      "id": "ABC0002",
10      "item": "Cable",
11      "description": "USB-B Cable"
12    },
13    {
14      "id": "ABC0003",
15      "item": "Cable",
16      "description": "USB-C Cable"
17    }
18  ],
19  "timestamp": "2021-03-06T14:55:45.680Z",
20  "author": "Rich Rose"
21}
  1. jq can be used to parse the example test.json file
1jq . test.json

NOTE: Here jq not only parses the file, it also validates it.

  1. jq can be used to read a JSON element
1jq .timestamp test.json

NOTE: Here jq is being asked to read the top level element timestamp

  1. jq can be used to read a JSON array
1jq .stock[].id test.json

NOTE: Here jq is being asked to read the top level element stock. We note that the stock item represents an array. Each array item contains an element (e.g. id, item and description)

Transform JSON to CSV

Sometimes it necessary to transform a JSON file into another format. One common format is comma separated values (CSV). Fortunately jq is able to help with this task and make data transformation very simple. Lets use the same example json file to transform the stock items into a CSV file.

  1. First we need to decide which elements we need. Lets use the Stock items
1jq '.stock[]' test.json

Which will provide an output similar to that below:

 1{
 2  "id": "ABC0001",
 3  "item": "Cable",
 4  "description": "USB-A Cable"
 5}
 6{
 7  "id": "ABC0002",
 8  "item": "Cable",
 9  "description": "USB-B Cable"
10}
11{
12  "id": "ABC0003",
13  "item": "Cable",
14  "description": "USB-C Cable"
15}
  1. Select the information that is to be displayed in the CSV. In our example, the fields id, item and description are used.
1jq '.stock[] | [.id, .item, .description]' test.json

NOTE: Here jq is being asked to read the top level element stock. We then use the pipe command (i.e. |) to pass the result as a parameter to the next command and perform another parse. In the second parse, we indicate we want specific information (i.e. .id, .item and .description).

The above command should provide an output similar to that below:

 1[
 2  "ABC0001",
 3  "Cable",
 4  "USB-A Cable"
 5]
 6[
 7  "ABC0002",
 8  "Cable",
 9  "USB-B Cable"
10]
11[
12  "ABC0003",
13  "Cable",
14  "USB-C Cable"
15]
  1. jq now can be told that we would like the output shown as a CSV. To achieve that we introduce the keyword @csv.
1jq '.stock[] | [.id, .item, .description] | @csv' test.json

NOTE: We are again using the | to chain the previous command results, this time to @csv.

The above command should provide an output similar to that below:

1"\"ABC0001\",\"Cable\",\"USB-A Cable\""
2"\"ABC0002\",\"Cable\",\"USB-B Cable\""
3"\"ABC0003\",\"Cable\",\"USB-C Cable\""
  1. The final step is to tell jq that we would like to view the raw output. Add the -r parameter and we have the desired formatting.
1jq -r '.stock[] | [.id, .item, .description] | @csv' test.json

The above command should provide an output similar to that below:

1"ABC0001","Cable","USB-A Cable"
2"ABC0002","Cable","USB-B Cable"
3"ABC0003","Cable","USB-C Cable"