Change Password

Input error
Input error
Input error
Submit

Change Nickname

Current Nickname:
Submit
v4.0

Function

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.

Please complete the following information to download this book
*
公司名称不能为空
*
公司邮箱必须填写
*
你的名字必须填写
*
你的电话必须填写
*
你的电话必须填写