How to convert arbitrary simple JSON to CSV using jq?

jq is a lightweight and flexible command-line JSON processor that enables you to parse, filter, map, and transform structured data with high flexibility. It is particularly well-suited for converting JSON data into other formats, such as CSV.

Conversion Steps

1. Analyze JSON Structure: First, examine the JSON structure to identify the required fields.
2. Write a jq Filter: Use jq's query language to extract the necessary data fields.
3. Format Output: Convert the extracted data into CSV format.
4. Use Command-Line Redirection: Redirect jq's output to a CSV file.

Specific Example

Consider the following JSON file (data.json):

json
[
  {
    "name": "Alice",
    "age": 25,
    "email": "alice@example.com"
  },
  {
    "name": "Bob",
    "age": 30,
    "email": "bob@example.com"
  }
]

We aim to convert this JSON to a CSV file containing all fields (name, age, email). Use the following jq command:

bash
jq -r '.[] | [ .name, .age, .email ] | @csv' data.json > data.csv

Command Breakdown:

• jq -r: Executes jq and outputs raw strings instead of JSON-encoded strings.
• '.[] | [ .name, .age, .email ] | @csv': This filter performs these actions:
  • .[]: Iterates over each element in the array.
  • [ .name, .age, .email ]: Constructs a new array containing name, age, and email for each element.
  • @csv: Converts the array to CSV lines.
• data.json > data.csv: Redirects the output to the data.csv file.

After execution, the data.csv file contains:

shell
"Alice",25,"alice@example.com"
"Bob",30,"bob@example.com"

Thus, the JSON data is successfully converted to CSV format. This process is highly flexible, allowing you to adjust the jq filter as needed to extract different data or modify the output format.