How to query JSON data in the terminal
Tags: bash, tip, json
Published: 2020-11-24 17:12:43

---
title: How to query JSON data in the terminal
published: true 
tags: bash, tip, json
---

Today we will be using `jq` to work with JSON data, you should be able to get it [here](https://stedolan.github.io/jq/).

## tl;dr

Return top-level keys from multiple json files in present working directory 

``` bash
jq -rs 'reduce .[] as $item ({}; . * $item) | keys[]' ./*.json
```

## Get the data

Go ahead and clone the assets if you want to work with this too.

``` bash
git clone https://github.com/davidmaceachern/playground-query-json.git
```

Navigate to where the JSON documents are.

``` bash
cd playground-query-json/assets/requests
```

Have a look at the JSON document names if you wish.

``` bash
ls
```

or

``` bash 
cat add_backend.json
```

Prints the following as the contents.

``` bash
{
  "id": "ID_TEST",
  "version": 0,
  "type": "PROXY",
  "data": {
    "type": "ADD_BACKEND",
    "data": {
      "app_id": "xxx",
      "backend_id": "xxx-0",
      "ip_address": "127.0.0.1",
      "port": 8080
    }
  }
}
```

## The problem

Ok so FYI this is test data for a project I am working on.

I need to decide on which properties I need the application to generate. The goal is to reduce them down as much as possible so the user only provides the items which they need to control and the application handles the rest.

At this stage, each of these files can be considered 1 single dataset.

``` bash
ls -1q *json | wc -l
```

If we run the above command we should find there are `16` files that match a '.json' ending.

## Combine all the documents into a single dataset

First we can print as we did using `cat` before but using `jq` instead:

``` bash
jq . add_backend.json
```

`jq` is the application we are invoking. The `.` represents the first argument `jq` needs which is a filter, or a thing we want to do to change our input, which right now is nothing. The third item is the filename which is our input.

Exactly the same but prettier:

```json
{
  "id": "ID_TEST",
  "version": 0,
  "type": "PROXY",
  "data": {
    "type": "ADD_BACKEND",
    "data": {
      "app_id": "xxx",
      "backend_id": "xxx-0",
      "ip_address": "127.0.0.1",
      "port": 8080
    }
  }
}
```

Let's combine two files and see what that looks like.

``` bash
jq . add_backend.json remove_backend.json
```

Which returns:

``` json
{
  "id": "ID_TEST",
  "version": 0,
  "type": "PROXY",
  "data": {
    "type": "ADD_BACKEND",
    "data": {
      "app_id": "xxx",
      "backend_id": "xxx-0",
      "ip_address": "127.0.0.1",
      "port": 8080
    }
  }
}
{
  "id": "ID_TEST",
  "version": 0,
  "type": "PROXY",
  "data": {
    "type": "REMOVE_BACKEND",
    "data": {
      "app_id": "xxx",
      "backend_id": "xxx-0",
      "ip_address": "127.0.0.1",
      "port": 8080
    }
  }
}
```

Ok great but we want to merge all the files together, along with handling nested
structures:

``` bash
jq -rs 'reduce .[] as $item ({}; . * $item)' ./*.json
```

Which returns:

```json
{
  "id": "ID_TEST",
  "version": 0,
  "type": "UPGRADE_MASTER",
  "data": {
    "type": "STATUS",
    "data": {
      "app_id": "xxx",
      "hostname": "yyy",
      "path_begin": "xxx",
      "fingerprint": "ab2618b674e15243fd02a5618c66509e4840ba60e7d64cebec84cdbfeceee0c5"
    },
    "path": "./config_dump.json"
  },
  "proxy_id": 0
}
```

We can output that result to a file to use later if we wish.

``` bash
jq -rs 'reduce .[] as $item ({}; . * $item)' ./*.json > ../aggregate.json
```

For now though I only care about keys, so inside the `jq` filter we can add a pipe
which only returns the keys.

``` bash
jq -rs 'reduce keys[] as $item ({}; . * $item) | keys[]' ./*.json
```

Which prints the unique top-level keys:

``` bash
data
id
proxy_id
type
version
```

Great, now I might encounter more issues with the JSON request format further into development, but for now a quick look at these keys is exactly what I
needed.

## So what?

Ok so, if we were actually writing a bash script this might be helpful, or using a language such as Python we can actually use `jq` queries inside our code using a package such as found [here](https://pypi.org/project/jq/).

`jq` is a great way to work with JSON data in the terminal, picking up knowledge of a couple of filters can come in handy. I've enjoyed using it in the past to explore JSON data and it can be handy for helping to make decisions. 

You can achieve similar things manually in VSCode, with JSON linting and copy/paste and find/replace but if it's a task you do over and over again it can be worthwhile to use tools like `jq`.

I'm writing a Javascript application, so I could have done this type of thing inside my unit tests, as Javascript is great when it comes to JSON, but I thought to share this approach today.

## References

- [Introducing JSON](https://www.json.org/json-en.html)
- [How to merge 2 json objects from two files using jq](https://stackoverflow.com/questions/19529688/how-to-merge-2-json-objects-from-2-files-using-jq)