ARANGODB Advanced Operations

Original: Arangodb high-level operations

The following advanced operations are described below: for: All elements of an iterative group. Return: Generate query results. FILTER: Limits the results to elements that match any logical criteria. Sort: Forces a series of intermediate results that have already been generated. LIMIT: Reduces the number of elements in the result to the specified number, optionally skipping elements (pagination). Let: Assign any value to a variable. COLLECT: Group by one or more groups of standard pairs. can also be counted and aggregated. Remove: Deletes a document from the collection. Update: The document in the collection is partially updated. Replace: Completely replaces the document in the collection. Insert: Inserts a new document into the collection. UPSERT: Update/Replace an existing document, or create it if it does not exist. With: Specifies the collection that is used in the query (only when the query starts).
=====================================================================================
for

The FOR keyword can be all elements of an iterative group. The general syntax is:

For variableName in expression

There is also a special variant of the graph traversal:

For Vertexvariablename, Edgevariablename, pathvariablename in Traversalexpression

For this particular case, see the Graph Traversal section. For all other situations, please read:

Each array element returned by an expression is accessed once. An expression is required to return an array in all cases. An empty array is also allowed. The current array element can be used for further processing in the variable specified by VariableName.

For u in the users return
  u

This traverses all the elements of the array user (note: In this case, the array consists of all the documents of the collection named "Users") and makes the current array element in the variable u available. You did not modify in this example, but are pushed to the result using the return keyword only.

Note: When iterating over an array based on a collection, the order of the documents is undefined unless you define an explicit sort order by using the sort statement.

By introducing the variable for available until the scope for is placed in the closed.

Another example that uses a statically declared array of values to iterate through:

For year in [2013] return
  {' Year ': Year, ' isleapyear ': Year% 4 = 0 && (year%!= 0 | | y Ear% 400 = 0)}

Multiple for statements are also allowed to be nested. When a for statement is nested, a cross product of the array elements returned by each for statement is created.

For you in the users
  for L in locations return
    {"User": U, "location": l}

In this example, there are two array iterations: an external iteration of the array user plus an internal iteration of the array position. The internal array iterates through the elements in the outer array multiple times. For each iteration, the current value of the user and location can be used for further processing in the variables U and L.

===================================================================================== return

The return statement can be used to generate the results of the query. You must specify a return statement at the end of each block in the data selection query, or the query result will be undefined. It is optional to use return at the primary level of a data modification query.

The general syntax for return is:

return expression

The expression returned by returning the return statement generated in the block is placed in, which means that one of the result returns statements is always an array. If no document matches the query and a return value returns an element as an array, an empty array is included.

To return all elements from an array of the current iteration without modification, you can use the following simple form:

For variableName in Expression return
  VariableName

Because return allows you to specify an expression, you can perform arbitrary calculations to compute the result element. Any variables that are valid in the scope of the return placement are available for calculation.

To traverse all documents called a user's collection and return the complete document, you can write:

For u in the users return
  u

In each iteration of the for loop, the document for the user collection is assigned to the variable U, and the return is not modified in this example. To return only one property of each document, you can use a different return expression:

For u in the users return
  u.name

Or to return multiple properties, an object can be constructed like this:

  for u in the users return {name:u.name, age:u.age}

Note: Return closes the current scope and eliminates all local variables. It is important to keep this in mind when you use subqueries.

Dynamic property names are also supported:

For u in users return
  {[u._id]: U.age}

In this example, the document _ID for each user is used as an expression to compute the property key:

[
  {"
    users/9883":}
  ,
  {
    "users/9915": "
  },
  {
    " users/10074 ":"
  }
]

Results Each user contains an object, each with a single key/value pair. This is usually not required. For a single object, mapping the user ID to age requires merging the results and returning to the other object return:

Return MERGE (for
  u in Users return
    {[u._id]: U.age}
)

[
  {
    "users/10074":, "
    users/9883": ","
    users/9915 ":"
  }
]

Keep in mind that if the key expression evaluates the same value multiple times, only one key/value pair with duplicate names can survive in the merge (). To avoid this, you can use a static name instead of a dynamic property name and return all document properties as property values:

  for u in the users return {name:u.name, age:u.age}

[
  {
    ' name ': ' John Smith ',
    ' age ':
  },
  {
    ' name ': ' James Hendrix ', ' Age
    ':
  },
  {
    "name": "Katie Foster",
    "Age":
  }
]

return list

Because Arangodb 2.7,return can be followed by the DISTINCT keyword. The DISTINCT keyword will ensure a unique return statement for the returned value:

For variableName in expression return
  DISTINCT expression

If distinct is applied to an expression that is itself an array or subquery, distinct does not make the value in each array or subquery unique, but ensures that the result contains only a different array or subquery result. To make the results of an array or subquery unique, you only need to apply distinct to a group or subquery.

For example, the following query applies distinct to its subquery results, but is not in the subquery:

For what in 1..2 return
  DISTINCT (for
    i in [1, 2, 3, 4, 1, 3] return 
      i
  )

Here we will have a two-iteration for loop in which each loop executes a subquery. The distinct here is applied to two subquery results. Two subqueries return the same result value (that is, [1,2,3,4,1,3]), so after distinct, there is only one value [1,2,3,4,1,3] left:

[
  [1, 2, 3, 4, 1, 3]
]

If the goal is to apply distinct to the subquery, you need to move it to:

For what in 1..2 let
  sub = (for
    i in [1, 2, 3, 4, 1, 3] return 
      DISTINCT i
  ) return 
  Sub

In these cases, distinct makes the subquery result unique so that each subquery returns a unique array of values ([1,2,3,4]). Because the subquery executes two times, the top level does not have a distinct, and the array returns two times:

[
  [1, 2, 3, 4],
  [1, 2, 3, 4]
]

Note: The order of the results is not defined as return DISTINCT.

Note: If the top level of the query does not have a for loop, it is not allowed to return distinct.
=====================================================================================
FILTER

The filter statement can be used to restrict the elements of arbitrary logical conditions that match the results. General Grammar

FILTER condition

The condition must be a condition that evaluates to False or true. If the condition result is false, the current element is skipped, so no further processing is done, not part of the result. If the condition is true, the current element is not skipped and can be further processed. For a list of comparison operators, logical operators, and so on that you can use under conditions, see operators.

For you in users
  FILTER u.active = = True && u.age <-
  u

Allows you to specify multiple filter statements in a query, even in the same block. If multiple filter statements are used, their results are combined with the logical and, which means that all filter conditions containing an element must be true.

For u in users
  FILTER u.active = = True
  FILTER U.age < return
  u

In the above example, the user of all the array elements has the property activity value True and the attribute age and value less than 39 (including empty) will be included in the result. All other elements of the user are skipped and are not included in the return generated results. For a description of the effects of nonexistent or empty properties, refer to the chapter "Accessing data from a collection." Order of Operations

Note that the location of the filter statement may affect the results of the query. There are 16 active users in the test data, such as:

For u in users
  FILTER u.active = = True return
  u

We can limit the result set to 5 users:

For u in users
  FILTER u.active = = True
  LIMIT 5 return
  u

This may return the user documentation for Jim,diego,anthony,michael and Chloe. The returned is undefined because there is no sort statement to ensure a particular order. If we add a second filter statement to return only women ...

For u in users
  FILTER u.active = = True
  LIMIT 5
  Filter U.gender = = "F" return
  U

... It may simply return the Chloe document because the limit is applied before the second filter. Not more than 5 documents reached the second filter block, not all of which were in line with gender standards, although there were more than 5 active female users in the collection. You can get more definite results by adding a sort block:

For u in users
  FILTER u.active = = True
  SORT u.age ASC
  LIMIT 5
  FILTER U.gender = = "F" return
  U

This will return the user Mariah and Mary. If sorted by age in Desc order, the Sophia,emma and Madison documents are returned. A filter after a limit is not very common however, you may want such a query instead of:

For u in users
  FILTER u.active = = true and U.gender = = "F"
  SORT u.age ASC
  LIMIT 5 return
  u

The importance of placing a filter block allows this single keyword to assume a two SQL keyword where and a having role. Therefore, the AQL filter is used in conjunction with the Collect aggregation with any other intermediate result, document properties, and so on. =====================================================================================

SORT

The sort statement forces the array of intermediate results already produced in the current block to be sorted. Sort allows you to specify one or more sorting criteria and orientations. The general syntax is:

SORT Expression Direction

The specified direction is optional. The default (implicit) direction for sorting is ascending. To explicitly specify the sort direction, you can use the keyword ASC (ascending) and Desc. You can use commas to separate multiple sorting criteria.

Note: When iterating over an array based on a collection, the order of the documents is never defined unless you define an explicit sort order by using sort.

For you
  in the users SORT U.lastname, U.firstname, u.id DESC return
  u

Note that a constant sort expression can be used to indicate that a particular sort order is not required. During the optimization process, the AQL optimizer optimizes the constant sort expression, but if the optimizer does not need to consider any particular sort order, you can explicitly specify that they can be further optimized.

=====================================================================================



LIMIT

The limit statement allows slices to use an array of offset and count results. It reduces the number of elements in the result to the specified number. Follow the two general forms of limit:

LIMIT count
LIMIT offset, count

The first form allows you to specify only count values, while the second form allows you to specify offsets and count values. The first form is the same as the second form that uses an offset value of 0.

For u in the users
  LIMIT 5 return
  u

The above query returns the first five documents of the user collection. You can also write the same results as limit 0, 5. The actual returned file is fairly arbitrary, because no explicit sort order is specified. Therefore, you typically need a sort operation.

The offset value specifies that many elements from the result will be skipped. It must be 0 or greater. The meter specifies how many elements should be included in the result at most.

For you in the users
  SORT u.firstname, U.lastname, u.id DESC
  LIMIT 2, 5 return
  u

In the above example, the user's document is sorted, the first two results are skipped, and the next five user documents are returned.

Note that variables and expressions cannot be used for offsets and counts. Their values must be known at query compilation, which means you can only use numeric text and binding parameters.

If using limit is related to other operations in the query. In particular, the limit operation before the filter can significantly change the result, because the operations are executed in the order in which they are written in the query. For a detailed example, see Filter.


=====================================================================================


Let

The Let statement can be used for any value assigned to a variable. The variable is then introduced in the scope where the Let statement is placed.

The general syntax is:

Let variableName = Expression

Variables are immutable in AQL, which means they cannot be reassigned:

Let A = [1, 2, 3]  //initial assignment

a = Push (A, 4)     //syntax error, unexpected identifier let
a = push (A, 4)//Parsing error, variable ' is assigned multiple times let
B = PUSH (A, 4)//Allowed, Result: [1, 2, 3, 4]

A let statement is used primarily to declare complex computations and to avoid repeated computations of the same value in multiple parts of a query.

For u in users let
  numrecommendations = LENGTH (u.recommendations) return
  { 
    "user": U, 
    " Numrecommendations ": Numrecommendations, 
    " Ispoweruser ": Numrecommendations >= 
  

In the example above, the recommended quantity is calculated using the Let statement, thus avoiding the calculation of the two-second value in the return statement.

Another use case for let is to declare a complex calculation in a subquery, making the entire query easier to read.

For u in the users let
  friends = (for
  f in Friends 
    FILTER u.id = = F.userid return
    f) let
  memberships  = (for
  m in memberships
    FILTER u.id = = M.userid return
      m
  ) return
  { 
    "user": U, 
    "Friends" : Friends, 
    "numfriends": LENGTH (Friends), 
    "memberships": Memberships 
  }

========================================================================================
COLLECT

The Collect keyword may be used by the standard of one or more groups for the array of groups.

The Collect statement will eliminate all local variables in the current scope. After collect, only the variables introduced by collect itself are available.

The general syntax for Collect is:

COLLECT variableName = Expression Options
COLLECT variableName = expression into groupsvariable options
COLLECT V Ariablename = expression into groupsvariable = Projectionexpression options
COLLECT variableName = expression into gro upsvariable KEEP keepvariable options
COLLECT variableName = expression with COUNT into countvariable options
COL Lect variableName = expression AGGREGATE variableName = aggregateexpression options
COLLECT AGGREGATE = a Ggregateexpression options
COLLECT with COUNT into countvariable options

Options are optional in all variants. grouping Syntax

The first syntax form of collect can only be grouped by the definition group criteria specified in the expression. To further address the results of the collect generation, a new variable (specified by VariableName) is introduced. This variable contains the group value.

The following is a sample query that can find different values in u.city and make them available in a variable city:

For you in
  the users COLLECT city = u.city return
  {' City 
    ': City 
  }

The second form is the same as the first form, but also introduces a variable (specified by groupsvariable) that contains all the elements that fall into the group. This is done as follows: The groupsvariable variable is an array that contains as many arrays as all elements in the group. Each member of the array is a JSON object, where the value of each variable defined in the AQL query is bound to the corresponding property. Note that it considers all the variables defined before the Collect statement, but does not consider all the variables in the top-level (outside of any for) unless the Collect statement itself is at the top, in which case all the variables are adopted.

For you in the users
  COLLECT city = u.city to groups return
  {' City 
    ': City, 
    ' usersincity ': Groups 
  }

In the example above, the array users are grouped by attribute city. The result is a new array of documents with one element for each different u.city value. The elements of the original array (here: User) of each city are available in the variable group. This is because into the clause.

Collect also allows you to specify multiple group standards. Individual group criteria can be separated by commas:

For u in the users
  COLLECT country = u.country, city = u.city to groups return
  { 
    "country": Country, 
    "City" : City, 
    "usersincity": Groups 
  }

In the example above, the array users are grouped by country by city, and the user is returned for each different combination of country and city. Discard Obsolete variables

The third form of collect allows you to rewrite the contents of groupsvariable using arbitrary projectionexpression:

  for you in the users COLLECT country = u.country, city = u.city to groups = U.name return
  { 
    "country": country,
   "City": City, 
    "UserNames": Groups 
  }

In the above example, only Projectionexpression is u.name. Therefore, only this attribute is copied to the groupsvariable of each document. This may be more efficient than copying all variables from range to groupsvariable, just as there is no projectionexpression.

An expression in into can also be used for any calculation:

For u in the users
  COLLECT country = u.country, city = u.city into groups = { 
    "name": U.name, 
    "isactive": U.stat US = = "Active"
  } return
  { 
    "country": Country, 
    ' city ': City, 
    "usersincity": Groups 
  }

Collect also provides an optional keep clause that can be used to control which variables will be copied into the created variable. If you do not specify a keep clause, all variables in the range are copied as child properties into the groupsvariable. This is safe, but if there are many variables or variables in the scope that contain large amounts of data, it can have a negative impact on performance.

The following example restricts the variables that are copied to groupsvariable to be named only. The variables u and somecalculation within the range are also not copied to groupsvariable because they are not listed in the KEEP clause:

 for u in the users let name = u.name Let somecalculation = u.value1 + u.value2 COLLECT city = u.city into groups KE EP name return {&qu