How to convert JSON to CSV from the command-line?

• August 25, 2020
• 3 min. read
• 0 Comments

Are you looking for an easy and fast way to convert a JSON file to a CSV one? In this blog post, I will show you how you can use jq command-line tool to do exactly that.

Table of Contents

• Install jq
• Prepare the JSON file
• Convert a JSON file to the CSV one

Install jq

First, you need to install jq in your operating system - https://stedolan.github.io/jq/download/

Prepare the JSON file

To convert JSON file to the CSV format we will need a JSON file that is represented by an array of objects. In this tutorial, I use Github’s Jobs API that returns JSON in that desired format. Below you can find a curl command that searches for open kotlin job positions and stores the result in the jobs.json file.

$ curl -s "https://jobs.github.com/positions.json?description=kotlin" > jobs.json

Convert a JSON file to the CSV one

And here is how we can convert input JSON file to the CSV format.

$ jq -r 'map({id,title,url,company,location}) | (first | keys_unsorted) as $keys | map([to_entries[] | .value]) as $rows | $keys,$rows[] | @csv' jobs.json > jobs.csv

$ cat jobs.csv
"id","title","url","company","location"
"2ededc50-3c18-4d2c-ba54-95151ca4209a","Android Engineer","https://jobs.github.com/positions/2ededc50-3c18-4d2c-ba54-95151ca4209a","Trade Republic Bank GmbH","Berlin"
"2cc06700-d9ac-45ad-9bd7-a7e5d3c29b6f","Software Engineer - Android","https://jobs.github.com/positions/2cc06700-d9ac-45ad-9bd7-a7e5d3c29b6f","AiCure","Remote"
"242132e0-f129-4a1b-8bfa-30f2fed13123","Senior Backend Developer (Data Science)","https://jobs.github.com/positions/242132e0-f129-4a1b-8bfa-30f2fed13123","komoot","Remote Europe"
"fd8c3b7c-ccfe-4cc1-882c-ef9c0522903e","Software Engineer","https://jobs.github.com/positions/fd8c3b7c-ccfe-4cc1-882c-ef9c0522903e","HBM nCode Federal LLC","Starkville, MS"
"7d84945b-893d-493e-957a-4ed9e6f84607","Software Engineer","https://jobs.github.com/positions/7d84945b-893d-493e-957a-4ed9e6f84607","HBM nCode Federal LLC","Southfield, MI"
"2231d64f-e79b-4036-8601-24b2717b2896","Senior Fullstack / Flutter Developer (m/f/d)","https://jobs.github.com/positions/2231d64f-e79b-4036-8601-24b2717b2896","Superlist","Remote"
"caa90907-8252-4732-a815-06d96f1348bb","Senior Software Engineer - Mobile (m/f/d)","https://jobs.github.com/positions/caa90907-8252-4732-a815-06d96f1348bb","BASF Digital Farming GmbH","Köln"

Let’s deconstruct it piece by piece.

• We run jq -r to output raw strings (without double quotes.)

• In the filter part, we pipe multiple filters together, starting with map({id,title,url,company,location}). This filter instructs jq which keys we want to extract from the input JSON file.

• Then we use (first | keys_unsorted) as $keys filter which takes the first object, extracts its keys and stores them under the $keys variable as an array.

• Next, we use map([to_entries[] | .value]) as $rows filter which converts every key-value entry like "foo": "bar" into an object like {"key":"foo","value":"bar"} so we can extract only values as an array and store it in a $rows variable.

• Once we do it, we can use $keys,$rows[] filter to put keys and rows together and then pipe it with the @csv filter to convert JSON objects to CSV rows.

How to merge two JSON files in the command-line using jq? | #jq #json #curl

In this jq tutorial video, I show you how you can merge two JSON files in the command-line using jq JSON processor. I explain how to merge those files using a simple "add" filter, then I explain how to merge nested objects, as well as how to merge arrays of different objects. Watch now »

• curl
• jq
• json
• csv
• command-line
• how-to