Where Clauses

The where clause filters data and establishes variable bindings for use in the select clause.

`where`

required?	type
no	`array` or `object`

You can think of a FlureeQL query executing in two phases: the where phase and the select phase. The where phase filters and returns sets of bound values that the select phase can use to construct JSON objects. The where clause may return node subject ?bindings that we then use with select expressions like "*" to perform graph crawls from those subject nodes.

The where clause may also retrieve all the bindings we need, and we simply instruct the select clause that we want those bindings returned directly as query results.

In any case, in addition to establishing logic variables for bound values, we use the where clause to establish various data constraints for the data we are interested in querying (e.g. filtering by a particular predicate value, or expressing optional and outer-join data conditions).

When a where clause is an array, it can combine a series of where conditions and where operations.

Where Condition

A where condition describes relationships between nodes that must be satisfied for the nodes to be included in result sets, and it names those sets.

For example, in this where condition:

{
  "@id": "?s",
  "schema:name": "Sheil"
}

a set of subjects that have a "schema:name" equal to "Sheil" is bound to the logic variable named "?s".

In this where condition:

{
  "@id": "?s",
  "schema:name": "?name"
}

two sets become available to select:

"?s" is the set of nodes that have any data on the predicate, "schema:name"
"?name" is the set of value nodes that the "schema:name" predicate points to

Multiple where conditions are implicitly joined with a logical AND. For example, given these two where conditions:

{
  "@id": "?s",
  "schema:name": "Freddie",
  "schema:familyName": "Mercury"
}

the set "?s" would include all nodes that have both a "schema:name" of "Freddie" and a "schema:familyName" of "Mercury".

A node that satisfies ONE of those conditions but not BOTH would NOT be bound to ?s

Successive `where` conditions

A where clause can express multiple, successive where conditions as either an array of objects or as nested, related objects.

The following two examples are effectively equivalent:

{
  "where": [
    {
      "@id": "?s",
      "bestFriend": "?friend"
    },
    {
      "@id": "?friend",
      "schema:name": "?name"
    }
  ]
}

{
  "where": {
    "@id": "?s",
    "bestFriend": {
      "@id": "?friend",
      "schema:name": "?name"
    }
  }
}

info

In the second example above, because the conditions on ?s and ?friend are nested in a single, hierarchical JSON object, we could even remove the "@id": "?friend" line, if we didn't need to use it in the select statement. We need it in the first example to relate the two nodes to one another, but in the second example, we could have just as easily used:

{
  "where": {
    "@id": "?s",
    "bestFriend": {
      "schema:name": "?name"
    }
  }
}

Finally, it is possible in where conditions to express a logic variable for a set of properties matching a particular condition. This is commonly done for one of two reasons:

We have identified a subject IRI, and now want all the properties with object values on that entity, or
We have an object value, and now we want all the properties that have that value.

Example of a where condition that uses a logic variable for a set of properties

{
  "where": {
    "@id": "http://example.org/jack",
    "?p": "?o"
  }
}

Where Operations

Where operations allow you to express more complex behavior than where conditions. There are four such operations:

"union"
"filter"
"bind"
"optional"

Where operations are represented as arrays, where the first element is the name of the operation, and the subsequent elements are arguments to the operation, for example:

...
[
  "optional",
    { "@id": "?s", "schema:name": "?name" },
    { "@id": "?s", "schema:age": "?age" }
],
...

`filter`

The "filter" operation lets you make comparisons beyond simple equality. For example, if you wanted to select all people older than 45 and younger than 50, you could write this:

{
  "where": [
    { "@id": "?s", "schema/age": "?age" },
    ["filter", "(> ?age 45)", "(< ?age 50)"]
  ],
  "select": { "?s": ["*"] }
}

Filter expressions

Filter expressions are strings that represent lisp-like expressions which can use your logic variables to return boolean values. When you have multiple filter expressions, an implicit AND is used to join them.

Take this example:

["filter", "(> ?age 45)", "(< ?age 50)"]

With lisp, expressions are enclosed in parentheses. You can think of the first element of the expression as a function, and the rest of the elements as arguments. In the first filter expression, "(> ?age 45)", > (greater than) is the function, and the arguments are ?age and 45. In English this would read as ?age is greater than 45.

Inside of each filter expression, you can refer to the logic variables that you've bound elsewhere in your where clause (i.e. the way that we use ?age as a variable in the example above).

See the full list of valid functions you can use in filter expressions.

`union`

The "union" operation allows you to express boolean OR logic (or in query terms, perform a full outer join). Take this query:

{
  "where": {
    "@id": "?s",
    "@type": "ex:Person",
    "schema:email": "?email",
    "ex:email": "?email"
  },
  ...
}

In the query above, if an entity does not have BOTH a value on schema:email and a value on ex:email AND if those two values are not identical (because we express ?email for both values), then that entity will not be bound to ?s.

But we can easily imagine a scenario where we know some entities have a value on schema:email and some have a value on ex:email, and regardless of which one they have, we want to return them in our query results.

This is where we can use union to express a logical OR condition (aka perform a full outer join on values of those two properties):

{
  "@context": { "schema": "http://schema.org/", "ex": "http://example.org/" },
  "where": [
    { "@id": "?s", "@type": "ex:Person" },
    [
      "union",
      { "@id": "?s", "schema:email": "?email" },
      { "@id": "?s", "ex:email": "?email" }
    ]
  ],
  "select": ["?s", "?email"]
}

In English, this query reads:

Return all subjects "?s" such that:
the subject has an @type (i.e. "rdf/type") of "ex:Person"
AND the subject
- EITHER has the property "schema:email" (bind its value to "?email")
- OR has the property "ex:email" (also bind its value to "?email")
- OR has both properties

`bind`

The "bind" operation allows you to bind a logic variable to a value. This can be useful if wanting to set a binding to the return value of a functional operation. We can use the same functions that we used in our filter expressions above.

Like filter and union, bind is an array where the first element is the name of the operation. In the case of bind, the following pairs of elements are the logic variable to bind and the expression to evaluate.

["bind", "?canVote", "(>= ?age 18)"]

Example of a where operation that uses bind

{
  "@context": { "schema": "http://schema.org/" },
  "where": [
    { "@id": "?s", "schema:age": "?age" },
    ["bind", "?canVote", "(>= ?age 18)"]
  ],
  "select": ["?s", "?age", "?canVote"]
}

See the full list of valid functions you can use in bind expressions.

`optional`

The "optional" operation allows you to use logic variable bindings for values that may or may not exist. This is similar to a left outer join in SQL.

For example, if you wanted to select all people and their best friends, but if you didn't want the query results to implicitly filter out people who had no values on bestFriend you could write this:

{
  "@context": { "ex": "http://example.org/" },
  "where": [
    { "@id": "?s", "@type": "ex:Person" },
    ["optional", { "@id": "?s", "bestFriend": "?friend" }]
  ],
  "select": ["?s", "?friend"]
}

When the where clause of the above query executes, ?s will be bound to all entities of @type, ex:Person. If we had NOT used optional on the next line, then any ?s entities with no value on bestFriend would be implicitly dropped from the ?s result set. But, because we used optional, those entities will be retained in the ?s result set, and ?friend will be bound to null for those entities.

optional, like filter, union, and bind, is an array where the first element is the name of the operation. In the case of optional, the following elements are the where conditions to evaluate.

NOTE: There is a difference between the following two optional operations:

["optional", { "@id": "?s", "bestFriend": "?friend"}, { "@id": "?s", "name": "?name"}]

["optional", { "@id": "?s", "bestFriend": "?friend", "name": "?name"}]

In #1, an entity could have a value on either bestFriend or name, and that value would then be bound to ?friend or ?name, respectively (the other would be null).

In #2, if an entity had only a value on either bestFriend or name, then BOTH ?friend and ?name would be bound to null.

info

If you are used to SQL databases and queries, you might be confused why we call this operation optional. In SQL, it is possible to persist null values on a column for a particular row.

It is important to remember that JSON-LD expresses a serialization of RDF triples. In the world of RDF triples, there are not null values. If a subject does not have a value on a particular predicate, then that subject simply does not have a triple with that predicate--or said differently, the triple simply does not exist.

When we use optional, we are saying "if this triple exists, then bind the value to the logic variable, otherwise bind null to the logic variable."

Valid Functions for Use in Filter and Bind Expressions

function	description	example
`and`	logical AND (same as `&&`)	`(and ?isRegistered ?canVote)`
`&&`	logical AND (same as `and`)	`(&& ?isRegistered ?canVote)`
`\|\|`	logical OR	`(\|\| ?isAnAdult ?canVote)`
`!`	logical NOT (same as `not`)	`(! (strStarts ?url \"http\"))`
`not`	logical NOT (same as `!`)	`(not (strStarts ?url \"http\"))`
`=`	equal	`(= ?age 45)`
`not=`	not equal	`(not= ?age 45)`
`>`	greater than	`(> ?age 45)`
`>=`	greater than or equal	`(>= ?age 45)`
`<`	less than	`(< ?age 45)`
`<=`	less than or equal	`(<= ?age 45)`
`+`	addition	`(< (+ ?age 10) 45)`
`-`	subtraction	`(< (- ?age 10) 45)`
`*`	multiplication	`(< (* ?age 2) 45)`
`/`	division	`(< (/ ?age 2) 45)`
`quot`	integer division	`(< (quot ?age 2) 45)`
`abs`	absolute value	`(> (abs ?age) 45)`
`round`	round to nearest integer	`(> (round ?age) 45)`
`ceil`	round up to nearest integer	`(> (ceil ?age) 45)`
`floor`	round down to nearest integer	`(> (floor ?age) 45)`
`bound`	returns true if logic variable is bound	`(bound ?age)`
`coalesce`	returns first non-null value	`(> (coalesce ?age ?defaultAge) 45)`
`nil?`	returns true if value is null	`(nil? ?age)`
`re-pattern`	returns a regex pattern, for use with `re-find`	`(re-find (re-pattern \"^http\") ?url)`
`re-find`	returns true for regex matches	`(re-find (re-pattern \"^http\") ?url)`
`strStarts`	returns true if string starts with substring	`(strStarts ?url \"http\")`
`strEnds`	returns true if string ends with substring	`(strEnds ?url \".com\")`
`strLen`	returns length of string	`(> (strLen ?name) 5)`
`contains`	returns true if string contains substring	`(contains ?name \"fred\")`
`now`	returns the current time	`(> ?date (now))`
`if`	conditional	`(if (> ?nums 30) (> ?age 100) true)`
`isNumeric`	returns true if value is numeric	`(isNumeric ?age)`
`in`	check if value is in a collection	`(in ?x [1 2 3])`
`str`	convert to string	`(str ?value)`
`as`	alias an expression (used in select with aggregates)	`(as (avg ?x) ?avgX)`

Next Steps

Aggregations & Grouping — Group and aggregate results
Modifiers — Order, limit, and paginate results

where​

Where Condition​

Successive where conditions​

Where Operations​

filter​

Filter expressions​

union​

bind​

optional​

Valid Functions for Use in Filter and Bind Expressions​

Next Steps​