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

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.