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
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.
See Date/time types for more details on XTDB’s timestamp literals.
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
FROM orders o
WHERE o.customer_id = c._id
ORDER BY o._id)
AS orders
FROM customers c⇒
[
{
"customerId": 0,
"name": "bob",
"orders": [ { "orderId": 0, "value": 26.20 }, { "orderId": 1, "value": 8.99 } ]
},
{
"customerId": 1,
"name": "alice",
"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)
AS customer
FROM orders o
ORDER BY o._id⇒
[
{
"orderId": 0,
"value": 26.20,
"customer": { "name": "bob" }
},
{
"order-id": 1,
"value": 8.99,
"customer": { "name": "bob" }
},
{
"order-id": 2,
"value": 12.34,
"customer": { "name": "alice" }
}
]