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

Somehow when running a jq query on the data below (which is only a subset of the larger JSON) to extract and output only an array of IDs, I receive errors.
The jq query is set to .[].id with no flags. the expected output should be an array of strings. The error in this particular case is: ‘action input JSON was invalid’. The input is an array of objects as seen below.

I have the same issue when extracting specific keys from objects using a similar filter. The error received is not always the same, however, if I apply the same filter is a jq query tester online, using the same exact JSON being fed to the plugin, the output array does process perfectly.

Any ideas why?

Here is a sample of this data.

[
{
“alerts”: [
{
“first_event_time”: “2021-09-14T19:00:28Z”,
“type”: “Account Visits Suspicious Link”,
“type_description”: “A user has accessed a link URL on a tracked threat list.”
}
],
“created_time”: “2021-09-14T19:01:42.265Z”,
“id”: “132e6981-f127-49e3-8de6-d61ab4794e68”,
“source”: “ALERT”,
“status”: “CLOSED”,
“title”: “Account ??? visited a suspicious link”
},
{
“alerts”: [
{
“first_event_time”: “2021-09-14T15:27:06.037Z”,
“type”: “Spear Phishing URL Detected”,
“type_description”: “A user visited a potential phishing domain.”
}
],
“created_time”: “2021-09-14T15:29:02.118Z”,
“id”: “07131738-aff6-4be6-8b2b-d854c3f05d28”,
“source”: “ALERT”,
“status”: “CLOSED”,
“title”: “Domain luxatiainternational.com, a look-alike of owned domain ???, was visited by 3 accounts/assets”
}
]