## Time Function

*datetime* in the content below represents both *datetime* and *timestamp*, the two data types of time.

### now() | System Time

System time function returns the current system time of *datetime* value.

Syntax: `now()`

Example: Insert an edge with transaction amount of 1000 from C001 to C002, and set the transaction time to the current system time

```
insert().into(@transaction)
.edges({_from: "C001", _to: "C002", amount: 1000, time: now()})
```

### date_add() | Time Shift

Time shift function returns the *datetime* value shifted from the original *datetime* value.

Syntax：`date_add(<date>, <number>, "<interval>")`

, details of parameters are as below

Name | Type | Specification | Description |
---|---|---|---|

date | datetime or timestamp | / | the orginal time value to shift |

number | int | / | the time offset |

interval | string | year, month, day, hour, minute, second, millisecond | the unit of the offset |

Example: Insert a transaction edge from C001 to C002 executed 5 hours ago

```
insert().into(@transaction)
.edges({_from: "C001", _to: "C002", time: date_add(now(), -5, "hour")})
```

Example: Defer the transaction time of edge TRX001 for 3 days, and check the updated edge

```
update().edges({no == "TRX001"})
.set({time: date_add(time, 3, "day")}) as edge
return edge{*}
```

Example：Defer the transaction time of edge TRX001 for 3 days and shift earlier by 5 hours, and check the updated edge

```
update().edges({no == "TRX001"})
.set({time: date_add(date_add(time, 3, "day"), -5, "hour")}) as edges
return edges{*}
```

### date_diff() | Time Difference

Time difference function calculates the length of time between two *datetime* values and returns that length (take the integer part).

Syntax: `date_diff(<start_datetime>, <end_datetime>, "<interval>")`

, details of parameters are as below

Name | Type | Specification | Description |
---|---|---|---|

start_datetime | datetime or timestamp | / | start of the length |

end_datetime | datetime or timestamp | / | end of the length |

interval | string | day, hour, minute, second | the unit of the returned value |

Start time is the minuend and end time is the subtrahend, i.e. the difference is calculated by

`start_datetime`

-`end_datetime`

.

Example: Calculate the number of days C001 has been activated

```
find().nodes({_id == "C001"})
return date_diff(now(), nodes.time, "day")
```

Example: Find owners of cards opened within 3 days after the acivation of its account, return these accounts

```
n({@account} as users).re({@own})
.n({@card && date_diff(time, users.time, "day") <= 3})
return distinct(users)
```

### year() | Year

Year function extracts the year of a *datetime* value and returns that integer.

Syntax: `year(<datetime>)`

Example: Return the year of "2020-01-02 00:05:32"

```
return year("2020-01-02 00:05:32") as yearInt
```

Example: Find 10 accounts opened in year 2021

```
find().nodes({year(@account.time) == 2021})
return nodes{*} limit 10
```

### month() | Month

Month function extracts the month of a *datetime* value and returns that integer.

Syntax: `month(<datetime>)`

Example: Return the month of "2020-01-02 00:05:32"

```
return month("2020-01-02 00:05:32") as monthInt
```

Example: Find 10 accounts opened in May 2021

```
find().nodes({year(@account.time) == 2021 && month(time) == 5})
return nodes{*} limit 10
```

### day() | Day of Month

Day of month function extracts the day of month of a *datetime* value and returns that integer.

Syntax: `day(<datetime>)`

Example: Return the day of month of "2020-01-02 00:05:32"

```
return day("2020-01-02 00:05:32") as dayOfMonth
```

Example: Find 50 accounts opened on 10th in any month of 2021

```
find().nodes({year(@account.time) == 2021 && day(time) == 10})
return nodes{*} limit 50
```

### day_of_week() | Day of Week

Day of week function calculates the day of week of a *datetime* value and returns that integer, and the correspondence between them is as follows:

Name | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
---|---|---|---|---|---|---|---|

Number | 1 | 2 | 3 | 4 | 5 | 6 | 7 |

Syntax: `day_of_week(<datetime>)`

Example: Return the day of week of "2020-01-02 00:05:32"

```
return day_of_week("2020-01-02 00:05:32") as dayOfWeek
```

Example: Calculate the actual payday of each month in 2022, given that the standard payday is 15th each month and will be postponed to the next Monday in case of non working days

```
uncollect ["2022-1-15 0:0:0","2022-2-15 0:0:0","2022-3-15 0:0:0","2022-4-15 0:0:0","2022-5-15 0:0:0","2022-6-15 0:0:0","2022-7-15 0:0:0","2022-8-15 0:0:0","2022-9-15 0:0:0","2022-10-15 0:0:0","2022-11-15 0:0:0","2022-12-15 0:0:0"] as dates with dates
with case day_of_week(dates)
when 1 then 1
when 7 then 2
else 0 end as offset
return date_add(dates, offset, "day")
```

Example: Find 100 accounts that transfer out via bank cards only on non working days (Saturday, Sunday)

```
n({@account} as buyer).e().n({@card}).re({@transaction} as buy).n()
with buyer, buy
group by buyer
with collect(distinct(day_of_week(buy.time))) as array
where (1 in array || 7 in array) && 2 nin array && 3 nin array && 4 nin array && 5 nin array && 6 nin array
return buyer{*} limit 100
```

## String Function

### lower() | Lowercase

Lowercase function converts all letters in a string to lowercase, leave other characters unchanged, and returns the new string.

Syntax: `lower(<string>)`

Example: Find 10 *@article* nodes, and return their titles in lowercase

```
find().nodes(@article) as nodes
return lower(nodes.title) limit 10
```

### upper() | Uppercase

Uppercase function converts all letters in a string to uppercase, leave other characters unchanged, and returns the new string.

Syntax: `upper(<string>)`

Example: Find 10 *@article* nodes, and return their titles in uppercase

```
find().nodes(@article) as nodes
return upper(nodes.title) limit 10
```

## Array Function

### intersection() | Array Intersection

Array intersection function calculates the common elements of two arrays and returns them as *array*, i.e. returns the intersection of the two arrays (repeated elements are allowed within the arrays).

Syntax: `intersection(<array1>, <array2>)`

Example: Find the common neighbors of node 1 and 2, and return as *array*

```
khop().src({_uuid == 1}).depth(1) as nodes1
khop().src({_uuid == 2}).depth(1) as nodes2
with collect(nodes1) as neighbors1, collect(nodes2) as neighbors2,
return intersection(neighbors1, neighbors2)
```

### difference() | Array Difference

Array difference function calculates the elements exist in the first array but not in the second array, and returns them as *array* (repeated elements are allowed within the arrays).

Syntax: `difference(<array1>, <array2>)`

Example: Find the neighbors of node 1, and remove the nodes that are neighbors of node 2, and return as *array*

```
khop().src({_uuid == 1}).depth(1) as nodes1
khop().src({_uuid == 2}).depth(1) as nodes2
with collect(nodes1) as neighbors1, collect(nodes2) as neighbors2,
return difference(neighbors1, neighbors2)
```

### size() | Array Size

Array size function calculates the size of the array, i.e. the number of elements in the array.

Syntax: `size(<array>)`

Example: Calculate the number of the common neighbors of node 1 and 2

```
khop().src({_uuid == 1}).depth(1) as nodes1
khop().src({_uuid == 2}).depth(1) as nodes2
with collect(nodes1) as neighbors1, collect(nodes2) as neighbors2,
return size(intersection(neighbors1, neighbors2))
```

## Mathematical Functions

### pi() | Pi

Pi function returns the value of `PI`

.

Syntax: `pi()`

Example: Find 10 *@angle* nodes, and return the angle values of their property *radian*

```
find().nodes({@angle}) as nodes
return nodes.radian * 180 / pi() limit 10
```

### pow() | Power

Power function performs power operation on numeric values.

Syntax: `pow(<base>, <exponent>)`

, details of parameters are as below

Name | Type | Specification | Description |
---|---|---|---|

base | number | / | Base |

exponent | number | / | Exponent |

Please refer to the square root function for the example.

### sqrt() | Square Root

Square root function calculates the square root of numeric values, the input and output is `[0, +∞]`

.

Syntax: `sqrt(<number>)`

Example: Find 10 *@point* nodes, and calculate their distances from the origin of the planar coordinate sytem (0,0) using their x and y coorinates (property *axisX* and *asixY*)

```
find().nodes({@point}) as nodes
return sqrt(pow(nodes.axisX, 2) + pow(nodes.axisY, 2)) limit 10
```

### abs() | Absolute Value

Absolute value function calculates the absolute value of numeric values.

Syntax: `abs(<number>)`

Example: Find 10 *@project* nodes, and return their IDs and evaluations; return 0 if the evaluation is negative

```
find().nodes({@projects}) as nodes
return nodes._id, (nodes.evalution + abs(nodes.evalution))/2 limit 10
```

### floor() | Round Down

Round down function returns the largest integer up to a certain numeric value.

Syntax: `floor(<number>)`

Example: Find 10 *@project* nodes, and return their evaluations after rounding down

```
find().nodes({@projects}) as nodes
return floor(nodes.evalution) limit 10
```

### ceil() | Round Up

Round up function returns the smallest integer that is no less than a certain numeric value.

Syntax: `ceil(<number>)`

Example: Find 10 *@project* nodes, and return their evaluations after rounding up

```
find().nodes({@projects}) as nodes
return ceil(nodes.evalution) limit 10
```

### round() | Round

Round function returns the nearest integer to a certain numeric value; when there are two nearest integers, takes the larger one.

Syntax: `round(<number>)`

Example: Find 10 *@project* nodes, and return their evaluations after rounding

```
find().nodes({@projects}) as nodes
return round(nodes.evalution) limit 10
```

## Trigonometric Function

### sin() | Sine

Sine function calculates the sine value of the radian, the input is the value of radian, and the output is `[-1,1]`

.

Syntax: `sin(<radian>)`

Example: Find 10 *@angle* nodes, and calculate the sine value of their property *radian*

```
find().nodes({@angle}) as nodes
return sin(nodes.radian) limit 10
```

### cos() | Cosine

Cosine function calculates the cosine value of the radian, the input is the value of radian, and the output is `[-1,1]`

.

Syntax: `cos(<radian>)`

Example: Find 10 *@angle* nodes, and calculate the cosine value of their property *radian*

```
find().nodes({@angle}) as nodes
return cos(nodes.radian) limit 10
```

### tan() | Tangent

Tangent function calculates the tangent value of the radian, the input is the value of radian, and the output is `(-∞, +∞)`

.

Syntax: `tan(<radian>)`

Example: Find 10 *@angle* nodes, and calculate the tangent value of their property *radian*

```
find().nodes({@angle}) as nodes
return tan(nodes.radian) limit 10
```

### cot() | Cotangent

Cotangent function calculates the cotangent value of the radian, the input is the value of radian, and the output is `(-∞, +∞)`

.

Syntax: `cot(<radian>)`

Example: Find 10 *@angle* nodes, and calculate the cotangent value of their property *radian*

```
find().nodes({@angle}) as nodes
return cot(nodes.radian) limit 10
```

### asin() | Arcsine

Arcsine function calculates the radians of the sine value, the input is `[-1,1]`

, and the output is `[-PI/2, PI/2]`

.

Syntax: `asin(<sine>)`

Example: Find 10 *@rightTangle* nodes, and calculate the opposite angle of the right corner edge *a* from property *rightA* (right corner edge *a*) and *hypotenuse*

```
find().nodes({@rightTangle}) as nodes
return asin(nodes.rightA / nodes.hypotenuse) limit 10
```

### acos() | Arccosine

Arccosine function calculates the radians of the cosine value, the input is `[-1,1]`

, and the output is `[0, PI]`

.

Syntax: `acos(<cosine>)`

Example: Find 10 *@rightTangle* nodes, and calculate the angle between the right corner edge *a* and hypotenuse from property *rightA* (right corner edge *a*) and *hypotenuse*

```
find().nodes({@rightTangle}) as nodes
return acos(nodes.rightA / nodes.hypotenuse) limit 10
```

### atan() | Arctangent

Arctangent function calculates the radians of the tangent value, the input is `(-∞, +∞)`

, and the output is `[-PI/2, PI/2]`

.

Syntax: `atan(<tangent>)`

Example: Example: Find 10 *@rightTangle* nodes, and calculate the opposite angle of the right corner edge *a* from property *rightA* (right corner edge *a*) and *rightB* (right corner edge *b*)

```
find().nodes({@rightTangle}) as nodes
return atan(nodes.rightA / nodes.rightB) limit 10
```

## Path Function

### length() | Path Length

Path length function returns the length of the path, i.e. the number of edges in the path.

Syntax: `length(<path>)`

Example: Find transaction paths within 3 steps from C001 to C002, and return the shortest 10 paths

```
n({_id == "C001"}).e({@transaction})[:3].n({_id == "C002"}) as path
return path{*}{*} limit 10
order by length(path) ASC
```

### pnodes() | Path Nodes

Path nodes function puts the UUIDs of the nodes in a path one after the other and combines them into an array.

Syntax: `pnodes(<path>)`

Example: Find transaction paths within 3 steps from C001 to C002, and return 10 arrays after combining the nodes in every path

```
n({_id == "U001"}).e({@transaction})[:3].n({_id == "U002"}) as path
return pnodes(path) limit 10
```

### pedges() | Path Edges

Path edge function puts the UUIDs of the edges in a path one after the other and combines them into an array.

Syntax: `pedges(<path>)`

Example: Find transaction paths within 3 steps from C001 to C002, and return 10 arrays after combining the edges in every path

```
n({_id == "U001"}).e({@transaction})[:3].n({_id == "U002"}) as path
return pedges(path) limit 10
```

## Aggregation Operation Function (Non-mapped Class)

Aggregation operation functions calculate multiple elements of a data column into a single value (or array), hence the name "aggregation".

Syntax: **<function>(<parameter>)**

Aggregation functions supported by Ultipa:

Function Name | Function Performance | Params Format | Params Datatype | Result Format | Result Datatype |
---|---|---|---|---|---|

count | counting | list | any | single value | integer |

sum | summation | list | number | single value | number |

max | maximum value | list | number, time | single value | number, time |

min | minimum value | list | number, time | single value | number, time |

avg | average value | list | number | single value | decimal |

stddev | standard deviation | list | number | single value | decimal |

collect | collection | list | any | array | array |

### count() | Count

Count opertaion can count the number of nodes, edges, paths, property values or schemas, as long as it's a column of elmements, the quantity can be counted by `count()`

.

Example: Find cards held by account U001, count the paths, cards and card number

```
n({_id == "U001"}).re({@own}).n({@card} as cards) as paths
return count(paths), count(cards), count(cards._id)
```

### sum() | Summation

Example: Sum the balance of cards held by account U001

```
n({_id == "U001"}).re({@own}).n({@card} as cards)
return sum(cards.balance)
```

Example: Sum the balance of all cards

```
find().nodes({@card}) as cards
return sum(cards.balance) as totalBalance
```

Example: Calculate the total stock sales of product P001, that is the sum of *stock*price* for each product SKU

```
n({_id == "P001"}).re({@own}).n({@SKU} as SKUs)
return sum(SKUs.stock * SKUs.price)
```

### max() | Maximum Value

Example: Find the maximum balance of cards held by account U001

```
n({_id == "U001"}).re({@own}).n({@card} as cards)
return max(cards.balance)
```

### min() | Minimum Value

Example: Find the minimum balance of cards held by account U001

```
n({_id == "U001"}).re({@own}).n({@card} as cards)
return min(cards.balance)
```

### avg() | Average Value

Example: Calculate the average balance of cards held by account U001

```
n({_id == "U001"}).re({@own}).n({@card} as cards)
return avg(cards.balance)
```

### stddev() | Standard Deviation

Example: Calculate the standard deviation of the monthly ROI (Return of Investment) rate of product I001, given that the rate is a property of schema *monthReport*

```
n({_id == "I001"}).re().n({@monthReport} as reports)
return stddev(reports.rate)
```

### collect() | Collection

Collection opertaion can aggreate a column of nodes, edges, paths, property values or schemas into an array in the original order, as long as it's a column of elmements, it can be converted into an array by `collect()`

.

Take the graph below as an example, schemas of nodes are `@square`

and `@round`

, both with property *color*:

Type | UQL Example |
Returned Result |
Array Element |
---|---|---|---|

Node | find().nodes() as n with collect(n) as a return a |
[11, 12, 13, 14, 15] | UUID the unique identifier of node |

Edge | find().edges() as e with collect(e) as a return a |
[21, 22, 23, 24, 25] | UUID the unique identifier of edge |

Path | n().re().n() as p with collect(p) as a return a |
[1, 2, 3, 4, 5] | Path itself doesn't have an unique identifier, number 1, 2, 3, 4 and 5 here only represents that the five paths that queried are distinct from each other |

Property | find().nodes() as n with collect(n.color) as a return a |
[yellow, blue, purple, red, green] | Value of property |

Schema | find().nodes() as n with collect(n.@) as a return a |
[round, round, square, round, square] | Name of schema |

Example: Get the array of cards reached via 2-step transction from card C001

```
n({_id == "C001"} as C001).re({@transaction})[2].n({@card} as cards)
return C001, collect(cards) as cardArray
```

## distinct() | Deduplication Function (Non-mapped Class)

Deduplication operation can deduplicate a column of nodes, edges or numeric values, and return the deduplicated data column. The basis of the deduplication of nodes and edges is the UUID.

Deduplication operation can be used either alone or as the parameter of aggregation operation function.

The direct returned paths, nodes or edges of UQL query statement is already deduplicated and no need to use

`distinct()`

redundantly.

Example: Deduplicate and return cards reached via 2-step transaction from C001

```
n({_id == "C001"} as C001).re({@transaction})[2].n({@card} as cards)
return distinct(cards)
```

Example: Deduplicate cards out reached by 2-step transaction from C001, return the array of card numbers and the number of cards

```
n({_id == "C001"} as C001).re({@transaction})[2].n({@card} as cards)
return collect(distinct(cards._id)), count(distinct(cards))
```

## CASE | Mapping Function

CASE is a user-defined function which maps every row of data in a data stream to a new value, and all the new values have the same structure type.

About the structure type of the mapped result:

- The accepted structure types are: ATTR, ARRAY, TABLE;
- Constant (such as a number or a string) is regarded as ATTR;
- When using NODE or EDGE, the unqiue identifier UUID of node or edge is used by default, i.e. they are regarded as ATTR;
- When using PATH, path itself doesn't have an unique identifier, numbers counted from 1 are used to identify distinct paths (such as 1,2,3,...), i.e. it's regarded as ATTR.

CASE needs to be used with multiple keywords:

- WHEN
- THEN
- ELSE (Optional)
- END

Syntax:

**CASE
when <condition1> then <projection1>
when <condition2> then <projection2>
...
else? <projectionN?>
END**

where:

- Judgement condition
`<condition>`

and mapping statement`<projection>`

is made up of alias reference (and its non-aggregate operations or numerical operations) or a constant in the data stream to be mapped. *CASE*matches each row of data from the first condition, and outputs the corresponding mapping result once the matching condition is met, and then skips to*END*in the end.*ELSE*is not mandatory, if there is no*ELSE*and the data doesn't meet any condition, the system returns null or empty string by default.

If only the conditional operator `==`

is used in `<condition>`

, and one side of `==`

can be written as a statement with a fixed form, then that statement can be put in advance after *CASE* while the statement on the other side stays after *WHEN*, thus obtaining a simplified *CASE* syntax:

Example: Divide *@people* nodes into 4 age groups, and count the number of people in each age group

```
find().nodes({@people}) as nodes
with CASE
when nodes.age < 15 then "child"
when nodes.age < 35 then "youth"
when nodes.age < 60 then "middle-age"
else "old" END as category
group by category
return category, count(category)
```

Example: Check the task completion of user UUID = 1, make the tasks which completion "*task process * task weight < 30*" invalid, then sum the completion of all valid tasks. Task process is saved in edge property *@execute.process*, task weight is save in node property *@task.weight*.

```
n({_uuid == 1}).re({@execute} as executes).n({@task} as tasks)
with CASE
when executes.process * tasks.weight >= 30 then 1
else 0 END as valid, executes, tasks
return sum(valid * executes.process * tasks.weight)
```

Example: Simplify the above example, map the original data list to list of valid task completion, then sum it

```
n({_uuid == 1}).re({@execute} as executes).n({@task} as tasks)
with CASE
when executes.process * tasks.weight >= 30 then executes.process * tasks.weight
else 0 END as completion
return sum(completion)
```

Example: Map each user level to a new label and return it in the same table

```
find().nodes({@account}) as nodes
with CASE nodes.level
when 1 then "Tin"
when 2 then "Copper"
when 3 then "Silver"
when 4 then "Gold"
else "Platinum" END as tag, nodes
return table(nodes._uuid, nodes.level, tag)
```

Example: Find out winners of the 1st class scholarship from the students, and calculate for each winner, the courses of 5 credits and above and the winner passed with a score of 90 and above. Return the winner's name, [[course's name, score], [course's name, score], ...]; or return the winner's name and [[null]] if the winner hasn't passed any course of 5 credits and above with a score of 90 and above.

```
n({@student} as winner).e({@win}).n({@scholarship.level == 1})
with winner
optional n(winner).e({@takeExam.score >= 90}).n({@course.credit >= 5} as course)
group by winner
with winner, collect(
CASE course
when 0 then ["null"]
else [course.name, takeExam.score]
END) as courseArray
return winner.name, courseArray
```

Analysis: 1) `optional`

is valid for each execution of the template query following it, namely any "winner" that has no query result will generates a pseudo path "0-0-0". 2) When sifting pseudo path "0-0-0" through function CASE, inspect whether the value of edge or the end-node of the path is 0. Do NOT inspect the value of "winner" since it is the input of the template query, and winner is always non-zero. 3) To keep all the mapping results in an unified structure type, mapping "0-0-0" to array ["null"] other than string "null".

## table() | Table Function

Table function can combine multiple separate columns of data into one table. In principle, the data in the same row of columns to be merged should have some corresponding relationship.

Syntax: `table(<column>, <column>, ...)`

Example: Find all transactions from cards at level 1 to cards at level 5, return the payer number, payee number and transfer amount as a table

```
n({@card.level == 1} as payer).re({@transaction} as transfer).n({@card.level == 5} as payee)
return table(payer._id, payee._id, transfer.amount) limit -1
```

Example: Count the number of cards at each level, and return the first 3 rows in the table

```
find().nodes({@card}) as cards
group by cards.level as cardLevel
return table(cardLevel, count(cards)) limit 3
```

Example: Count the number of cards at each level, and return the 3 rows with the highest number of cards in the table

```
find().nodes({@card}) as cards
group by cards.level as cardLevel
with cardLevel, count(cards) as count
return table(cardLevel, count(cards)) limit 3
order by count desc
```

Note: Rows in the table can't be sorted directly, so user should sort the original data stream before combining them into table. In this example, although the sorting cluase `order by`

is written after `return`

clause which contains `table()`

function, `order by`

is executed before `return`

.