imiell

This post was originally triggered – and I choose that word carefully – by a recent experience on a cloud cost-optimisation project. These experiences prompted me to consider how things had changed since I started working in software.

As part of the project that provoked me, I was researching the topic of cloud cost analysis and was struck by how many people complained that the tools the big three providers give you are not adequate to the task. I had a look myself and indeed found that the questions I wanted answering were difficult to answer using the GUI tools.

No problem, I thought: I’ll just download the raw CSVs and find an open source project that sticks the data in a database, allowing me to write whatever queries I want on the data. After some searching I couldn’t find any such project, so wrote one myself and stuck it on GitHub. Once this was built I could write all sorts of queries like ‘Compare costs per GCP service/project/sku with previous month, showing any percentage change greater than x’, modifying them at will to hone in on the specific data I was interested in much faster and more effectively than could be achieved with GUIs. Some of the more generic ones are in the repo.

While working on this with a very bright and knowledgeable younger colleague, I was struck by the fact that he’d never needed to learn SQL, and he made sense of these queries by comparing them to other JSON-based *QLs that he’d come across (PromQL, GraphQL etc). This surprised me.

The Good Old Days

I’m going to grossly simplify matters here, but when I was coming up in the industry (around 2001) three-tier backend applications were where it was at. There were webservers (usually Apache), application servers, and databases. Knowledge and experience was thin on the ground, so you had to know a bit of everything. HTML, Javascript (mostly form submission fiddling), a language to do business logic in the application server layer (usually Java in those days, but I’d sworn off it for life), and SQL for the database. Different people had different preferences and specialisms, but you had to have a basic knowledge of all those technologies to be an effective developer.

The entire development engineer industry was about ‘dev’ at this point. ‘Ops’ was a challenge that was at the time usually either done in a half-hearted way by the developers themselves (in smaller orgs), or passed over to the sysadmins to manage (in bigger orgs). The smaller orgs grew into a devops mindset (“we’re not hiring a separate team to support the operation of the system, it’s cheaper to get the devs to do it”), and the bigger ones embraced SRE (“we need capable engineers to support and manage site reliability engineer live systems and there’s economies of scale in centralising that.”). Data science was also not really a thing then.

There were DBAs but they were there to ensure backups were done, and punish those who polluted their treasured databases with poorly written queries or badly defined indexes. They therefore sometimes doubled up as SQL tutors to the devs. 

Since almost everyone was a dev, and devs did a bit of everything, engineers were often asked SQL questions in job interviews, usually starting with a simple join and then asking about indexes and performance characteristics for the more advanced candidate.

The Rise of the Specialist

Along with the rise of ops in the noughties came the continuation of increasing specialisation in the IT industry. We’d had the DBA for some time, but the ‘dev’ was gradually supplanted by the front-end engineer, the tester, the data scientist, the DevOps engineer, and later the SRE, the security engineer… the list goes on.

I say this was a continuation because this process has been going on since computers existed, from the original physicists and technicians that maintained and programmed machines the size of rooms bifurcating into COBOL and FORTRAN programmers in the 60s and 70s, leading to system admins in the 80s, and then network admins, and so on.

Parallels to this can be seen in the medical profession. Once upon a time we had a ‘ship’s surgeon‘, who had to deal with pretty much any ailment you might expect from having cannon balls fired at you while lugging your heavy cannons to face the enemy, and (in more peaceful moments) disease-ridden sailors sharing whatever diseased they’d brought in from shore leave. Fast-forward hundreds of years and we now have plastic surgeons that specialise in just in ear surgery (otologists).

And a similar thing seems to be happening with computing jobs. Whereas once upon a time everyone who interacted with a computer at work would have likely had to type in (or even write) a few SQL queries in the past, now there’s a data scientist for that. The dev now attends scrum meetings and complains about JIRA (and doesn’t even write much JQL).

Is SQL Becoming a Niche Skill?

So back to the issue at hand. Opinion seems to be divided on the subject. These interactions were typical when I asked on Twitter:

There’s no doubt that historical demand for SQL has always been strong. While SQL’s elder sibling COBOL clings to life on the mainframe, SQL remains one of the most in-demand skills in the industry, according to the TIOBE index. In the 20 years since 2004 it’s gone from 7th to 8th most popular language along side mainstays like C and upstarts like C++, Java, and Visual Basic.

Not only that, but SQL is still a widely used data querying language outside its common domains. Those who prefer SQL to JQ (and who among us without a PhD in mathematics do not?) might be interested in DuckDB, a SQL-fronted alternative to jq. And facebook’s osquery, which gives a common SQL interface to query different operating systems’ state for compliance, security, or performance purposes.

‘Schemas Are Bad, M’Kay?’

In another recent project with a similar demographic of engineer, I was horrified to be voted down about using a relational database as the consensus was ‘schemas are bad’ and ‘MongoDB is more flexible’. This, I thought, is a world gone topsy-turvy. To gain dominion over your data, you need to wrestle it to the floor with a schema. Without that, you won’t have the power to range over it with whatever questions you have to ask it, and you won’t have a database, just a set of documents or a fancier filesystem.

To this it’s often objected that schemas are ‘hard to change’, but this is only true when you’ve got many users and code dependencies on the database or huge amounts of data, not when you’re initially building up a system. They can be annoying to change, because the syntax varies between vendor and you often end up looking up whether it’s ALTER TABLE ADD INDEX or CREATE INDEX. LLMs almost banish this problem, however, as they are incredibly good at saving you time on this.

Poking my head out of that world I’ve been shocked at how few people currently in IT really understand data programming/management these days. npm and Javascript sure, python probably, but data, its management and any programming around it seems to have been pushed to the sides for the ‘data scientists’ to deal with, as though data were not an essential aspect of pretty much any computing work.

But perhaps I’m just an old ship’s surgeon who might know how to saw a leg off and administer smelling salts, but doesn’t have the deft skill of an otologist who practices on their specialism on the daily. I’ll just call myself a ‘full stack’ engineer and hope no-one notices.

If you like this, you might like one of my books:
Learn Bash the Hard Way
Learn Git the Hard Way
Learn Terraform the Hard Way

This post was originally published here, and is re-posted with permission.

Other Posts

• Part I – JSON, Objects and Arrays
• Part II – The jq Command
• Part III – Filters

Pipes

The pipe is the next-most used feature of jq after filters. If you are already familiar with pipes in the shell, it’s a very similar concept.

How Important is this Post?

Pipes are fundamental to using jq, so this section is essential to understand. We will cover:

• The pipe (|)
• The ‘.[]‘ filter
• Nulls
• Array references

Setup

Create a folder to work in, and move into it:

$ mkdir ljqthw_pipes
$ cd ljqthw_pipes

The .[] Filter

Before we get to the pipe, we’re going to talk about another filter. This filter
doesn’t have a snappy name, so we’re going to refer to it as the .[] filter. It’s
technically known as the ‘array/object value iterator’.

Create a simple JSON document in a file:

$ echo '[{"username": "alice", "id": 1 }, {"username": "bob", "id": 2 }]' > doc1

This document is an array representing users on a system, with two objects in it.
Each object has a username and id name-value pair.

Since the file is a simple array, we can filter the individual objects by referring
to the array index in a fashion similar to other languages, by putting the index
number in square brackets:

$ jq '.[0]' doc1
$ jq '.[1]' doc1

If the referenced index doesn’t exist, then a null is returned:

$ jq '.[2]' doc1

You can also refer to negative indexes to reference the array from the end rather than the start:

$ jq '.[-1]' doc1
$ jq '.[-2]' doc1
$ jq '.[-3]' doc1

If you don’t provide an index, then all the items in the array are returned:

$ jq '.[]' doc1

Look at the output. How are the items returned?

• In a new array?
• In a series of JSON documents?
• In a comma-separated list?

Running these commands may help you decide. Note that in the below commands we are using a shell pipe, not a jq pipe. The two uses of the pipe (|) character are analogous, but not the same:

$ jq '.[]' doc1 | jq '.'
$ jq '.[]' doc1 | jq '.[0]'

It’s really important to understand that the .[] filter does not just work on
arrays, even though it uses an array’s square brackets. If a series of JSON
objects are supplied, then it will iterate through the name value pairs, returning
the values one by one. That’s why it’s called the ‘array/object value iterator’.

Type this out to see that in practice:

$ echo '{"username": "alice"} {"id": 1}' > doc2
$ jq '.' doc2
$ jq '.[]' doc2

This time you had no array, just a series of two JSON objects. The .[] filter
iterates over a series of objects, returning the values in it. Remember, it’s
formally known as the ‘array/object value iterator’.

Now try this. What happens? Why?

$ echo '"username"' > doc3
$ jq '.[]' doc3

This filter confused me for a long time when I was learning jq, as I thought the
.[] filter just removed the square brackets from the json, returning an array’s
contents, like this example does:

$ echo '[{"a": "b", "c": "d" }]' > doc4
$ jq '.' doc4
$ jq '.[]' doc4

While it obviously does do that in this case, you should now be aware that this
filter does more than just ‘remove the square brackets’!

Introducing The Pipe

OK, back to doc1. This time you’re going to run the .[] filter over it, and then
use a jq pipe to process it further. Run these commands and think about what
the pipe character (|) is doing.

$ jq '.' doc1
$ jq '.[]' doc1
$ jq '.[] | .["username"]' doc1

The first command shows you the JSON document pretty-printed, to remind you of the
raw content.

The second command (the ‘array/object value iterator’) iterates over the objects in
the array, and produces two JSON documents. This is an example of what was pointed
out above: the .[] filter does not simply ‘remove the square brackets’. If it
did, the resulting two objects would have commas between them. It turns the two
objects within the array into a series of JSON documents. Make sure you understand
this before continuing.

The third command introduces the jq pipe. Note that this time, the pipe character
(|) is inside the jq string’s single quotes, making it a jq pipe rather than
the ‘shell pipe’ we saw earlier. The jq pipe passes the output of the .[] operator
on to the next filter (.["username"]). This filter then effectively retrieves
the value of the username name/value pair from each JSON document from the output
of the first .[] filter.

You can run the last command in other ways too, with identical results:

$ jq '.[] | ."username"' doc1
$ jq '.[] | .username' doc1

jq will figure out what you mean if you use either of those shorthands.

Nulls

Not all JSON documents have objects in them with consistent names. Here you create
a document with inconsistent names in its objects (username and uname):

$ echo '[{"username": "alice"}, {"uname": "bob" }]' > doc4

Now see what happens when you reference one of the names in your query:

$ jq '.[] | .username' doc4

The object with the matching name outputs the its value, but the other
object outputs a null value, indicating that there was no match for
the query for that object.

Referencing the other object’s name swaps which value it outputs and which
results in a null:

$ jq '.[] | .uname' doc4

What You Learned

• What the .[] (array/object value iterator) filter does, and how it works on arrays and objects
• How to reference array items
• The various ways you can refer to names in JSON objects

Exercises

1) Create a file with three JSON documents in them: an array with a single name-value pair object in it, an array with two name-value pair objects in it, and a single bare object. Run it through the .[] filter and predict what output it will give.

If you like this, you might like one of my books:
Learn Bash the Hard Way
Learn Git the Hard Way
Learn Terraform the Hard Way

Other Posts

• Part I – JSON, Objects and Arrays
• Part II – The jq Command
• Part IV – Pipes

Simple Filters

In this section we introduce the most-frequently used feature of jq: the filter. Filters allow you to reduce a given stream of JSON to another smaller, more refined stream of JSON that you can then do more filtering or processing on on if you want.

How Important is this Post?

Filters are fundamental to using jq, so this post’s content is essential to understand.

We will cover:

• The most commonly-used filters
• Selecting values from a stream of JSON
• Railroad diagrams, and how arrays cannot contain name-value pairs in JSON

Setup

Create a folder to work in, and move into it:

$ mkdir ljqthw_nv
$ cd ljqthw_nv

Now create a simple JSON document to work with:

$ echo '{"user1": "alice", "user2": "bob"}' > json_object

This file contains a simple JSON object with two name-value pairs (user1, and user2 being the names, and alice and bob being their values, respectively).

The Dot Filter

The concept of the filter is central to jq. The filter allows us to select those parts of the JSON document that we might be interested in.

The simplest filter – and one you have already come across – is the ‘dot’ filter. This filter is a simple period character (.) and doesn’t do any selection on the data at all:

$ jq . json_object

Note that here we are using the filename as the last argument to jq, rather than passing the data through a UNIX pipe with cat.

Arrays, Name-Value Pairs, and Railroad Diagrams

Now let’s try and create a similar array with the same name-value pairs, and run the dot filter against it:

$ echo '["user1": "alice", "user2": "bob"]' > json_array
$ jq . json_array

Was that what you expected? When I ran this I expected it to just work, based on my experiences of data structures in other languages. But no. Arrays in JSON cannot contain a name-value pair as one of its values – it’s not a JSON object, and arrays must be composed of JSON objects.

What can an array contain? Have a look at this railroad diagram, from https://json.org/:

The above diagram defines what an array consists of. Make sure you understand how to read the diagram before continuing, as being able to read such diagrams is useful in many contexts in software development.

Railroad Diagram
A railroad diagram is also known as a syntax diagram.
It visually defines the syntax for a particular language
or format. As your code or document is read, you can
follow the line,choosing which path to take as it splits. 
If your code or document can be traced through the
'railroad', then it is syntactically correct. As far as I can
tell, there is no 'official' format for railroad diagrams
but the conventional signs can easily be found
and deciphered by searching online for examples.

The ‘value’ in the array diagram above is defined here:

Following the value diagram, you can see that there is no ‘name-value’ pair defined within a value. Name-value pairs are defined in the object railroad diagram:

A JSON object consists of zero or more name-value pairs, separated by commas, making it fundamentally different from an array, which contains only JSON values separated by spaces.

It’s worth understanding these diagrams, as they can help you a lot as you try and create and parse JSON with jq. There are further diagrams on the https://json.org site (string, number, whitespace). They are not reproduced in this section, as you only need to reference them in the rarer cases when you are not sure (for example) whether something is a number or not in JSON.

Our First Query – Selecting A Value

Now, back to our original document.

$ cat json_object
{
  "user1": "alice",
  "user2": "bob"
}

Let’s say we want to know what user2‘s value is in this document. To do this, we need a filter that outputs only the value for a specific name.

First, we can select the object, and then select by name:

$ jq '.user2' json_object

This is our first proper query with jq. We’ve filtered the contents of the file down to the information we want.

In the above we just put the bare string user2 after the dot filter. jq accepts this, but you can also refer to the name by placing it in quotes, or placing it in quotes inside square brackets:

$ jq '.["user2"]' json_object
$ jq '."user2"' json_object

However, with the square brackets and without the quotes does not work:

$ jq '.[user2]' json_object

This is because the [""] form is the official way to look up a name in an object. The simpler .user2 and ."user2" forms we saw above are just a shorthand for this, so you don’t need the four characters of scaffolding ([""]) around the name. As you are learning jq it may be better to use the longer form to embed the ‘correct’ form in your mind.

Types of Quote Are Important

It’s important to know that the types of quote you use in your JSON are significant to jq. Type this in, and think about how it is different to the ‘non-broken’ json above.

$ echo "['user1': 'alice', 'user2': 'bob']" > json_array_broken
$ jq '.' json_array_broken

What You Learned

• The ‘dot’ filter
• How to select the value of a name from an object
• The various allowed forms for performing the selection
• The syntactic difference between an object, an array, and a name-value pair
• The types of quote are important

Exercises

1) Create another json_array file as per this section, but this time with two JSON objects (ie the same line twice). Re-run the queries above and see what happens to demonstrate that jq works on a stream of JSON objects.

If you like this, you might like one of my books:
Learn Bash the Hard Way
Learn Git the Hard Way
Learn Terraform the Hard Way