Format, Parameters

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

Syntax:

• Format: with <column1> as <alias1>, <column2> as <alias2>, ...
• Parameters: see table below
• Affected columns: <column> and all its homologous columns

When using Ultipa engine v4.0, aliases need to be passed by WITH so that they can be used by later statements far away from them; while Ultipa engine v4.0 does not require this operation.

Analysis

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 statement above, the first WITH conducts function and deduplication operations to homologous columns--paths and n2; the second WITH conducts Cartesian multiplication to the data streams where the two non-homologous columns belong to.

When WITH is deduplicating a column, its homologous columns will be deduplicated as well.

Column

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)

Analysis: 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{*}

Anaylsis: when deduplicating users of multiple cards: WITH does not accept inputting non-homologous columns while using distict() 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 Statement with 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.