WITH can perform functional operations on the data stream and pass the result onto later statements. When the input columns are heterologous, their data streams will be Cartesian multiplied.

Syntax: WITH <expression> as <alias>, <expression> as <alias>, ... Input:

• <expression>: Operational expression
• <alias>: Alias of operational result, optional

Deduplication of an alias in WITH affects its homologous aliases.

find().nodes() as n1 limit 2
n(3).e()[2].n(as n2) as path
with pnodes(path) as array, distinct(n2.color) as colors
with n1, array
return n1, array, colors

In the UQL above, the first WITH conducts function and deduplication operations to paths and n2, they and the acquired array and colors are all homologous aliases; the second WITH conducts Cartesian multiplication to the data streams where heterologous aliases n1 and array belong to.

Single Alias

Example: Find the card with the highest level

find().nodes({@card}) as n1
with max(n1.level) as maxLevel
find().nodes({@card.level == maxLevel}) as n2
return n2{*}

Heterologous Aliases

Example: Cartesian multiple columns 1, 2, 3 with columns 4 and 5, return in table

uncollect [1,2,3] as a1
uncollect [4,5] as a2
with a1, a2
return table(a1, a2)

Example: find customers who own cards CA001, CA002, and CA003, then find products belonging to category book, return the purchase and view paths from these customers to products

khop().n({_id in ["CA001","CA002","CA003"]}).e({@has}).n({@customer}) as n1
n({@prodCAT.name == "book"}).e().n({@product} as n2)
with distinct(n1)
with n1, n2
n(n1).e({@buy || @view}).n(n2) as p
return p{*}

Analysis: when deduplicating users of multiple cards: WITH does not accept inputting non-homologous columns while using distinct() for deduplication; in this example, the effect from a cartesian multiplication on n1 and n2 using WITH is similar to intergroup networking from AUTONET, as shown below:

khop().n({_id in ["CA001","CA002","CA003"]}).e({@has}).n({@customer}) as n1
n({@prodCAT.name == "book"}).e().n({@product} as n2)
with collect(distinct(n1)) as a1
with a1, collect(n2) as a2
autonet().src(a1).dest(a2).depth(1) as p
return p{*}

Complex Example using WITH

Example: find cards that transfer to CA021, CA022, and CA029, from which find the cards at the highest levels, combine Card IDs in arrays, map them with payee cards and return

n({_id in ["CA021","CA022","CA029"]} as payee).le({@transfer}).n({@card} as payer) 
with payee, payer
group by payee
with max(payer.level) as maxLevel
n(payee).le({@transfer}).n({@card.level == maxLevel} as topPayer)
group by payee
return payee, collect(topPayer)

Analysis: the first WITH functions to prolong the statement cycles of alias payee and payer in Ultipa engine v4.0; when using Ultipa engine v4.1, this clause can be omitted.