For examples on how to run SQL queries in each client library, see the individual driver documentation .
At the top-level, XTDB SQL queries augment the SQL standard in the following ways:
SELECT is optional - if not provided, it defaults to SELECT *
FROM is optional - if not provided, it defaults to a 0-column, 1-row table.
This enables queries of the form SELECT 1 + 2 - e.g. to quickly
evaluate a data-less calculation.
SELECT may optionally be provided between GROUP BY and ORDER BY, for readability - at the place in the pipeline where it’s actually evaluated.
GROUP BY is inferred if not provided to be every column reference used outside of an aggregate function.
e.g. for SELECT a, SUM(b) FROM foo, XT will infer GROUP BY a
<with clause>
<query term>
ORDER
BY
<value>
ASC
DESC
NULLS
FIRST
LAST
,
LIMIT
LIMIT
<limit>
<limit>
LIMIT
LIMIT
<limit>
<limit>
OFFSET
OFFSET
<offset>
<offset>
OFFSET
OFFSET
<offset>
<offset>
OFFSET
OFFSET
<offset>
<offset>
OFFSET
OFFSET
<offset>
<offset>
LIMIT
LIMIT
<limit>
<limit>
LIMIT
LIMIT
<limit>
<limit>
WITH
MATERIALIZED
<query name>
AS
(
<query>
)
,
WITH clauses in XTDB are ‘optimization fences’ - XTDB will not attempt to optimize an outer query into a WITH clause, or across WITH clauses.
Supply MATERIALIZED to eagerly materialize a WITH clause, so that the results can be re-used multiple times in the same query.
WITH RECURSIVE is not yet supported in XTDB.
<select clause>
<from clause>
WHERE
WHERE
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
WHERE
WHERE
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
GROUP
GROUP
BY
BY
<value>
<value>
,
,
<value>
<value>
,
,
GROUP
GROUP
BY
BY
<value>
<value>
,
,
<value>
<value>
,
,
GROUP
GROUP
BY
BY
<value>
<value>
,
,
<value>
<value>
,
,
GROUP
GROUP
BY
BY
<value>
<value>
,
,
<value>
<value>
,
,
HAVING
HAVING
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
HAVING
HAVING
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
HAVING
HAVING
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
HAVING
HAVING
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<from clause>
WHERE
WHERE
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
WHERE
WHERE
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
GROUP
GROUP
BY
BY
<value>
<value>
,
,
<value>
<value>
,
,
GROUP
GROUP
BY
BY
<value>
<value>
,
,
<value>
<value>
,
,
GROUP
GROUP
BY
BY
<value>
<value>
,
,
<value>
<value>
,
,
GROUP
GROUP
BY
BY
<value>
<value>
,
,
<value>
<value>
,
,
HAVING
HAVING
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
HAVING
HAVING
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
HAVING
HAVING
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
HAVING
HAVING
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
<predicate>
,
,
,
,
<select clause>
VALUES
(
<value>
,
)
,
XTQL
<XTQL query>
(
<XTQL query>
,
?
)
<query term>
UNION
INTERSECT
EXCEPT
ALL
DISTINCT
<query term>
(
<query term>
)
NB:
SELECT is optional in XTDB - if not provided, it defaults to SELECT *
SELECT may be placed after GROUP BY in XTDB, so that the query clauses are written in the order that they’re executed in practice.
GROUP BY is optional in XTDB - if not provided, it defaults to all of the columns used outside of an aggregate function.
If you start your query with FROM, you may then include arbitrarily many sets of WHERE/GROUP BY/SELECT clauses, which will be evaluated in order.
Predicates can be comma-separated in XTDB, to aid with SQL generation - these are treated as conjuncts.
There may be an arbitrary number of commas at the start, between any two predicate expressions, or at the end.
XTQL queries are sent within an SQL string literal - e.g.
'(-> (from ...) ...)'.
Given the possible presence of single quotes within the query, it is recommended to use dollar-delimited strings here: XTQL $$ <xtql query> $$
Due to implementation details in some drivers (e.g. PGJDBC), it is required to additionally specify the params in standard SQL (?) following your XTQL query, so that the driver knows how many arguments to allow.
For more details on XTQL queries, see the XTQL documentation .
SELECT
*
<star exclude>
<star exclude>
<star exclude>
<star exclude>
<star rename>
<star rename>
<star rename>
<star rename>
<star exclude>
<star exclude>
<star exclude>
<star exclude>
<star rename>
<star rename>
<star rename>
<star rename>
<value>
AS
<column name>
<table name>
.
*
<star exclude>
<star exclude>
<star exclude>
<star exclude>
<star rename>
<star rename>
<star rename>
<star rename>
<star exclude>
<star exclude>
<star exclude>
<star exclude>
<star rename>
<star rename>
<star rename>
<star rename>
,
EXCLUDE
<column name>
(
<column name>
,
)
RENAME
<column name>
<column name>
AS
AS
<column name>
<column name>
<column name>
<column name>
AS
AS
<column name>
<column name>
(
<column name>
<column name>
AS
AS
<column name>
<column name>
<column name>
<column name>
AS
AS
<column name>
<column name>
,
)
FROM
<relation>
(
<column name>
,
)
,
(
<relation>
)
<table name>
<query name>
<temporal filter>
<table alias>
<relation>
LEFT
LEFT
RIGHT
RIGHT
LEFT
LEFT
RIGHT
RIGHT
OUTER
OUTER
OUTER
OUTER
LEFT
LEFT
RIGHT
RIGHT
LEFT
LEFT
RIGHT
RIGHT
OUTER
OUTER
OUTER
OUTER
INNER
INNER
LEFT
LEFT
RIGHT
RIGHT
LEFT
LEFT
RIGHT
RIGHT
OUTER
OUTER
OUTER
OUTER
LEFT
LEFT
RIGHT
RIGHT
LEFT
LEFT
RIGHT
RIGHT
OUTER
OUTER
OUTER
OUTER
INNER
INNER
JOIN
<relation>
ON
<predicate>
USING
(
<column name>
,
)
<relation>
CROSS
JOIN
<relation>
<relation>
NATURAL
LEFT
LEFT
RIGHT
RIGHT
LEFT
LEFT
RIGHT
RIGHT
OUTER
OUTER
OUTER
OUTER
LEFT
LEFT
RIGHT
RIGHT
LEFT
LEFT
RIGHT
RIGHT
OUTER
OUTER
OUTER
OUTER
INNER
INNER
LEFT
LEFT
RIGHT
RIGHT
LEFT
LEFT
RIGHT
RIGHT
OUTER
OUTER
OUTER
OUTER
LEFT
LEFT
RIGHT
RIGHT
LEFT
LEFT
RIGHT
RIGHT
OUTER
OUTER
OUTER
OUTER
INNER
INNER
JOIN
<relation>
VALUES
(
<value>
,
)
,
XTQL
<XTQL query>
(
<XTQL query>
,
?
)
(
(
<query>
<query>
)
)
(
(
<query>
<query>
)
)
LATERAL
(
(
<query>
<query>
)
)
(
(
<query>
<query>
)
)
UNNEST
(
<value>
)
WITH
ORDINALITY
<table alias>
FOR
VALID_TIME
VALID_TIME
SYSTEM_TIME
SYSTEM_TIME
VALID_TIME
VALID_TIME
SYSTEM_TIME
SYSTEM_TIME
AS
OF
<timestamp>
FROM
<timestamp>
TO
<timestamp>
BETWEEN
<timestamp>
AND
<timestamp>
ALL
ALL
VALID_TIME
VALID_TIME
SYSTEM_TIME
SYSTEM_TIME
VALID_TIME
VALID_TIME
SYSTEM_TIME
SYSTEM_TIME
The valid-time/system-time filters for a given table take precedence as follows:
Any explicit specifications in the FROM clause.
Any options passed to the SETTING clause of the given query (see ‘Basis’ ).
Any options passed to the BEGIN clause of the current transaction (see ‘Basis’ ).
Then as follows:
System time defaults to ‘as best known’ - the latest processed transaction on the queried node.
Valid time defaults to ‘as of now’ - the clock time taken either from CLOCK_TIME (if overridden) or the actual clock time on the queried node.
<literal>
<column reference>
"
<column reference>
"
`
<column reference>
`
<param>
+
-
<value>
<value>
+
-
*
/
<value>
<function name>
(
<value>
,
)
ROW_NUMBER
(
)
OVER
(
<window>
)
<predicate>
CAST
(
<value>
AS
<data type>
)
CASE
<value>
WHEN
<value>
THEN
<value>
,
WHEN
<predicate>
THEN
<value>
,
ELSE
<value>
END
COALESCE
(
<value>
,
)
NULLIF
(
<value>
,
<value>
)
ARRAY
[
<value>
,
]
ARRAY
(
<query>
)
<record>
NEST_ONE
NEST_MANY
(
<query>
)
(
<value>
)
?
$<param idx>
{
<field name>
<field name>
:
:
<value>
<value>
<field name>
<field name>
:
:
<value>
<value>
,
,
<field name>
<field name>
:
:
<value>
<value>
<field name>
<field name>
:
:
<value>
<value>
,
,
<field name>
<field name>
:
:
<value>
<value>
<field name>
<field name>
:
:
<value>
<value>
,
,
<field name>
<field name>
:
:
<value>
<value>
<field name>
<field name>
:
:
<value>
<value>
,
,
}
RECORD
OBJECT
(
<field name>
<field name>
:
:
<value>
<value>
<field name>
<field name>
:
:
<value>
<value>
,
,
<field name>
<field name>
:
:
<value>
<value>
<field name>
<field name>
:
:
<value>
<value>
,
,
<field name>
<field name>
:
:
<value>
<value>
<field name>
<field name>
:
:
<value>
<value>
,
,
<field name>
<field name>
:
:
<value>
<value>
<field name>
<field name>
:
:
<value>
<value>
,
,
)
NULL
<numeric literal>
'
<SQL-style string>
'
$$
<string>
$$
E'
<C-style string>
'
DATE
'
<ISO8601 date literal>
'
TIME
'
<ISO8601 time literal>
'
TIMESTAMP
'
<ISO8601 timestamp literal>
'
WITH
WITHOUT
TIME
ZONE
'
<SQL timestamp literal>
'
DURATION
'
<ISO8601 duration literal>
'
TRUE
FALSE
NOT
<predicate>
<predicate>
AND
OR
<predicate>
<value>
=
<>
!=
<
<=
>=
>
<value>
ANY
ALL
(
<query>
)
<predicate name>
(
<value>
,
)
<value>
IS
NOT
TRUE
FALSE
NULL
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
EXISTS
(
<query>
)
<value>
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
IN
(
<value>
,
<query>
)
<value>
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
LIKE
<value>
ESCAPE
'
<escape character>
'
<value>
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
LIKE_REGEX
<JVM regex>
FLAG
'
<JVM regex flags>
'
<value>
~
~*
!~
!~*
<JVM regex>
<value>
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NOT
BETWEEN
ASYMMETRIC
SYMMETRIC
<value>
AND
<value>
PARTITION
BY
<value>
,
ORDER
BY
<value>
ASC
DESC
NULLS
FIRST
LAST
,
Nested sub-queries allow you to easily create tree-shaped results, using NEST_MANY and NEST_ONE:
For example, if you have a one-to-many relationship (e.g. customers → orders), you can write a query that, for each customer, returns an array of their orders as nested objects:
SELECT c . _id AS customer_id, c . name ,
NEST_MANY( SELECT o . _id AS order_id, o . value
WHERE o . customer_id = c . _id
⇒
"orders" : [ { "orderId" : 0 , "value" : 26.20 }, { "orderId" : 1 , "value" : 8.99 } ]
"orders" : [ { "orderId" : 2 , "value" : 12.34 } ]
In the other direction (many-to-one) - for each order, additionally return details about the customer - use NEST_ONE to get a single nested object:
SELECT o . _id AS order_id, o . value ,
NEST_ONE( SELECT c . name FROM customers c
WHERE c . _id = o . customer_id )
⇒
"customer" : { "name" : " bob " }
"customer" : { "name" : " bob " }
"customer" : { "name" : " alice " }
Queries in XTDB run against a ‘basis’, which consists of:
a ‘snapshot’ - an upper bound on the transactions that are visible to the query.
a ‘clock time’ - used for any function calls that reference the current time (e.g. CURRENT_TIMESTAMP)
These can be set either on a per-query basis, using SETTING, or at the start of a transaction, using BEGIN:
SETTING
DEFAULT
VALID_TIME
SYSTEM_TIME
TO
AS
OF
<timestamp>
FROM
<timestamp>
TO
<timestamp>
BETWEEN
<timestamp>
AND
<timestamp>
ALL
SNAPSHOT_TOKEN
CLOCK_TIME
TO
=
<timestamp>
,
<query>
Setting the default valid-time/system-time applies to any FROM clause that doesn’t have any valid-time/system-time specification explicitly set.
Setting the SNAPSHOT_TOKEN enforces an upper-bound on the transactions visible to the query - i.e. no matter what the per-table system-time clauses specify, they will not see anything newer than this snapshot-token.
If not provided, this defaults to the latest-completed transaction on the queried node.
Setting the CLOCK_TIME defines a fixed value for any functions that depend on the current time - e.g. CURRENT_TIMESTAMP.
It also defines the default valid-time selection for any tables in FROM clauses that don’t otherwise have a valid-time specification.
If not provided, it defaults to the clock-time fixed at the start of the transaction.
BEGIN
READ
ONLY
WITH
(
SNAPSHOT_TOKEN
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
<snapshot token>
CLOCK_TIME
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
<timestamp>
AWAIT_TOKEN
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
<await token>
TIMEZONE
TIME
ZONE
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
<timezone>
,
)
READ
WRITE
...
COMMIT
ROLLBACK
A transaction may be either READ ONLY or READ WRITE.
If not specified, it will be inferred from the first statement in the transaction.
Transactions must not mix query statements and DML statements.
Additionally, for read-only transactions:
SNAPSHOT_TOKEN and CLOCK_TIME behave the same as in SETTING .
AWAIT_TOKEN may be provided to wait for a specific transaction to be visible on the queried node before starting the transaction.
If not provided, it defaults to waiting for the latest-submitted transaction on the current connection.
TIMEZONE sets the time zone for the duration of the transaction, affecting any time zone-aware date/time literals and functions.
If not provided, it defaults to the time-zone of the connection.
For read-write transactions, see the transaction reference .
Committing/rolling back a read-only transaction has no effect in XTDB, because readers never block writers nor each other.