My First Steps Into Using JQ to Convert JSON to CSV
Introduction
[disclaimer: This article is about my preparations leading up to using the JQ commands. My next article will be the actual examples, stay tuned.]
This week I spent some time working on the Shopify API (guide: Build a Shopify App with Node and React). I needed to retrieve the JSON data and convert it to CSV. From what I could understand, the proper way to do this would be to create my own backend and fetch the API data to it. Then use a frontend to pull the data and manipulate it the way I wanted. (If anyone knows a better way, please share!)
The first method I tried was to pull the data directly from a React front end SPA and the API threw up a CORS error. Now, there are tons of resources for developing Shopify Apps in Rails [github: shopify_app], Node [github: shopify-app-cli and shopify-app-node], python, express, django — there’s a whole page of examples. Not to mention resources consolidated by coders like Julio Napurí (julionc@github [github: awesome-shopify]. Why do something so hacky as using curl (transfers data with URLs in your terminal) to get a raw ouput of API data and convert it to CSV?
Because of time constraints. My team needs the data now. I recently pivoted into software development from a varied background and honestly, my experience with APIs is limited. I went through the process of creating a Shopify test app, then I created a test shop, built a Node/Express server to do the API data fetch and setup my routes. Then, there were many struggles to get the Oauth authentication flow to work, but after MUCH trial and error, and Googling I got the Shopify Oauth flow to give me my access token and requested scopes. But, then I got a nudge from my project manager (aka Boss Lady) and realized I still had a long way to go. I still needed to setup the server on cloud, build my react front end to be user friendly, and I still didn’t know how it would all integrate into the Shopify admin panel.
So, I thought “Let me see if there’s a way to take the raw JSON data I get back from the API using simple HTTP authentication, redirect it into a .JSON file and simply convert that into a .CSV file. I couldn’t imagine that this hasn’t been done before and there must be a tool to easily pull out the appropriate values from the .JSON file and spit them out into a .CSV.
The Tools
There are a variety of JSON to CSV tools out there. I found these two particularly useful:
- Eric Mill’s “Convert JSON to CSV” site converts .JSON to .CSV in your browser, without sending the data to outside servers. One thing I particularly love about this site is that the source code for the conversion is right in the .HTML. Just navigate to the site, and right click ‘View Page Source’ (Ctrl-u on chrome). Eric Mill’s biography was also a very interesting read.
- Gabor Turi’s “Online JSON Viewer” was useful to see how the JSON structure was made up. It feels a lot clearer to me than the usual JSON formatters you get in browser plugins (which are great too, don’t get me wrong!)
(If you’ve got some recommendations, I’d love to hear about them!)
The tool I’m talking about today is jq [website] and I don’t mean jQuery.
The jq website describes jq as “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.” Seeing the word sed gave me flashbacks of my early years on SunOS and Solaris machines when I knew even less than I know now. Trying to understand it’s command syntax was like trying to translate hieroglyphics, but I said “I enjoy suffering, what the heck, let’s try this out.” Turns out its not that bad…well, tbh, I just took my first baby step, so we’ll see how it goes.
The Process
The first step is to retrieve the .JSON data from the Shopify API. I’ve been working with the Order resource whose API reference is here. It will describe the REST methods and URLs to hit and describe the significance of all the properties.
I am using the cURL [curl website] command to retrieve my data. I find cURL useful for quick and dirty downloads of data and quick testing of whether RESTful API routes are working. Some resources for cURL I found useful are:
- This really basic guide at Geekflare.com by Chandan Kumar.
- And this one by Tania Rascia on making API requests with Postman and cURL.
Here is an example of the format that the cURL command takes to access a Shopify store at dans-shop.myshopify.com:
$ curl https://<apikey>:<password>@dans-shop.myshopify.com/admin/api/2020-01/<endpoint>I put in three placeholders for clarity: <apikey>, <password>, <endpoint>
- apikey: An api key looks like this: 8834ae00cf891fea08fb41b260278872 and is provided by Shopify’s dev control panel when you create an app. It comes with a …
- api key password: 729c4b37d6834e2092d6cb80bbd323c9
- The endpoint is the RESTful endpoint you are trying to hit. There is a list of endpoints for Orders here.
With all the information put in, the command looks much more complicated but is really not that bad. Just look for the colon (‘:’) between the api key and password.
$ curl https://8834ae00cf891fea08fb41b260278872:729c4b37d6834e2092d6cb80bbd323c9@dans-site.myshopify.com/admin/api/2020-01/orders.json?ids=450789469.jsonNotice at the very end of the command, where I had used a placeholder <endpoint> is now orders.json?ids=450789469.json . If you look up the list of endpoints for Orders, you can see that this is a request for a single Order, with the id 450789469 .
The JSON Data
After executing this command in your Bash shell, you’ll see some transfer speed information, and if all goes well, you’ll get a flood of text that looks like this:
One way to capture this in your terminal is to go back up to the top (if your scroll buffer goes that far) and copy/paste it. The other way is to use the Bash shell’s output redirection character > to send the output of the cURL command to a file.
$ curl https://8834ae00cf891fea08fb41b260278872:729c4b37d6834e2092d6cb80bbd323c9@dans-site.myshopify.com/admin/api/2020-01/orders.json?ids=450789469.json > order.json
In case you’re curious about how this works there is more information about special characters in Bash here.
So, now we have a text file named order.json in our directory, lets start messing around with it using jq .
Quick Overview of JSON
For those of you like me, who has dealt with .JSON files but haven’t given much thought to them. Here is simple overview, briefly:
JSON objects are surrounded with curly braces {} and contain key-value pairs. The keys must be of type:string but the values can be any valid JSON type. This includes string, number, object, array, boolean and null. The keys and values are separated by a colon : for example a key of name and value of danny would look like this: "name":"danny" .
In our order example, the first key is order (line 3) and its corresponding value is the rest of the file. That “rest of the file”value is made up of more key-value pairs, arrays, and more objects .
The important thing to understand here is that we are going access the sub-levels below the top level (named order here) using the dot . orbracket [] notation. Looking at our example order.json file, if we want the item in line 7, the created_at value of “2008–01–10T11:00:00–05:00” we would use the dot notation to access it: order.created_at . To access the “Happy Birthday” string in line 67, we would have to access the array that is stored in the note_attributes key. Notice that it’s also the first element of the array that is stored in note_attributes , so to retrieve this object that contains our string we would use order.note_attributes[0] . However, that will return the object stored at that key:
{
"name": "custom engraving",
"value": "Happy Birthday"
}To get to our value of “Happy Birthday” we would now use the nested key value like this: order.note_attributes[0].value
Hopefully this aint too confusing, it took me a while to get it and understand I need to discriminate between top level values, arrays, and objects. Once you get it, it makes more sense, but is still a headache to look at!
Ok, we’re all set to start using jq commands and seeing what that output looks like. Follow me and get notified when I write that up next week! Thanks for reading. 😁 [update: here’s that next article!]
Other stories in this series:
