Sequence Operators

Capella Operational

reference

Sequence operators enable you to return a value from a sequence.

Sequences

A sequence is a construct that returns a sequence of integer values, one at a time, rather like a counter. Each time you request the next value for a sequence, an increment is added to the previous value, and the resulting value is returned. This is useful for generating values such as sequential ID numbers, where you need the Query service to keep track of the current value from one query to the next.

You can define any of the following attributes when you create a sequence. You can alter an existing sequence in order to restart it, or to change any of the sequence attributes.

Start Value, Increment, and Direction

By default, a sequence starts at 0 and goes up by 1 at each step. You can specify the start value of the sequence, the incremental value for the sequence, and the direction of the sequence: ascending or descending.

Maximum and Minimum Value

The highest possible value for a sequence is the highest signed 64-bit integer, 2⁶³-1. This is the default maximum value.

The lowest possible value for a sequence is the lowest signed 64-bit integer, -2⁶³. This is the default minimum value.

You can specify a different maximum or minimum value for a sequence.

Cycling

A sequence may permit cycling. In this case, the sequence behaves as follows:

If the sequence is ascending, then when it reaches the maximum value, it continues from the minimum value — which may be different to the sequence’s specified starting value.
If the sequence is descending, then when it reaches the minimum value, it continues from the maximum value — which may be different to the sequence’s specified starting value.

If a sequence does not permit cycling, then when it reaches the maximum or minimum value, it generates an error.

Next Value Operator

The next value operator increments a given sequence and returns the next value.

Prerequisites

RBAC Privileges

To use this operator, you must have the Query Use Sequences privilege granted on the scope. For more details about user roles, see Authorization.

Syntax

next-val-expr ::= 'NEXT' 'VALUE' 'FOR' sequence |
                  'NEXTVAL' 'FOR' sequence

Synonym: NEXT VALUE FOR and NEXTVAL FOR are synonyms.

sequence

(Required) A name that identifies the sequence within a namespace, bucket, and scope. See Sequence Name below.

Sequence Name

sequence ::= ( ( namespace ':' )? bucket '.' scope '.' )? identifier

Each sequence is associated with a given namespace, bucket, and scope. You must specify the namespace, bucket, and scope to refer to the sequence correctly.

namespace	(Optional) The namespace of the bucket which contains the sequence.
bucket	(Optional) The bucket which contains the sequence.
scope	(Optional) The scope which contains the sequence.
identifier	(Required) The name of the sequence. The sequence name is case-sensitive.

Currently, only the default namespace is available. If you omit the namespace, the default namespace in the current session is used.

If the query context is set, you can omit the bucket and scope from the statement. In this case, the bucket and scope for the sequence are taken from the query context.

The namespace, bucket, scope, and sequence name must follow the rules for identifiers. If the namespace, bucket, scope, or sequence name contain any special characters such as hyphens (-), you must wrap that part of the expression in backticks (` `).

Return Value

For a new sequence, the next value operator returns the starting value in the sequence.

For a sequence that has been referenced already, the next value operator increments the sequence and returns the new value. However, the sequence is only incremented once per document.

Subqueries operate on independent documents from their containing queries, so subqueries increment the sequence independently.

A sequence is not guaranteed to generate unique values. For example, in the following circumstances, it may generate a value that it has generated before:

If the sequence cycles.
If the sequence is restarted with a value that overlaps with previously-generated values.
If the sequence is restarted to change the direction of the increment: for example, descending instead of ascending.

Restrictions

You cannot use this operator in a WHERE or ON clause. This generates a semantic error 3100.

Examples

To try the examples in this section, set the query context to the tenant_agent_00 scope in the travel sample dataset. For more information, see Query Context.

Example 1. Start a sequence

This statement starts a sequence called ordNum for use in the following examples.

Query

CREATE SEQUENCE ordNum START WITH 1000;

Example 2. Insert a sequential value in a document body

The following statement uses the ordNum sequence to generate a booking number within the body of the document.

Query

INSERT INTO bookings
  VALUES (UUID(),
    {"num": NEXT VALUE FOR ordNum, "user": 0})
  RETURNING *;

Results

[
  {
    "bookings": {
      "num": 1000,
      "user": 0
    }
  }
]

Example 3. Insert a sequential value in a document key

The following statement uses the ordNum sequence to generate the document key and a booking number within the body of the document.

Query

INSERT INTO bookings
  VALUES (TO_STRING(NEXTVAL FOR ordNum),
    {"num": NEXTVAL FOR ordNum, "user": 1})
  RETURNING META().id, *;

Results

[
  {
    "id": "1001",
    "bookings": {
      "num": 1002,
      "user": 1
    }
  }
]

Since the key is not part of the document, the query has incremented the sequence twice. This gives a different sequence number for the document key and the document value, which may not be what you want.

Example 4. Insert the same sequential value in a document key and body

The following statement uses an INSERT SELECT statement. With this query, the document key and document value are both generated within the same document.

Query

INSERT INTO bookings (KEY k, VALUE v)
  SELECT TO_STRING(NEXTVAL FOR ordNum) k,
         {"num": NEXTVAL FOR ordNum, "user": 1} v
  RETURNING META().id, *;

Result

[
  {
    "id": "1003",
    "bookings": {
      "num": 1003,
      "user": 1
    }
  }
]

The next value operator only increments a sequence once within the context of a document. This gives the same sequence number in the document key and the document value.

Previous Value Operator

The previous value operator returns the current value in a sequence, without incrementing or decrementing the sequence. This is useful when you need to refer to the same value again without generating a new value.

Prerequisites

RBAC Privileges

To use this operator, you must have the Query Use Sequences privilege granted on the scope. For more details about user roles, see Authorization.

Syntax

prev-val-expr ::= 'PREVIOUS' 'VALUE' 'FOR' sequence |
                  'PREV' 'VALUE' 'FOR' sequence |
                  'PREVVAL' 'FOR' sequence

Synonym: PREVIOUS VALUE FOR, PREV VALUE FOR, and PREVVAL FOR are synonyms.

sequence

(Required) A name that identifies the sequence within a namespace, bucket, and scope. See Sequence Name below.

Sequence Name

sequence ::= ( ( namespace ':' )? bucket '.' scope '.' )? identifier

Each sequence is associated with a given namespace, bucket, and scope. You must specify the namespace, bucket, and scope to refer to the sequence correctly.

namespace	(Optional) The namespace of the bucket which contains the sequence.
bucket	(Optional) The bucket which contains the sequence.
scope	(Optional) The scope which contains the sequence.
identifier	(Required) The name of the sequence. The sequence name is case-sensitive.

Currently, only the default namespace is available. If you omit the namespace, the default namespace in the current session is used.

If the query context is set, you can omit the bucket and scope from the statement. In this case, the bucket and scope for the sequence are taken from the query context.

Return Value

The previous value operator returns one of the following, in order of precedence:

If a value has been generated for the current document, the operator returns the current value generated for the document.
If in a transaction, and a value has been generated for the transaction, the operator returns the current value generated in the transaction.
Otherwise, the operator returns the current value generated for the sequence on the node.

The previous value operator does not increment or decrement the sequence.

If no value has been generated for the sequence, the previous value operator returns an error.

Restrictions

You cannot use this operator in a WHERE or ON clause. This generates a semantic error 3100.

Examples

To try the examples in this section, set the query context to the tenant_agent_00 scope in the travel sample dataset. For more information, see Query Context.

Example 5. Insert the same sequential value in multiple statements

This example assumes that you have created a sequence called ordNum as described in Example 1.

Query

BEGIN TRANSACTION;
INSERT INTO bookings VALUES(UUID(),
  {"num": NEXT VALUE FOR ordNum,
   "user": 0,
   "type": "order"});
INSERT INTO bookings VALUES(UUID(),
  {"order_num": PREVVAL FOR ordNum,
   "hotel": "hotel_17413",
   "type": "item"});
INSERT INTO bookings VALUES(UUID(),
  {"order_num": PREVVAL FOR ordNum,
   "hotel": "hotel_15912",
   "type": "item"});
COMMIT;

Example 6. Check the result of Example 5

Query

SELECT o.num, o.user, ARRAY_AGG(i.hotel) items
FROM bookings o,
     bookings i
WHERE o.type = "order"
  AND i.type = "item"
  AND o.num = i.order_num
GROUP BY o.num, o.user;

Result

[
  {
    "num": 1004,
    "user": 0,
    "items": [
      "hotel_15912",
      "hotel_17413"
    ]
  }
]

To create a sequence, see CREATE SEQUENCE.
To alter a sequence, see ALTER SEQUENCE.
To drop a sequence, see DROP SEQUENCE.
To monitor sequences, see Monitor Sequences.

Sequence Operators

Sequences

Start Value, Increment, and Direction

Maximum and Minimum Value

Cycling

Next Value Operator

Prerequisites

Syntax

Sequence Name

Return Value

Restrictions

Examples

Previous Value Operator

Prerequisites

Syntax

Sequence Name

Return Value

Restrictions

Examples

Related Links