Reformatting data with the jq Plugin

jq is a tool for searching json data and reformatting it into various output formats. It can be used to filter, reformat, and extract data from within a json object. Depending on how you structure your query, it can output valid json or simple text. jq is someplace between a query language and a template language - it incorporates elements of both with templated output but also with select-style filtering.

Perhaps an example is the best place to get started:

Sometimes we just need the same data in a new format. For example, if a plugin returns something like this:

{
    "results": [
        {
            "key": "greeting",
            "value": "Howdy!"
        },
        {
            "key": "question",
            "value": "How is your day?"
        },
        {
            "key": "answer",
            "value": "I've got a bad case of the Mondays!"
        }
    ]
}

but what we need is an array of the keys like this:

[
  "greeting",
  "question",
  "answer"
]

we can use the following jq string to generate our result:
[.results[].key]

You can think of jq variable references as paths to a variable in our json object. Just like DNS, the “top” level is represented by a .. In this example, we’re looking for all the key values in our results array. We’ve surrounded our query with [ and ] so our output will be a json array.

As you can see, we have elements of templating (we’ve created an array by surrounding our query in brackets) but we also have elements of a query since we’re functionally running a select key from our results object.

Let’s take this a step farther - let’s say we now want to extract the answer from our object. We could do this by looping through our results array, checking to see if our key = answer and return the value. This can be time consuming, especially if we have hundreds of elements in our array. With jq we can do this in a single step:

.results[]|select ((.key|ascii_downcase)=="answer")|.value

which creates a simple string containing our “answer”.

We can turn it into json like this:
.results[]|select ((.key|ascii_downcase)=="answer")|{"answer":.value}
which produces

{
  "answer": "I've got a bad case of the Mondays!"
}

In this example, we’ve leveraged a select statement and we’ve converted our string to lower-case so we don’t have to worry about a key coming through in mixed case. The pipe operator (|) takes the output from the previous statement as the input to the current one. You can think of it as creating a new json object. If you’re familiar with powershell and piping objects from commandlet to commandlet, this should be a familiar pattern.

Finally, in InsightConnect, the jq plugin produces a string. To consume the results in your workflow as json, you can just run the results through a type converter 'string to object` step.

2 Likes