SQL Queries

For examples on how to run SQL queries in each client library, see the individual driver documentation:

Top-level queries

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

<query>

<with clause>

<query term>

<select clause>

From clause, joins

<from clause>

<relation>

<temporal filter>

Expressions

<value>

<literal>

<predicate>

Nested sub-queries

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

Note

There is a table and column name mapping between SQL and XTQL: documents inserted with XTQL have their hyphens translated to underscores, and their namespace segments converted to $ symbols, as hyphens, periods and slashes are not valid symbols in SQL identifiers. For example, :foo.bar/baz-quux in XTQL is referenced in SQL as foo$bar$baz_quux. The built-in XTDB columns :xt/id, :xt/valid-from, :xt/valid-to etc are referenced in SQL as _id, _valid_from and _valid_to respectively. This mapping is reversed when querying SQL documents from XTQL.