Queries

Introduction

Crux is a schemaless document database that provides you with a comprehensive means of traversing and querying across all of your documents. Crux automatically indexes the top-level fields in all documents, supporting efficient ad-hoc joins and retrievals. Crux is both immutable and bitemporal, which are both required characteristics for a safe schemaless database.

Crux is also a graph database. The central characteristic of a graph database is that it can support arbitrary-depth graph queries (recursive traversals) very efficiently by default, without any need for schema-level optimisations. Graph queries are possible through the Crux dialect of the Datalog query language.

SQL

Crux 1.x supports both Datalog and SQL, but SQL support is provided through a module. To query Crux using SQL, read the SQL module documentation.

Datalog

Extensible Data Notation (edn) is a simple data format that is used to describe Crux Datalog queries. To understand how edn works, read the brief description at http://edn-format.org.

To understand how Datalog works, read the Learn Crux Datalog Today tutorial from the Tutorials page.

Basic Structure

A query in Crux is performed by calling crux/q on a Crux database snapshot with a quoted map and, optionally, additional arguments.

(crux/q
 (crux/db node) (1)
 '{:find [p1] (2)
   :where [[p1 :name n]
           [p1 :last-name n]
           [p1 :name name]]
   :in [name]}
 "Ivan") (3)

1	Database value. Usually the snapshot view comes from calling `crux/db` on a Crux node
2	Query map or vector (ex. `[:find …etc… ]`, as found in other Datalog databases)
3	Argument(s) supplied to the `:in` relations

The query map accepts the following Keywords

Table 1. Query Keys
Key	Type	Purpose
`:find`	Vector	Specify values to be returned
`:where`	Vector	Restrict the results of the query
`:in`	Vector	Specify external arguments
`:order-by`	Vector	Control the result order
`:limit`	Int	Specify how many results to return
`:offset`	Int	Specify how many results to discard
`:rules`	Vector	Define powerful statements for use in `:where` clauses
`:timeout`	Int	Specify maximum query run time in ms

Find

The find clause of a query specifies what values to be returned. These will be returned as a list.

Logic Variable

You can directly specify a logic variable from your query. The following will return all last names bound to the logic variable n.

(crux/q
 (crux/db node)
 '{:find [n]
   :where [[p :last-name n]]})

Aggregates

You can specify an aggregate function to apply to at most one logic variable.

Table 2. Built-in Aggregate Functions
Usage	Description
`(sum ?lvar)`	Accumulates as a single value via the Clojure `+` function
`(min ?lvar)`	Return a single value via the Clojure `compare` function which may operate on many types (integers, strings, collections etc.)
`(max ?lvar)`
`(count ?lvar)`	Return a single count of all values including any duplicates
`(avg ?lvar)`	Return a single value equivalent to `sum / count`
`(median ?lvar)`	Return a single value corresponding to the statistical definition
`(variance ?lvar)`
`(stddev ?lvar)`
`(rand N ?lvar)`	Return a vector of exactly N values, where some values may be duplicates if N is larger than the range
`(sample N ?lvar)`	Return a vector of at-most N distinct values
`(distinct ?lvar)`	Return a set of distinct values

Example:

(crux/q
 (crux/db node)
 '{:find [(sum ?heads)
          (min ?heads)
          (max ?heads)
          (count ?heads)
          (count-distinct ?heads)]
   :where [[(identity [["Cerberus" 3]
                       ["Medusa" 1]
                       ["Cyclops" 1]
                       ["Chimera" 1]])
            [[?monster ?heads]]]]})

#{[6 1 3 4 2]}

Note there is always implicit grouping across aggregates due to how Crux performs the aggregation lazily before turning the result tuples into a set.

Custom Aggregates

Custom (user-defined) aggregates are supported by adding a new method (via Clojure defmethod) for crux.query/aggregate. This method takes a single (ignored) parameter and returns a multi-arity function which accepts zero parameters, an accumulator, or an accumulator and a single entity. For example:

(defmethod crux.query/aggregate 'sort-reverse [_]
  (fn
    ([] [])
    ([acc] (vec (reverse (sort acc))))
    ([acc x] (conj acc x))))

Pull

Crux queries support a pull syntax, allowing you to decouple specifying which entities you want from what data you’d like about those entities in your queries. Crux’s support is based on the excellent EDN Query Language (EQL) library.

To specify what data you’d like about each entity, include a (pull ?logic-var projection-spec) entry in the :find clause of your query:

;; with just 'query':
(crux/q
 (crux/db node)
 '{:find [?uid ?name ?profession]
   :where [[?user :user/id ?uid]
           [?user :user/name ?name]
           [?user :user/profession ?profession]]})
#{[1 "Ivan" :doctor] [2 "Sergei" :lawyer], [3 "Petr" :doctor]}

;; using `pull`:
(crux/q
 (crux/db node)
 '{:find [(pull ?user [:user/name :user/profession])]
   :where [[?user :user/id ?uid]]})
#{[{:user/name "Ivan" :user/profession :doctor}]
  [{:user/name "Sergei" :user/profession :lawyer}]
  [{:user/name "Petr" :user/profession :doctor}]}

You can quickly grab the whole document by specifying * in the projection spec:

(crux/q
 (crux/db node)
 '{:find [(pull ?user [*])]
   :where [[?user :user/id 1]]})
#{[{:crux.db/id :ivan :user/id 1, :user/name "Ivan", :user/profession :doctor}]}

If you have the entity id(s) in hand, you can call pull or pull-many directly:

           ;; using `pull`:
           (crux/pull
            (crux/db node)
            [:user/name :user/profession]
            :ivan)
           {:user/name "Ivan", :user/profession :doctor}
           ;; using `pull-many`:
           (crux/pull-many
            (crux/db node)
            [:user/name :user/profession]
            [:ivan :sergei])
           [{:user/name "Ivan", :user/profession :doctor},
            {:user/name "Sergei", :user/profession :lawyer}]

We can navigate to other entities (and hence build up nested results) using joins. Joins are specified in {} braces in the projection-spec - each one maps one join key to its nested spec:

;; with just 'query':
(crux/q
 (crux/db node)
 '{:find [?uid ?name ?profession-name]
   :where [[?user :user/id ?uid]
           [?user :user/name ?name]
           [?user :user/profession ?profession]
           [?profession :profession/name ?profession-name]]})
#{[1 "Ivan" "Doctor"] [2 "Sergei" "Lawyer"] [3 "Petr" "Doctor"]}

;; using `pull`:
(crux/q
 (crux/db node)
 '{:find [(pull ?user [:user/name {:user/profession [:profession/name]}])]
   :where [[?user :user/id ?uid]]})
#{[{:user/name "Ivan" :user/profession {:profession/name "Doctor"}}]
  [{:user/name "Sergei" :user/profession {:profession/name "Lawyer"}}]
  [{:user/name "Petr" :user/profession {:profession/name "Doctor"}}]}

We can also navigate in the reverse direction, looking for entities that refer to this one, by prepending _ to the attribute name:

(crux/q
 (crux/db node)
 '{:find [(pull ?profession [:profession/name {:user/_profession [:user/id :user/name]}])]
   :where [[?profession :profession/name]]})
#{[{:profession/name "Doctor"
    :user/_profession [{:user/id 1 :user/name "Ivan"},
                       {:user/id 3 :user/name "Petr"}]}]
  [{:profession/name "Lawyer"
    :user/_profession [{:user/id 2 :user/name "Sergei"}]}]}

Attribute parameters

Crux pull syntax supports a handful of custom EQL parameters, specified by wrapping the :attribute key in a pair: (:attribute {:param :value, …}).

:as - to rename attributes in the result, wrap the attribute in (:source-attribute {:as :output-name}):

{:find [(pull ?profession [:profession/name
                           {(:user/_profession {:as :users}) [:user/id :user/name]}])]
 :where [[?profession :profession/name]]}

;; => [{:profession/name "Doctor",
;;      :users [{:user/id 1, :user/name "Ivan"},
;;              {:user/id 3, :user/name "Petr"}]},
;;     {:profession/name "Lawyer",
;;      :users [{:user/id 2, :user/name "Sergei"}]}]

:limit - limit the amount of values returned under the given property/join: (:attribute {:limit 5})
:default - specify a default value if the matched document doesn’t contain the given attribute: (:attribute {:default "default"})

:into - specify the collection to pour the results into: (:attribute {:into #{}})

{:find [(pull ?profession [:profession/name
                           {(:user/_profession {:as :users, :into #{}})
                           [:user/id :user/name]}])]
 :where [[?profession :profession/name]]}

;; => [{:profession/name "Doctor",
;;      :users #{{:user/id 1, :user/name "Ivan"},
;;               {:user/id 3, :user/name "Petr"}}},
;;     {:profession/name "Lawyer",
;;      :users #{{:user/id 2, :user/name "Sergei"}}}]

:cardinality (reverse joins) - by default, reverse joins put their values in a collection - for many-to-one/one-to-one reverse joins, specify {:cardinality :one} to return a single value.

For full details on what’s supported in the projection-spec, see the EQL specification

Returning maps

To return maps rather than tuples, supply the map keys under :keys for keywords, :syms for symbols, or :strs for strings:

(crux/q
 (crux/db node)
 '{:find [?name ?profession-name]
   :keys [name profession]
   :where [[?user :user/id 1]
           [?user :user/name ?name]
           [?user :user/profession ?profession]
           [?profession :profession/name ?profession-name]]})
#{{:name "Ivan", :profession "Doctor"}}

Where

The :where section of a query limits the combinations of possible results by satisfying all clauses and rules in the supplied vector against the database (and any :in relations).

Table 3. Valid Clauses
Name	Description
triple clause	Restrict using EAV indexes
predicate	Restrict with any predicate
range predicate	Restrict with any of `<` `<=` `>=` `>` `=`
unification predicate	Unify two distinct logic variables with `!=` or `==`
not rule	Negate a list of clauses
not-join rule	Not rule with its own scope
or rule	Restrict on at least one matching clause
or-join rule	Or with its own scope
defined rule	Restrict with a user-defined rule

Clause Inputs

Clauses may refer to combinations of literal values and logic variables as inputs. Identical logic variables used across multiple clauses unify automatically (with the exception of or-join or not-join scoping).

A literal set containing literal values (e.g. #{"val-1" "val-2"}) will be interpreted as an input relation of distinct values, and can be used in place of any literal input (e.g. a set of entity IDs in the first position of a triple clause).

Triple

A triple clause is a vector of (1) a literal entity ID or a logic variable, (2) a hard-coded attribute keyword (top-level key in a document), and (3) optionally, a value which can be a literal or a logic variable.

It restricts results by matching EAV facts

(crux/q
 (crux/db node)
 '{:find [p]
   :where [[p :name]]}) (1)

(crux/q
 (crux/db node)
 '{:find [p]
   :where [[p :name "Ivan"]]}) (2)

(crux/q
 (crux/db node)
 '{:find [p]
   :where [[q :name n]
           [p :last-name n]]}) (3)

1	This matches all entities, `p`, which have a `:name` field.
2	This matches all entities, `p`, which have a `:name` of `"Ivan"`.
3	This matches all entities, `p`, which have a `:name` which match the `:last-name` of `q`.

Predicates

Any fully qualified Clojure function that returns a boolean can be used as a "filter" predicate clause.

Predicate clauses must be placed in a clause, i.e. with a surrounding vector.

(crux/q
 (crux/db node)
 '{:find [p]
   :where [[p :age age]
           [(odd? age)]]})

This matches all entities, p which have an odd :age.

Subqueries

You can nest a subquery with a :where clause to bind the result for further use in the query.

Binding results as a scalar

(crux/q
 (crux/db node)
 '{:find [x]
   :where [[(q {:find [y]
                :where [[(identity 2) x]
                        [(+ x 2) y]]})
            x]]})

In the above query, we perform a subquery doing some arithmetic operations and returning the result - and bind the resulting relation as a scalar.

Result set:

#{[[[4]]]}

Binding results as a tuple

(crux/q
 (crux/db node)
 '{:find [x]
   :where [[(q {:find [y]
                :where [[(identity 2) x]
                        [(+ x 2) y]]})
            [[x]]]]})

Similar to the previous query, except we bind the resulting relation as a tuple.

Result set:

#{[4]}

In this example, we bind the results of a subquery and use them to return another result.

(crux/q
 (crux/db node)
 '{:find [x y z]
   :where [[(q {:find [x y]
                :where [[(identity 2) x]
                        [(+ x 2) y]]})
            [[x y]]]
           [(* x y) z]]})

Result set:

#{[2 4 8]}

Any fully qualified Clojure function can also be used to return relation bindings in this way, by returning a list, set or vector.

Range Predicate

A range predicate is a vector containing a list of a range operator and then two logic variables or literals.

Allowed range operators are <, <=, >=, >, and =.

(crux/q
 (crux/db node)
 '{:find [p] (1)
   :where [[p :age a]
           [(> a 18)]]})

(crux/q
 (crux/db node)
 '{:find [p] (2)
   :where [[p :age a]
           [q :age b]
           [(> a b)]]})

(crux/q
 (crux/db node)
 '{:find [p] (3)
   :where [[p :age a]
           [(> 18 a)]]})

1	Finds any entity, `p`, with an `:age` which is greater than 18
2	Finds any entity, `p`, with an `:age` which is greater than the `:age` of any entity
3	Finds any entity, `p`, for which 18 is greater than `:age` of `p`

Unification Predicate

Use a unification predicate, either == or !=, to constrain two independent logic variables. Literals (and sets of literals) can also be used in place of one of the logic variables.

;; Find all pairs of people with the same age:

[[p :age a]
 [p2 :age a2]
 [(== a a2)]]

;; ...is approximately equivalent to...

[[p :age a]
 [p2 :age a]]

;; Find all pairs of people with different ages:

[[p :age a]
 [p2 :age a2]
 [(!= a a2)]]

;; ...is approximately equivalent to...

[[p :age a]
 [p2 :age a2]
 (not [(= a a2]])]

Not

The not clause rejects a graph if all the clauses within it are true.

[{:crux.db/id :petr-ivanov :name "Petr" :last-name "Ivanov"} (1)
 {:crux.db/id :ivan-ivanov :name "Ivan" :last-name "Ivanov"}
 {:crux.db/id :ivan-petrov :name "Ivan" :last-name "Petrov"}
 {:crux.db/id :petr-petrov :name "Petr" :last-name "Petrov"}]

(crux/q
 (crux/db node)
 '{:find [e]
   :where [[e :crux.db/id]
           (not [e :last-name "Ivanov"] (2)
                [e :name "Ivan"])]})

#{[:petr-ivanov] [:petr-petrov] [:ivan-petrov]} (3)

1	Data
2	Query
3	Result

This will match any document which does not have a :name of "Ivan" and a :last-name of "Ivanov".

Not Join

The not-join rule allows you to restrict the possibilities for logic variables by asserting that there does not exist a match for a given sequence of clauses.

You declare which logic variables from outside the not-join scope are to be used in the join.

Any other logic variables within the not-join are scoped only for the join.

[{:crux.db/id :ivan :name "Ivan" :last-name "Ivanov"} (1)
 {:crux.db/id :petr :name "Petr" :last-name "Petrov"}
 {:crux.db/id :sergei :name "Sergei" :last-name "Sergei"}]

(crux/q
 (crux/db node)
 '{:find [e]
   :where [[e :crux.db/id]
           (not-join [e] (2)
                     [e :last-name n] (3)
                     [e :name n])]})

#{[:ivan] [:petr]} (4)

1	Data
2	Declaration of which logic variables need to unify with the rest of the query
3	Clauses
4	Result

This will match any entity, p, which has different values for the :name and :last-name field.

Importantly, the logic variable n is unbound outside the not-join clause.

Or

An or clause is satisfied if any of its legs are satisfied.

[{:crux.db/id :ivan-ivanov-1 :name "Ivan" :last-name "Ivanov" :sex :male} (1)
 {:crux.db/id :ivan-ivanov-2 :name "Ivan" :last-name "Ivanov" :sex :male}
 {:crux.db/id :ivan-ivanovtov-1 :name "Ivan" :last-name "Ivannotov" :sex :male}
 {:crux.db/id :ivanova :name "Ivanova" :last-name "Ivanov" :sex :female}
 {:crux.db/id :bob :name "Bob" :last-name "Controlguy"}]

(crux/q
 (crux/db node)
 '{:find [e] (2)
   :where [[e :name name]
           [e :name "Ivan"]
           (or [e :last-name "Ivanov"]
               [e :last-name "Ivannotov"])]})

#{[:ivan-ivanov-1] [:ivan-ivanov-2] [:ivan-ivanovtov-1]} (3)

1	Data
2	Query
3	Result

This will match any document, p, which has a :last-name of "Ivanov" or "Ivannotov".

When within an or rule, you can use and to group clauses into a single leg (which must all be true).

(crux/q
 (crux/db node)
 '{:find [name]
   :where [[e :name name]
           (or [e :sex :female]
               (and [e :sex :male]
                    [e :name "Ivan"]))]})

Whenever the query engine complains that each leg in the or or or-join clause requires the "same logic variables", you can add a no-op predicate clause like [(any? e)] within and clauses for each of the missing variables in the various legs. You need to add such no-op predicates until each leg contains the same set of variables.

(crux/q
 (crux/db node)
 '{:find [name]
   :where [[e :name name]
           (or (and [e :sex :female]
                    [(= name "Ivanova")])
               (and [e :sex :male]
                    [(any? name)]))]})

Hypothetically, Crux could automatically detect these cases and insert no-op predicates on the user’s behalf, but that would be a deviation from the essential semantics of Datalog. Note that clojure.core/any? is a function that always returns true.

Or Join

The or-join clause is satisfied if any of its legs are satisfied.

You declare which logic variables from outside the or-join scope are to be used in the join.

Any other logic variables within the or-join are scoped only for the join.

[{:crux.db/id :ivan :name "Ivan" :age 12} (1)
 {:crux.db/id :petr :name "Petr" :age 15}
 {:crux.db/id :sergei :name "Sergei" :age 19}]

(crux/q
 (crux/db node)
 '{:find [p]
   :where [[p :crux.db/id]
           (or-join [p] (2)
                    (and [p :age a] (3)
                         [(>= a 18)])
                    [p :name "Ivan"])]})

#{[:ivan] [:sergei]} (4)

1	Data
2	Declaration of which logic variables need to unify with the rest of the query
3	Clauses
4	Result

This will match any document, p which has an :age greater than or equal to 18 or has a :name of "Ivan".

Importantly, the logic variable a is unbound outside the or-join clauses.

Rules

See Rules

In

Crux queries can take a set of additional arguments, binding them to variables under the :in key within the query.

:in supports various kinds of binding.

Scalar binding

(crux/q
 (crux/db node)
 '{:find [e]
   :in [first-name]
   :where [[e :name first-name]]}
 "Ivan")

In the above query, we parameterize the first-name symbol, and pass in "Ivan" as our input, binding "Ivan" to first-name in the query.

Result Set:

#{[:ivan]}

Collection binding

(crux/q
 (crux/db node)
 '{:find [e]
   :in [[first-name ...]]
   :where [[e :name first-name]]}
 ["Ivan" "Petr"])

This query shows binding to a collection of inputs - in this case, binding first-name to all of the different values in a collection of first-names.

Result Set:

#{[:ivan] [:petr]}

Tuple binding

(crux/q
 (crux/db node)
 '{:find [e]
   :in [[first-name last-name]]
   :where [[e :name first-name]
           [e :last-name last-name]]}
 ["Ivan" "Ivanov"])

In this query we are binding a set of variables to a single value each, passing in a collection as our input. In this case, we are passing a collection with a first-name followed by a last-name.

Result Set:

#{[:ivan]}

Relation binding

(crux/q
 (crux/db node)
 '{:find [e]
   :in [[[first-name last-name]]]
   :where [[e :name first-name]
           [e :last-name last-name]]}
 [["Petr" "Petrov"]
  ["Smith" "Smith"]])

Here we see how we can extend the parameterisation to match using multiple fields at once by passing and destructuring a relation containing multiple tuples.

Result Set:

#{[:petr] [:smith]}

Ordering and Pagination

A Datalog query naturally returns a result set of tuples, however, the tuples can also be consumed as a sequence and therefore you will always have an implicit order available. Ordinarily this implicit order is undefined (i.e. not meaningful), because the join order and result order are unlikely to correlate.

The :order-by option is available for use in the query map to explicitly control the result order.

(crux/q
 (crux/db node)
 '{:find [time device-id temperature humidity]
   :where [[c :condition/time time]
           [c :condition/device-id device-id]
           [c :condition/temperature temperature]
           [c :condition/humidity humidity]]
   :order-by [[time :desc] [device-id :asc]]})

Use of :order-by will require that results are fully-realised by the query engine, however this happens transparently and it will automatically spill to disk when sorting large numbers of results.

Basic :offset and :limit options are supported however typical pagination use-cases will need a more comprehensive approach because :offset will naively scroll through the initial result set each time.

(crux/q
 (crux/db node)
 '{:find [time device-id temperature humidity]
   :where [[c :condition/time time]
           [c :condition/device-id device-id]
           [c :condition/temperature temperature]
           [c :condition/humidity humidity]]
   :order-by [[device-id :asc]]
   :limit 10
   :offset 90})

Ordered results are returned as bags, not sets, so you may want to deduplicate consecutive identical result tuples (e.g. using clojure.core/dedupe or similar).

:limit may be used in isolation, without :order-by, and will also return a bag of results that can contain duplicates.

More powerful ordering and pagination features may be provided in the future. Feel free to open an issue or get in touch to discuss your ordering requirements, e.g. see #1514

Rules

Rules are defined by a rule head and then clauses as you would find in a :where statement.

They can be used as a shorthand for when you would otherwise be repeating the same restrictions in your :where statement.

(crux/q
 (crux/db node)
 '{:find [p]
   :where [(adult? p)] (1)
   :rules [[(adult? p) (2)
            [p :age a] (3)
            [(>= a 18)]]]})

1	Rule usage clause (i.e. invocation)
2	Rule head (i.e. signature)
3	Rule body containing one or more clauses

The above defines the rule named adult? which checks that the supplied entity has an :age which is >= 18

Multiple rule bodies may be defined for a single rule name (i.e. using matching rule heads) which works in a similar fashion to an or-join.

The clauses within Rules can also be further Rule invocation clauses. This allows for the recursive traversal of entities and more.

(crux/q
 (crux/db node)
 '{:find [?e2]
   :in [?e1]
   :where [(follow ?e1 ?e2)]
   :rules [[(follow ?e1 ?e2)
            [?e1 :follow ?e2]]
           [(follow ?e1 ?e2)
            [?e1 :follow ?t]
            (follow ?t ?e2)]]}
 :ivan)

This example finds all entities that the entity with :name "Smith" is connected to via :follow, even if the connection is via intermediaries.

Bound arguments

To improve the performance of a rule you can specify that certain arguments in the rule head must be "bound" logic variables (i.e. there must be known values for each argument at the point of evaluation) by enclosing them in a vector in the first argument position. Any remaining arguments will be treated as regular "free" logic variables.

As an analogy, bound variables are input arguments to a function, and free variables are the destructured return values from that function.

Changes are only necessary in the rule head(s) - no changes are required in the body or the usage clauses. Rule heads must always match.

For example, the following query and rule set will work and return the correct results.

(crux/q
 (crux/db node)
 '{:find [child-name]
   :in [parent]
   :where [[parent :crux.db/id]
           (child-of parent child)
           [child :name child-name]]
   :rules [[(child-of p c)
            [p :child c]]
           [(child-of p c)
            [p :child c1]
            (child-of c1 c)]]}
 parent-id)

However, specifying that the p variable should be bound before the rule can be evaluated will improve the evalution time by many orders-of-magnitude for large data sets.

(crux/q
 (crux/db node)
 '{:find [child-name]
   :in [parent]
   :where [[parent :crux.db/id]
           (child-of parent child)
           [child :name child-name]]
   :rules [[(child-of [p] c)
            [p :child c]]
           [(child-of [p] c)
            [p :child c1]
            (child-of c1 c)]]}
 parent-id)

Timeout

:timeout sets the maximum run time of the query (in milliseconds).

If the query has not completed by this time, a java.util.concurrent.TimeoutException is thrown.

Valid Time travel

When performing a query, crux/q is called on a database snapshot.

To query based on a different Valid Time, create this snapshot by specifying the desired Valid Time when we call db on the node.

(crux/submit-tx
 node
 [[:crux.tx/put
   {:crux.db/id :malcolm :name "Malcolm" :last-name "Sparks"}
   #inst "1986-10-22"]])

(crux/submit-tx
 node
 [[:crux.tx/put
   {:crux.db/id :malcolm :name "Malcolma" :last-name "Sparks"}
   #inst "1986-10-24"]])

Here, we have put different documents in Crux with different Valid Times.

(def q
  '{:find [e]
    :where [[e :name "Malcolma"]
            [e :last-name "Sparks"]]})

Here, we have defined a query, q to find all entities with a :name of "Malcolma" and :last-name of "Sparks"

We can run the query at different Valid Times as follows

(crux/q (crux/db node #inst "1986-10-23") q)

(crux/q (crux/db node) q)

The first query will return an empty result set (#{}) because there isn’t a document with the :name "Malcolma" valid at #inst "1986-10-23"

The second query will return #{[:malcolm]} because the document with :name "Malcolma" is valid at the current time. This will be the case so long as there are no newer versions (in the valid time axis) of the document that affect the current valid time version.

Joins

Query: "Join across entities on a single attribute"

Given the following documents in the database

[{:crux.db/id :ivan :name "Ivan"}
 {:crux.db/id :petr :name "Petr"}
 {:crux.db/id :sergei :name "Sergei"}
 {:crux.db/id :denis-a :name "Denis"}
 {:crux.db/id :denis-b :name "Denis"}]

We can run a query to return a set of tuples that satisfy the join on the attribute :name

(crux/q
 (crux/db node)
 '{:find [p1 p2]
   :where [[p1 :name n]
           [p2 :name n]]})

Result Set:

#{[:ivan :ivan]
  [:petr :petr]
  [:sergei :sergei]
  [:denis-a :denis-a]
  [:denis-b :denis-b]
  [:denis-a :denis-b]
  [:denis-b :denis-a]}

Note that every person joins once, plus 2 more matches.

Query: "Join with two attributes, including a multi-valued attribute"

Given the following documents in the database

[{:crux.db/id :ivan :name "Ivan" :last-name "Ivanov"}
 {:crux.db/id :petr :name "Petr" :follows #{"Ivanov"}}]

We can run a query to return a set of entities that :follows the set of entities with the :name value of "Ivan"

(crux/q
 (crux/db node)
 '{:find [e2]
   :where [[e :last-name l]
           [e2 :follows l]
           [e :name "Ivan"]]})

Result Set:

#{[:petr]}

Note that because Crux is schemaless there is no need to have elsewhere declared that the :follows attribute may take a value of edn type set.

Streaming Queries

Query results can also be streamed, particularly for queries whose results may not fit into memory. For these, we use crux.api/open-q, which returns a Closeable sequence. Note that results are returned as bags, not sets, so you may wish to deduplicate consecutive identical result tuples (e.g. using clojure.core/dedupe or similar).

We’d recommend using with-open to ensure that the sequence is closed properly. Additionally, ensure that the sequence (as much of it as you need) is eagerly consumed within the with-open block - attempting to use it outside (either explicitly, or by accidentally returning a lazy sequence from the with-open block) will result in undefined behaviour.

(with-open [res (crux/open-q (crux/db node)
                             '{:find [p1]
                               :where [[p1 :name n]
                                       [p1 :last-name n]
                                       [p1 :name "Smith"]]})]
  (doseq [tuple (iterator-seq res)]
    (prn tuple)))

History API

Full Entity History

Crux allows you to retrieve all versions of a given entity:

(api/submit-tx
  node
  [[:crux.tx/put
    {:crux.db/id :ids.persons/Jeff
     :person/name "Jeff"
     :person/wealth 100}
    #inst "2018-05-18T09:20:27.966"]
   [:crux.tx/put
    {:crux.db/id :ids.persons/Jeff
     :person/name "Jeff"
     :person/wealth 1000}
    #inst "2015-05-18T09:20:27.966"]])

; yields
{:crux.tx/tx-id 1555314836178,
 :crux.tx/tx-time #inst "2019-04-15T07:53:56.178-00:00"}

; Returning the history in descending order
; To return in ascending order, use :asc in place of :desc
(api/entity-history (api/db node) :ids.persons/Jeff :desc)

; yields
[{:crux.tx/tx-time #inst "2019-04-15T07:53:55.817-00:00",
  :crux.tx/tx-id 1555314835817,
  :crux.db/valid-time #inst "2018-05-18T09:20:27.966-00:00",
  :crux.db/content-hash ; sha1 hash of document contents
  "6ca48d3bf05a16cd8d30e6b466f76d5cc281b561"}
 {:crux.tx/tx-time #inst "2019-04-15T07:53:56.178-00:00",
  :crux.tx/tx-id 1555314836178,
  :crux.db/valid-time #inst "2015-05-18T09:20:27.966-00:00",
  :crux.db/content-hash "a95f149636e0a10a78452298e2135791c0203529"}]

Retrieving previous documents

When retrieving the previous versions of an entity, you have the option to additionally return the documents associated with those versions (by using :with-docs? in the additional options map)

(api/entity-history (api/db node) :ids.persons/Jeff :desc {:with-docs? true})

; yields
[{:crux.tx/tx-time #inst "2019-04-15T07:53:55.817-00:00",
  :crux.tx/tx-id 1555314835817,
  :crux.db/valid-time #inst "2018-05-18T09:20:27.966-00:00",
  :crux.db/content-hash
  "6ca48d3bf05a16cd8d30e6b466f76d5cc281b561"
  :crux.db/doc
  {:crux.db/id :ids.persons/Jeff
   :person/name "Jeff"
   :person/wealth 100}}
 {:crux.tx/tx-time #inst "2019-04-15T07:53:56.178-00:00",
  :crux.tx/tx-id 1555314836178,
  :crux.db/valid-time #inst "2015-05-18T09:20:27.966-00:00",
  :crux.db/content-hash "a95f149636e0a10a78452298e2135791c0203529"
  :crux.db/doc
  {:crux.db/id :ids.persons/Jeff
   :person/name "Jeff"
   :person/wealth 1000}}]

Document History Range

Retrievable entity versions can be bounded by four time coordinates:

valid-time-start
tx-time-start
valid-time-end
tx-time-end

All coordinates are inclusive. All coordinates can be null.

; Passing the additional 'opts' map with the start/end bounds.
; As we are returning results in :asc order, the map contains the earlier starting coordinates -
; If returning history range in descending order, we pass the later coordinates as start coordinates to the map
(api/entity-history
 (api/db node)
 :ids.persons/Jeff
 :asc
 {:start-valid-time #inst "2015-05-18T09:20:27.966"
  :start-tx-time #inst "2015-05-18T09:20:27.966"
  :end-valid-time #inst "2020-05-18T09:20:27.966"
  :end-tx-time #inst "2020-05-18T09:20:27.966"})

; yields
[{:crux.tx/tx-time #inst "2019-04-15T07:53:56.178-00:00",
  :crux.tx/tx-id 1555314836178,
  :crux.db/valid-time #inst "2015-05-18T09:20:27.966-00:00",
  :crux.db/content-hash
  "a95f149636e0a10a78452298e2135791c0203529"}
 {:crux.tx/tx-time #inst "2019-04-15T07:53:55.817-00:00",
  :crux.tx/tx-id 1555314835817
  :crux.db/valid-time #inst "2018-05-18T09:20:27.966-00:00",
  :crux.db/content-hash "6ca48d3bf05a16cd8d30e6b466f76d5cc281b561"}]

Clojure Tips

Quoting

Logic variables used in queries must always be quoted in the :find and :where clauses, which in the most minimal case could look like the following:

(crux/q db
  {:find ['?e]
   :where [['?e :event/employee-code '?code]]}))

However it is often convenient to quote entire clauses or even the entire query map rather than each individual use of every logic variable, for instance:

(crux/q db
  '{:find [?e]
    :where [[?e :event/employee-code ?code]]}))

Maps and Vectors in data

Say you have a document like so and you want to add it to a Crux db:

{:crux.db/id :me
 :list ["carrots" "peas" "shampoo"]
 :pockets {:left ["lint" "change"]
           :right ["phone"]}}

Crux breaks down vectors into individual components so the query engine is able see all elements on the base level. As a result of this the query engine is not required to traverse any structures or any other types of search algorithm which would slow the query down. The same thing should apply for maps so instead of doing :pocket {:left thing :right thing} you should put them under a namespace, instead structuring the data as :pocket/left thing :pocket/right thing to put the data all on the base level. Like so:

(crux/submit-tx
  node
  [[:crux.tx/put
    {:crux.db/id :me
     :list ["carrots" "peas" "shampoo"]
     :pockets/left ["lint" "change"]
     :pockets/right ["phone"]}]
   [:crux.tx/put
    {:crux.db/id :you
     :list ["carrots" "tomatoes" "wig"]
     :pockets/left ["wallet" "watch"]
     :pockets/right ["spectacles"]}]])

To query inside these vectors the code would be:

(crux/q (crux/db node) '{:find [e l]
                         :where [[e :list l]]
                         :in [l]}
                       "carrots")
;; => #{[:you "carrots"] [:me "carrots"]}

(crux/q (crux/db node) '{:find [e p]
                         :where [[e :pockets/left p]]
                         :in [p]}
                       "watch")
;; => #{[:you "watch"]}

Note that l and p is returned as a single element as Crux decomposes the vector

DataScript Differences

This list is not necessarily exhaustive and is based on the partial re-usage of DataScript’s query test suite within Crux’s query tests.

Crux does not support:

vars in the attribute position, such as [e ?a "Ivan"] or [e _ "Ivan"]

Crux does not yet support:

ground (you can use alternatively use identity)
get-else (see get-attr which returns a relation instead)
get-some
missing? (however, instead of [(missing? $ ?e :height)] you can use (not-join [?e] [?e :height]))
backref attribute syntax (i.e. [?child :example/_child ?parent])

Custom Functions and Advanced Examples

Many advanced query requirements can be addressed using custom predicate function calls since you can reference any function that is loaded by using its fully qualified name in a Datalog clause, e.g. [(clojure.core/+ x y) z)]. These custom functions can be passed the db query context using the $ argument convention for sophisticated nesting of queries and other lookups. Destructuring the results of a function as a relation is also supported, similarly to :in bindings.

Be aware during development that query compilation internalizes function definitions and this means that subsequent re-definitions of your custom functions may not be reflected until the query is modified and therefore re-compiled.

Many examples of advanced queries are showcased across the query tests and the suite of benchmarks maintained in the crux-bench sub-project.

Reference

Queries

Introduction

SQL

Datalog

Basic Structure

Find

Logic Variable

Aggregates

Example:

Custom Aggregates

Pull

Attribute parameters

Returning maps

Where

Clause Inputs

Triple

Predicates

Subqueries

Binding results as a scalar

Binding results as a tuple

Range Predicate

Unification Predicate

Not

Not Join

Or

Or Join

Rules

In

Scalar binding

Collection binding

Tuple binding

Relation binding

Ordering and Pagination

Rules

Bound arguments

Timeout

Valid Time travel

Joins

Query: "Join across entities on a single attribute"

Query: "Join with two attributes, including a multi-valued attribute"

Streaming Queries

History API

Full Entity History

Retrieving previous documents

Document History Range

Clojure Tips

Quoting

Maps and Vectors in data

DataScript Differences

Custom Functions and Advanced Examples