Using JQ to Convert JSON to CSV: Training Wheels
Last week I wrote about my adventures into JSON and the Shopify API, and using jq to convert between JSON and CSV. Although I’ve moved on from using JQ I wanted to share some sample usage of JQ. This blog post will be about the basics of using jq.
First, the данные
The sample data is a JSON file that represents a single order from Shopify’s API.
I’d recommend copying and pasting it into Gabor Turi’s Online JSON Viewer to out put a more readable, block divided format to follow along.
II. Piping input to JQ
jq is a JSON processor, which is used with UNIX pipes ‘|’. Unix pipes are a way to redirect the output of a command to act as the input of another one.
For example:
$ cat sijo.txt | lessTry copying this excerpt from Wikipedia about Korean poetry and saving it as sijo.txt in your shell. Then type the command above.
You should see something like:
Sijo is a traditional Korean vernacular poetic form that emerged in the Koryǒ period, flourished during the Chosǒn Dynasty, and is still written today. Common themes include, but are not limited to, the following: nature, nostalgia of the past, love interests, historical events, moral instruction. Most were written and enjoyed by the educated yangban class. However, there was an exception to this. Kisaengs were of the lowest class, yet they could partake in creating and reciting sijo.Structurally, 'sijo' is widely known to have three lines averaging 14-16 syllables, for a total of 44-46: theme (3, 4,4,4); elaboration (3,4,4,4); counter-theme (3,5) and completion (4,3). However, only a small percentage of sijo actually follow this structure. Instead of syllabic count, it is more accurate to structure sijo with hemistichs or syntactic units. Each line is composed of 2 hemistichs and within each hemistich is a syntactic unit, which is why there are 2 syntactic units in each sijo line. This structure, however, may vary dependent on the type of sijo as well. For instance, narrative sijo (sasol sijo) is more novel-like, with the second line being long and completely expanded. Sijo with the 3-line format follows a common structure of having the first line introduce the situation and establishing the theme. Line 2 elaborates on and develops the information provided in the first line. The first half of the third and final line employs a “twist”: a surprise of meaning, sound, or other device as well as a conclusion or resolution. This is to act as a counter to the theme that was introduced.Example:오늘이 오늘이소서 매일이 오늘이소서:
Your cursor will be placed at that last line, to the right of the colon ‘:’. You are in the ‘less’ command. From here, you can hit spacebar to go to the next page, or ‘q’ to quit or ‘h’ for help.
So, how did this work? Well cat sijo.txt con’cat’enates the file, usually the output goes to your terminal (window/screen), but when you use the pipe ‘|’ operator it will push that output as an input to the next command, in this case ‘less’.
How is this related to jq ?
Commands like cat 'file-name’ and ls 'directory-name’ work by prepending the target file or directory. When using jq we will usually be using a command like cat some-file.json and piping that output to the jq command. For example:
$ cat sampleOrder.json | jq '.'If you get an error, or do not have jq installed, checkout this stack overflow post.
三 , The commands
The most basic jq operator is the dot ‘.’ operator. In the sample command above, the jq command will just respond by pretty printing the json file.
$ cat sampleOrder.json | jq '.order'[output omitted for brevity]
Outputs value of the key ‘order’. This key is the top level of the Shopify order JSON object. If you are using the JSON visualization website you can see it is the top-most key and the value of this pair is the rest of the file.
$ cat sampleOrder.json | jq '.order.id'450789469
Outputs the id value of the key order which is ‘450789469’.
$ cat sampleOrder.json | jq '.order.line_items'[
{
"id": 466157049,
"variant_id": 39072856,
"title": "IPod Nano - 8gb",
"quantity": 1,
"sku": "IPOD2008GREEN",
"variant_title": "green",
"vendor": null,
"fulfillment_service": "manual",
"product_id": 632910392,
"requires_shipping": true,
"taxable": true,
"gift_card": false,
"name": "IPod Nano - 8gb - green",
"variant_inventory_management": "shopify",
"properties": [
{
"name": "Custom Engraving Front",
"value": "Happy Birthday"
},
{
"name": "Custom Engraving Back",
"value": "Merry Christmas"
}
],
"product_exists": true,
"fulfillable_quantity": 1,
"grams": 200,
"price": "199.00",
"total_discount": "0.00",
"fulfillment_status": null,
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"discount_allocations": [],
"admin_graphql_api_id": "gid://shopify/LineItem/466157049",
"tax_lines": [
{
"title": "State Tax",
"price": "3.98",
"rate": 0.06,
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
}
}
]
},
{
"id": 518995019,
"variant_id": 49148385,
"title": "IPod Nano - 8gb",
"quantity": 1,
"sku": "IPOD2008RED",
"variant_title": "red",
"vendor": null,
"fulfillment_service": "manual",
"product_id": 632910392,
"requires_shipping": true,
"taxable": true,
"gift_card": false,
"name": "IPod Nano - 8gb - red",
"variant_inventory_management": "shopify",
"properties": [],
"product_exists": true,
"fulfillable_quantity": 1,
"grams": 200,
"price": "199.00",
"total_discount": "0.00",
"fulfillment_status": null,
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"discount_allocations": [],
"admin_graphql_api_id": "gid://shopify/LineItem/518995019",
"tax_lines": [
{
"title": "State Tax",
"price": "3.98",
"rate": 0.06,
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
}
}
]
},
{
"id": 703073504,
"variant_id": 457924702,
"title": "IPod Nano - 8gb",
"quantity": 1,
"sku": "IPOD2008BLACK",
"variant_title": "black",
"vendor": null,
"fulfillment_service": "manual",
"product_id": 632910392,
"requires_shipping": true,
"taxable": true,
"gift_card": false,
"name": "IPod Nano - 8gb - black",
"variant_inventory_management": "shopify",
"properties": [],
"product_exists": true,
"fulfillable_quantity": 1,
"grams": 200,
"price": "199.00",
"total_discount": "0.00",
"fulfillment_status": null,
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"discount_allocations": [],
"admin_graphql_api_id": "gid://shopify/LineItem/703073504",
"tax_lines": [
{
"title": "State Tax",
"price": "3.98",
"rate": 0.06,
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
}
}
]
}
]
Outputs the array of 3 line items. (again, if you check the visualization website it helps to see where this is coming from).
$ cat sampleOrder.json | jq '.order | .id'450789469
This command also outputs the id of the key order , but do you see how it is different?
By piping ‘|’ the output of jq .order inside of the single quotes ' we can access that entire level of values.
So, now let’s try this:
cat sampleOrder.json | jq '.order | .id, .email, .created_at'450789469
"bob.norman@hostmail.com"
"2008-01-10T11:00:00-05:00"
Cool, right? 🤓
Handling Arrays
Okay, so now I bet you are wondering how jq handles arrays inside the order key. Well, one array is line_items which are the items ordered by the customer in this order.
$ cat sampleOrder.json | jq '.order.line_items'[
{
"id": 466157049,
"variant_id": 39072856,
"title": "IPod Nano - 8gb",
"quantity": 1,
"sku": "IPOD2008GREEN",
"variant_title": "green",
"vendor": null,
"fulfillment_service": "manual",
"product_id": 632910392,
"requires_shipping": true,
"taxable": true,
"gift_card": false,
"name": "IPod Nano - 8gb - green",
"variant_inventory_management": "shopify",
"properties": [
{
"name": "Custom Engraving Front",
"value": "Happy Birthday"
},
{
"name": "Custom Engraving Back",
"value": "Merry Christmas"
}
],
"product_exists": true,
"fulfillable_quantity": 1,
"grams": 200,
"price": "199.00",
"total_discount": "0.00",
"fulfillment_status": null,
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"discount_allocations": [],
"admin_graphql_api_id": "gid://shopify/LineItem/466157049",
"tax_lines": [
{
"title": "State Tax",
"price": "3.98",
"rate": 0.06,
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
}
}
]
},
{
"id": 518995019,
"variant_id": 49148385,
"title": "IPod Nano - 8gb",
"quantity": 1,
"sku": "IPOD2008RED",
"variant_title": "red",
"vendor": null,
"fulfillment_service": "manual",
"product_id": 632910392,
"requires_shipping": true,
"taxable": true,
"gift_card": false,
"name": "IPod Nano - 8gb - red",
"variant_inventory_management": "shopify",
"properties": [],
"product_exists": true,
"fulfillable_quantity": 1,
"grams": 200,
"price": "199.00",
"total_discount": "0.00",
"fulfillment_status": null,
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"discount_allocations": [],
"admin_graphql_api_id": "gid://shopify/LineItem/518995019",
"tax_lines": [
{
"title": "State Tax",
"price": "3.98",
"rate": 0.06,
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
}
}
]
},
{
"id": 703073504,
"variant_id": 457924702,
"title": "IPod Nano - 8gb",
"quantity": 1,
"sku": "IPOD2008BLACK",
"variant_title": "black",
"vendor": null,
"fulfillment_service": "manual",
"product_id": 632910392,
"requires_shipping": true,
"taxable": true,
"gift_card": false,
"name": "IPod Nano - 8gb - black",
"variant_inventory_management": "shopify",
"properties": [],
"product_exists": true,
"fulfillable_quantity": 1,
"grams": 200,
"price": "199.00",
"total_discount": "0.00",
"fulfillment_status": null,
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"discount_allocations": [],
"admin_graphql_api_id": "gid://shopify/LineItem/703073504",
"tax_lines": [
{
"title": "State Tax",
"price": "3.98",
"rate": 0.06,
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
}
}
]
}
]
This returns the 3 items in the array line_items . So the next question is how to get a single item out…
$ cat sampleOrder.json | jq '.order.line_items[1]'{
"id": 518995019,
"variant_id": 49148385,
"title": "IPod Nano - 8gb",
"quantity": 1,
"sku": "IPOD2008RED",
"variant_title": "red",
"vendor": null,
"fulfillment_service": "manual",
"product_id": 632910392,
"requires_shipping": true,
"taxable": true,
"gift_card": false,
"name": "IPod Nano - 8gb - red",
"variant_inventory_management": "shopify",
"properties": [],
"product_exists": true,
"fulfillable_quantity": 1,
"grams": 200,
"price": "199.00",
"total_discount": "0.00",
"fulfillment_status": null,
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"discount_allocations": [],
"admin_graphql_api_id": "gid://shopify/LineItem/518995019",
"tax_lines": [
{
"title": "State Tax",
"price": "3.98",
"rate": 0.06,
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
}
}
]
}
This works. But so does:
// returns the array, same as above
$ cat sampleOrder.json | jq '.order.line_items | .[1] '[output omitted for brevity]// returns elements 1,2
$ cat sampleOrder.json | jq '.order.line_items[1,2]'[output omitted for brevity]// returns the ids of the elements 1&2
$ cat sampleOrder.json | jq '.order.line_items[1,2] | .id'518995019
703073504// returns id, name and the value of the key-value pair 'total_discount_set' inside each of the elements [1] and [2] of line_items.$ cat sampleOrder.json | jq '.order.line_items[1,2] | .id, .name, .total_discount_set["shop_money"]'518995019
"IPod Nano - 8gb - red"
{
"amount": "0.00",
"currency_code": "USD"
}
703073504
"IPod Nano - 8gb - black"
{
"amount": "0.00",
"currency_code": "USD"
}
Now, you can see we’re dealing with some shamanastic magic stuff here.
You can also remove keys from the JSON and the output will be the file without those keys:
// Outputs the two orders, after deleting the key-value pair total_discount_set (key), shop_money (value).$ cat sampleOrder.json | jq '.order.line_items[1,2] | del(.total_discount_set["shop_money"])'{
"id": 518995019,
"variant_id": 49148385,
"title": "IPod Nano - 8gb",
"quantity": 1,
"sku": "IPOD2008RED",
"variant_title": "red",
"vendor": null,
"fulfillment_service": "manual",
"product_id": 632910392,
"requires_shipping": true,
"taxable": true,
"gift_card": false,
"name": "IPod Nano - 8gb - red",
"variant_inventory_management": "shopify",
"properties": [],
"product_exists": true,
"fulfillable_quantity": 1,
"grams": 200,
"price": "199.00",
"total_discount": "0.00",
"fulfillment_status": null,
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"total_discount_set": {
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"discount_allocations": [],
"admin_graphql_api_id": "gid://shopify/LineItem/518995019",
"tax_lines": [
{
"title": "State Tax",
"price": "3.98",
"rate": 0.06,
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
}
}
]
}
{
"id": 703073504,
"variant_id": 457924702,
"title": "IPod Nano - 8gb",
"quantity": 1,
"sku": "IPOD2008BLACK",
"variant_title": "black",
"vendor": null,
"fulfillment_service": "manual",
"product_id": 632910392,
"requires_shipping": true,
"taxable": true,
"gift_card": false,
"name": "IPod Nano - 8gb - black",
"variant_inventory_management": "shopify",
"properties": [],
"product_exists": true,
"fulfillable_quantity": 1,
"grams": 200,
"price": "199.00",
"total_discount": "0.00",
"fulfillment_status": null,
"price_set": {
"shop_money": {
"amount": "199.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "199.00",
"currency_code": "USD"
}
},
"total_discount_set": {
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"discount_allocations": [],
"admin_graphql_api_id": "gid://shopify/LineItem/703073504",
"tax_lines": [
{
"title": "State Tax",
"price": "3.98",
"rate": 0.06,
"price_set": {
"shop_money": {
"amount": "3.98",
"currency_code": "USD"
},
"presentment_money": {
"amount": "3.98",
"currency_code": "USD"
}
}
}
]
}
You can also slice arrays:
$ cat sampleOrder.json | jq '.order | .line_items[0,2] | .id'
466157049
703073504And create arrays out of the return values inside jq
$ cat sampleOrder.json | jq '.order | [.line_items[0,2].id] '
[
466157049,
703073504
]I’ve just scratched the surface of jq functionality and it’s command set. I highly recommend the official manual for more details. Next time, I’ll go into details of converting from JSON to CSV (finally!)
Sources:
jq Manual by Stephen Dolan (Official repo)
JSON on the command line with jq by George Ornbo
Reshaping JSON with jq by Matthew Lincoln
Other stories in this series:
