Unwind
UNWIND
UNWIND
is a function that takes a list input and produces a row for each value in the list. UNWIND
has many applications, and is particularly useful when you want to produce a value for each item in a list of values. Using UNWIND
can ease downstream ETL pipelines, as well as provide insights that would be otherwise cumbersome to evaluate.
FIND
aws_instance as i
RETURN
UNWIND(i._integrationClass) as class
COUNT(i) as class_count
This query retrieves all aws_instance
s and then unwinds each return value's _integrationClass
property, which is usually a list, into a discrete row. Each row is then counted and aggregated, effectively returning the total count of distinct _integrationClass
values on all aws_instance
s.
The query would return something like
class | class_count |
---|---|
Infrastructure | 237 |
CSP | 237 |
Another powerful UNWIND
query is
FIND
* as a
RETURN
UNWIND(a._type) as type,
UNWIND(a._class) as class,
COUNT(a) as total
ORDER BY total
This query retrieves all _type
and _class
values for everything, splits them into their own rows, and then counts the total of unique combinations of _type
and _class
.
The (shortened) returned value would look something like
type | class | total |
---|---|---|
aws_ecr_image | Image | 450111 |
aws_macie_finding | Finding | 320428 |
unified_entity | Person | 129009 |
unified_entity | UnifiedIdentity | 129009 |
jira_user | User | 121137 |
jira_issue | Issue | 48760 |
This query without the unwinds would produce results that look similar at first glance, but further inspection will show that there are results that are not unwound, for instance:
type | class | total |
---|---|---|
aws_ebs_volume | DataStore,Disk | 538 |
Unwinding a query that filters for the aws_ebs_volume
type gives:
type | class | total |
---|---|---|
aws_ebs_volume | DataStore | 538 |
aws_ebs_volume | Disk | 538 |
UNWIND caveats
Multiple UNWIND statements
Each unwind is combined with the result of each other unwind, effectively producing a list comprehension. Unwinding a list x
and list y
will produce len(x) * len(y)
rows; x
, y
, z
produces len(x) * len(y) * len(z)
; and so on.
Usage inside other functions
Currently, J1QL does not support nesting an UNWIND
inside other functions, like COUNT
or MERGE
.