Skip to main content

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.

Example using UNWIND
FIND
aws_instance as i
RETURN
UNWIND(i._integrationClass) as class
COUNT(i) as class_count

This query retrieves all aws_instances 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_instances.

The query would return something like

classclass_count
Infrastructure237
CSP237

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

typeclasstotal
aws_ecr_imageImage450111
aws_macie_findingFinding320428
unified_entityPerson129009
unified_entityUnifiedIdentity129009
jira_userUser121137
jira_issueIssue48760

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:

typeclasstotal
aws_ebs_volumeDataStore,Disk538

Unwinding a query that filters for the aws_ebs_volume type gives:

typeclasstotal
aws_ebs_volumeDataStore538
aws_ebs_volumeDisk538

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.