The following jq expressions may be useful when working with the Run jq Command step. They allow you to transform JSON data without writing custom code. For additional examples, refer to the jq cheat sheet.
Filter for records
Filter all records in an array (e.g., from Microsoft Graph) that are older than 90 days, based on a specific field:
group_by(.userPrincipalName)
| map(.[] + {
"lastSignInDateEpoch": (
.[].signInActivity.lastSignInDateTime // empty
| fromdateiso8601 as $Epochdate
| $Epochdate
)
})
| .[]
| select(.lastSignInDateEpoch < {{ $.get_date.timestamp }})
Compare arrays by a common key
Compare two arrays using a shared key (e.g., email):
[[.[0] + .[1] | group_by(.email)[]]
| .[]
| select(length > 1)
| add]
Reduce arrays
Convert an array into an object using a specific field as the key:
reduce .[] as $i ({}; .[$i.description] = $i)
Merge data points into an array
Transform and enrich records with additional metadata:
.[] | {
"event": .,
"time": (
.timestamp
| scan("(.+?)([.][0-9]+)?Z$")
| [(.[0] + "Z" | fromdateiso8601), (.[1] // 0 | tonumber)]
| add
),
"index": "{{ $.set_workflow_variables.vars.splunk_index }}",
"source": "{{ $.set_workflow_variables.vars.splunk_source }}",
"host": "{{ $.set_workflow_variables.vars.splunk_host }}",
"sourcetype": "{{ $.set_workflow_variables.vars.splunk_sourcetype }}"
}
Delete keys from JSON
Remove a specific key from all objects without redefining the entire structure:
[.[] | del(.field3)]
Deduplicate multiple lists
Concatenate and deduplicate five lists in a single step:
[.[0] + .[1] + .[2] + .[3] + .[4]] | add | unique
Workflow
The following workflow demonstrates how to apply jq steps using the expressions above.