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
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" }
}
]