SQL Queries

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

Fuller SQL reference documentation will follow shortly.

For now, the following is a subset of what’s currently possible in XTDB SQL:

SELECT <value> [ [ AS ] <column_name> ] [ , ... ]
FROM <relation> [ [ AS ] <table_alias> [ (<column_name> [, ...] ) ]] [ , ... ]
     [ <join_clause>+ ]
[ WHERE <predicate> ]
[ GROUP BY <value> [ , ... ] ]
[ ORDER BY <value> [ (ASC | DESC) ] [ , ... ] ]
[ LIMIT <number> ]
[ OFFSET <number> ]

relation :: <table_name> [ <for_system_time> ] [ <for_valid_time> ]
          | ( <sub_query> )
          | ( VALUES (<column_name> [ , ... ]) )

for_system_time :: FOR SYSTEM_TIME <temporal_range> | FOR ALL SYSTEM_TIME | FOR SYSTEM_TIME ALL
for_valid_time :: FOR VALID_TIME <temporal_range> | FOR ALL VALID_TIME | FOR VALID_TIME ALL
temporal_range :: AS OF <date_time>
                | FROM <date_time> TO <date_time> -- start inclusive, end exclusive
                | BETWEEN <date_time> AND <date_time> -- start inclusive, end inclusive

join_clause :: [ join_type ] JOIN <relation> <join_condition> [ ... ]
join_type :: INNER | LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ OUTER ]
join_condition :: USING ( <column_name> [ , ... ] )
                | ON <predicate>

predicate :: <value> <binary_op> <value>
           | <value> [ NOT ] IN ( <query> )
           | [ NOT ] EXISTS ( <query> )

value :: <predicate>
       | <function> ( [ <arg> [ , ... ] ])
       | (<query>)
       | NEST_ONE(<query>)
       | NEST_MANY(<query>)

• Temporarily, column names in XTDB SQL must be qualified - e.g. SELECT u.first_name, u.last_name FROM users u

• Predicates and functions are taken from the XT 'standard library'.

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.xt$id AS customer_id, c.name,
         NEST_MANY(SELECT o.xt$id AS order_id, o.value
                   FROM orders o
                   WHERE o.customer_id = c.xt$id
                   ORDER BY o.xt$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.xt$id AS order_id, o.value,
         NEST_ONE(SELECT c.name FROM customers c
                  WHERE c.xt$id = o.customer_id)
           AS customer
  FROM orders o
  ORDER BY o.xt$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 xt$id, xt$valid_from and xt$valid_to respectively. This mapping is reversed when querying SQL documents from XTQL.