Query JSON documents in the Terminal with GROQ

Celý článek

JSON documents are everywhere today, but they are rarely structured the way you want them to be. They often include too much data, have weirdly named fields, or place the data in unnecessary nested objects. Graph-Relational Object Queries (GROQ) is a query language (like SQL, but different) which is designed to work directly on JSON documents. It basically lets you write queries you can quickly filter and then reformat JSON documents to get them into the most convenient shape.

GROQ was developed by Sanity.io (where it’s used as the primary query language). It’s open source and it gives us built-in ways to use it in JavaScript and the command line on any JSON source. Together, we’ll add GROQ to the terminal toolkit, which will save you time whenever you need to wrangle some JSON data on a poject.

Let’s install GROQ

Like most things, we need to install the GROQ CLI tool and can do so with with npm (or Yarn) in the terminal:

$ npm install -g groq-cli

In order to play with it, we need to have a JSON file available. We’ll use curl to download an example dataset of todo data:

$ curl -o todos.json https://jsonplaceholder.typicode.com/todos

Let’s take a quick look at a sample item in the data:

{
  "userId": 1,
  "id": 1,
  "title": "delectus aut autem",
  "completed": false
},

Pretty straightforward. We have a user ID, a todo item ID, a todo title and a boolean specifying whether the todo item is completed or not.

Now let’s run a basic GROQ query: finding all completed todos, but only return the todo titles and user IDs. It’s OK to copy/paste this line because we’ll walk through what it means in a bit.

$ cat todos.json | groq '*[completed == true]{title, userId}' --pretty

The groq command line tools accept a JSON document on standard input. This works very nicely with the Unix philosophy of “doing one thing and work on a text stream.” In order to read JSON from a file we’ll use the cat command. Also note that groq will output a minimal JSON on a single line by default, but by passing --pretty, we get a nicely indented and highlighted syntax.

To store the result, we can pipe it to a new file using >:

$ cat todos.json | groq '*[completed == true]{title, userId}' > result.json

The query itself consists of three parts:

• * refers to the dataset (i.e. the data in the JSON file).
• [completed == true] is a filter that removes items that are marked as incomplete.
• {title, userId} is a projection that causes the query to only return the "title" and "userId" properties.

Let’s warm up with some exercises

You probably didn’t think you’d need to exercise to get through this post! Well, the good news is that we’re only exercising the mind with a few things to try out with GROQ before we get into more details.

1. What happens if you remove [completed == true] and/or {title, userId}?
2. How can you change the query to find all todos by the user with an ID of 2?
3. How can you change the query to find uncompleted todos by the user with an ID of 2?
4. What happens if the filter in the original query example swaps places with the projection?
5. How would you write a single command (with pipes) that downloads the JSON and processes it with GROQ?

We’ll put the answers at the end of the post for you to reference.

Querying the Nobel prize winners

The todo data is nice for a warmup, but let’s be honest: It’s not very motivating to look at a list that uses Latin as placeholder content. However, the Nobel Prize has a dataset of all past laureates available to use publicly.

Here’s a sample return:

{
  "laureates": [
    {
      "id": "1",
      "firstname": "Wilhelm Conrad",
      "surname": "Röntgen",
      "born": "1845-03-27",
      "died": "1923-02-10",
      "bornCountry": "Prussia (now Germany)",
      "bornCountryCode": "DE",
      "bornCity": "Lennep (now Remscheid)",
      "diedCountry": "Germany",
      "diedCountryCode": "DE",
      "diedCity": "Munich",
      "gender": "male",
      "prizes": [...],
    },
    // ...
  ]
}

Ah! This is much more interesting! Let’s download the dataset and find the first name of all Norwegian laureates. Here, we’re going to use --output flag for curl to save the data to a file.

$ curl --output laureate.json http://api.nobelprize.org/v1/laureate.json
$ cat laureate.json | groq '*.laureates[bornCountryCode == "NO"]{firstname}' --pretty

What do you get back? I received 12 Norwegian Nobel laureates. Not bad!

Note that this query is not quite like the first query we wrote. We have an extra .laureates in this one. When we used * in the todo dataset, it represents the whole JSON document which is contained in an array at the top-level of the todo dataset. On the other hand, the laureate file uses an object at the top-level where the list of laureates is stored in the "laureates" property.

To access a specific item, we can use the filter [0] and return just the first name. That ought to tell us who the first Norwegian was to win a Nobel prize.

$ cat laureate.json | groq '*.laureates[bornCountryCode == "NO"]{firstname}[0]' --pretty

// Returned object
{
  "firstname": "Ivar"
}

More exercises!

We’d be remiss not to play around with this new dataset a bit to see how the queries work.

1. Write a query to find all Nobel laureates from your own country.
2. Write a query to return the last Norwegian laureate. Hint: -1 refers to the last item.
3. What happens if you try to filter directly on the root object? *[bornCountryCode == "NO"]?
4. What’s the difference between *.laureates\[bornCountryCode == "NO"\][0] and *.laureates\[0\][bornCountryCode == "NO"]?

Like last time, answers will be at the end of this post.

Working with filters

Now we know that in total there have been 12 Norwegian Nobel laureates, how many of them were born after 1950? That’s no problem figuring out with GROQ:

$ cat laureate.json | groq '*.laureates[bornCountryCode == "NO" && born >= "1950-01-01"]{firstname}' --pretty

// Sample return
[
  {
    "firstname": "May-Britt"
  },
  {
    "firstname": "Edvard I."
  }
]

In fact, GROQ has a rich set of operators we can use inside a filter. We can compare numbers and strings with equal (==), not equal (!=), greater than (>), greater than or equal ( >=), less than (<), and less than or equal (<=). Plus, comparisons can be combined with AND (&&), OR (||) and NOT (!). The in operator even makes it possible to check for many cases at once (e.g. bornCountryCode in ["NO", "SE", "DK"]). And the defined function allows us to see if a field exists (e.g. defined(diedCountry)).

Even more exercises!

You know the drill: try playing with filters a bit to see how they work with the dataset. Answers are at the end, of course.

1. Write a query that returns living laureates.
2. Is there a difference between the filters [bornCountryCode == "NO"][born >= "1950-01-01"] and [bornCountryCode == "NO" && born >= "1950-01-01"]?
3. Can you find all laureates that won a prize in 1973?

Working with projections

The Nobel Prize dataset separates the first name and the surname for each laureate, but what if we want to combine them together into one field? Projections in GROQ can do exactly that!

*.laureates[bornCountryCode == "NO" && born >= "1950-01-01"]{
  "name": firstname + " " + surname, 
  born,
  "prizeCount": count(prizes),
}

Running this query tells us that May-Britt Moser and Edvard Moser received one prize (which was, in fact, the same prize):

[
  {
    "name": "May-Britt Moser",
    "born": "1963-01-04",
    "prizeCount": 1
  },
  {
    "name": "Edvard I. Moser",
    "born": "1962-04-27",
    "prizeCount": 1
  }
]

What happened here? Well, when we write a projection in GROQ what we’re really writing is a JSON object. Previously, we had simple projections (like {firstname}), but this is a shortcut way of writing {"firstname": firstname}. By using the expanded object syntax, we can both rename keys and transform the values.

GROQ has a rich set of operators and functions for transforming data, including string concatenation, arithmetic operators (+, -, *, /, %, **), counting arrays (count(prizes)), and rounding numbers (round(num, <amount of decimals>).

$ cat laureate.json | groq --pretty '
{
  "count": count(*.laureates),
  "norwegians": *.laureates[bornCountryCode == "NO"]{firstname}, 
}
'

{
  "count": 928,
  "norwegians": [
    {
      "firstname": "Ivar"
    },
    {
      "firstname": "Lars"
    },
    …
  ]
}

*[userId == 2]

*[userId == 2 && completed == false] or *[userId == 2 && !completed]

curl https://jsonplaceholder.typicode.com/todos | groq '*[completed == true]{title, userId}' > result.json

*.laureates[bornCountryCode == "INSERT-YOUR-COUNTRY-HERE"]

*.laureates\[bornCountryCode == "NO"\][-1]

*.laureates[died == "0000-00-00"]

*.laureates["1973" in prizes[].year]

*.laureates[count(prizes) >= 2]

count(*.laureates[gender == "female"])

*.laureates{"fullname": surname + ", " + firstname}

*.laureates{"laureates": {firstname, "percentage": round(count(prizes) / count(*.laureates[].prizes), 3) * 100}, "total": count(*.laureates[].prizes)}