Convert CSV to JSON with JQ

Nov 1, 2023 linux jq json csv
Share on:

Convert CSV to JSON 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.

In a previous post I covered the task of converting JSON to CSV.

Recently I realised that I never covered the opposite transaction i.e. CSV to JSON. So here is the missing piece to that puzzle. If you are working with data systems transformation can be a major hassle. I highly recommend using an existing tooling where appropriate. Taking the well worn path can make life much easier.

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

System Requirements

Installation

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

Working with CSV and JQ

Working with jq takes all the hardwork out of handling json. How does it fair with CSV based files? Lets look at an example

  1. Create a new CSV file named test1.csv. Use the content below:
1item,task
2Do this,This is how its done

NOTE: The CSV includes a header and we will use these fields as the JSON Key. The data fields will act as the JSON Value.

Transform CSV to JSON

To transform the CSV to JSON, we need to tell jq a bit about the format.

We can observe the following regarding the CSV

  • Separator field used is a comma
  • Should handle multiple rows
  • A header row is included and items 0 and 1 will be used respectively

Based on the above we can build a jq command as follows:

  • Use the -R parameter to allow a redirect < test1.csv
  • Use split(",") to indicate the item separator
  • Use {item: .[0], task: .[1]} to indicate the JSON data format
  • Use {questions: [ ]} to label the array the resultant array

Adding the questions label is optional, I just like an array to be labelled :-) With the above syntax outlined the command is ready to be executed.

  1. Use the following command
1jq -R '{questions: [inputs | split(",") | {item: .[0], task: .[1]}]}' < test1.csv

Which will provide an output similar to that below:

1{
2  "questions": [
3    {
4      "item": "'Do this'",
5      "task": "'This is how its done'"
6    }
7  ]
8}

Note: In the above I am using inputs rather than input. I do this as I want the above jq command to work with multiple CSV lines.