Filtering
A Query can specify a predicate expression which should be used to filter rows in the response.
A predicate expression can be one of
- An application of a comparison operator to a column and a value, or
- An
EXISTSexpression, or - A conjunction of other expressions, or
- A disjunction of other expressions, or
- A negation of another expression
The predicate expression is specified in the predicate field of the Query object.
Comparison Operators
Unary Operators
Unary comparison operators are denoted by expressions with a type field of unary_comparison_operator.
The only supported unary operator currently is is_null, which return true when a column value is null:
{
"type": "unary_comparison_operator",
"operator": "is_null",
"column": {
"name": "title"
}
}
Binary Operators
Binary comparison operators are denoted by expressions with a type field of binary_comparison_operator.
The set of available operators depends on the type of the column involved in the expression. The operator property should specify the name of one of the binary operators from the field's scalar type definition.
The type ComparisonValue describes the valid inhabitants of the value field. The value field should be an expression which evaluates to a value whose type is compatible with the definition of the comparison operator.
Equality Operators
This example makes use of an eq operator, which is defined using the equal semantics, to test a single column for equality with a scalar value:
{
"collection": "articles",
"arguments": {},
"query": {
"fields": {
"id": {
"type": "column",
"column": "id"
},
"title": {
"type": "column",
"column": "title"
}
},
"predicate": {
"type": "binary_comparison_operator",
"column": {
"type": "column",
"name": "id",
"path": []
},
"operator": "eq",
"value": {
"type": "scalar",
"value": 1
}
}
},
"collection_relationships": {}
}
Set Membership Operators
This example uses an in operator, which is defined using the in semantics, to test a single column for membership in a set of values:
{
"collection": "articles",
"arguments": {},
"query": {
"fields": {
"id": {
"type": "column",
"column": "id"
},
"title": {
"type": "column",
"column": "title"
}
},
"predicate": {
"type": "binary_comparison_operator",
"column": {
"type": "column",
"name": "author_id",
"path": []
},
"operator": "in",
"value": {
"type": "scalar",
"value": [1, 2]
}
}
},
"collection_relationships": {}
}
Custom Operators
This example uses a custom like operator:
{
"collection": "articles",
"arguments": {},
"query": {
"fields": {
"id": {
"type": "column",
"column": "id"
},
"title": {
"type": "column",
"column": "title"
}
},
"predicate": {
"type": "binary_comparison_operator",
"column": {
"type": "column",
"name": "title",
"path": []
},
"operator": "like",
"value": {
"type": "scalar",
"value": "Functional"
}
}
},
"collection_relationships": {}
}
Columns in Operators
Comparison operators compare columns to values. The column on the left hand side of any operator is described by a ComparisonTarget, and the various cases will be explained next.
Referencing a column from the same collection
If the ComparisonTarget has type column, and the path property is empty, then the name property refers to a column in the current collection.
Referencing a column from a related collection
If the ComparisonTarget has type column, and the path property is non-empty, then the name property refers to column in a related collection. The path consists of a collection of PathElements, each of which references a named relationship, any collection arguments, and a predicate expression to be applied to any relevant rows in the related collection.
When a PathElement references an array relationship, the enclosing operator should be considered existentially quantified over all related rows.
Referencing a column from the root collection
If the ComparisonTarget has type root_collection_column, then the name property refers to a column in the root collection.
The root collection is defined as the collection in scope at the nearest enclosing Query, and the column should be chosen from the row in that collection which was in scope when that Query was being evaluated.
Referencing nested fields within columns
If the field_path property is empty or not present then the target is the value of the named column.
If field_path is non-empty then it refers to a path to a nested field within the named column.
(A ComparisonTarget may only have a non-empty field_path if the connector supports capability query.nested_fields.filter_by.)
Values in Binary Operators
Binary (including array-valued) operators compare columns to values, but there are several types of valid values:
- Scalar values, as seen in the examples above, compare the column to a specific value,
- Variable values compare the column to the current value of a variable,
- Column values compare the column to another column, possibly selected from a different collection. Column values are also described by a
ComparisonTarget.
EXISTS expressions
An EXISTS expression tests whether a row exists in some possibly-related collection, and is denoted by an expression with a type field of exists.
EXISTS expressions can query related or unrelated collections.
Related Collections
Related collections are related to the original collection by a relationship in the collection_relationships field of the top-level QueryRequest.
For example, this query fetches authors who have written articles whose titles contain the string "Functional":
{
"collection": "authors",
"arguments": {},
"query": {
"fields": {
"first_name": {
"type": "column",
"column": "first_name"
},
"last_name": {
"type": "column",
"column": "last_name"
},
"articles": {
"type": "relationship",
"arguments": {},
"relationship": "author_articles",
"query": {
"fields": {
"id": {
"type": "column",
"column": "id"
},
"title": {
"type": "column",
"column": "title"
}
}
}
}
},
"predicate": {
"type": "exists",
"in_collection": {
"type": "related",
"arguments": {},
"relationship": "author_articles"
},
"predicate": {
"type": "binary_comparison_operator",
"column": {
"type": "column",
"name": "title",
"path": []
},
"operator": "like",
"value": {
"type": "scalar",
"value": "Functional"
}
}
}
},
"collection_relationships": {
"author_articles": {
"arguments": {},
"column_mapping": {
"id": "author_id"
},
"relationship_type": "array",
"source_collection_or_type": "author",
"target_collection": "articles"
}
}
}
Unrelated Collections
{
"collection": "authors",
"arguments": {},
"query": {
"fields": {
"first_name": {
"type": "column",
"column": "first_name"
},
"last_name": {
"type": "column",
"column": "last_name"
},
"articles": {
"type": "relationship",
"arguments": {},
"relationship": "author_articles",
"query": {
"fields": {
"id": {
"type": "column",
"column": "id"
},
"title": {
"type": "column",
"column": "title"
}
}
}
}
},
"predicate": {
"type": "exists",
"in_collection": {
"type": "unrelated",
"arguments": {},
"collection": "articles"
},
"predicate": {
"type": "and",
"expressions": [
{
"type": "binary_comparison_operator",
"column": {
"type": "column",
"name": "author_id",
"path": []
},
"operator": "eq",
"value": {
"type": "column",
"column": {
"type": "root_collection_column",
"name": "id"
}
}
},
{
"type": "binary_comparison_operator",
"column": {
"type": "column",
"name": "title",
"path": []
},
"operator": "like",
"value": {
"type": "scalar",
"value": "Functional"
}
}
]
}
}
},
"collection_relationships": {
"author_articles": {
"arguments": {},
"column_mapping": {
"id": "author_id"
},
"relationship_type": "array",
"source_collection_or_type": "author",
"target_collection": "articles"
}
}
}
Nested Collections
If the query.exists.nested_collections capability is enabled, then exists expressions can reference nested collections.
For example, this query finds institutions which employ at least one staff member whose last name contains the letter s:
{
"collection": "institutions",
"arguments": {},
"query": {
"fields": {
"id": {
"type": "column",
"column": "id"
},
"name": {
"type": "column",
"column": "name"
},
"staff": {
"type": "column",
"column": "staff",
"arguments": {
"limit": {
"type": "literal",
"value": null
}
}
}
},
"predicate": {
"type": "exists",
"in_collection": {
"type": "nested_collection",
"arguments": {
"limit": {
"type": "literal",
"value": null
}
},
"column_name": "staff"
},
"predicate": {
"type": "binary_comparison_operator",
"column": {
"type": "column",
"name": "last_name",
"path": []
},
"operator": "like",
"value": {
"type": "scalar",
"value": "s"
}
}
}
},
"collection_relationships": {
}
}
Conjunction of expressions
To express the conjunction of multiple expressions, specify a type field of and, and provide the expressions in the expressions field.
For example, to test if the first_name column is null and the last_name column is also null:
{
"type": "and",
"expressions": [
{
"type": "unary_comparison_operator",
"operator": "is_null",
"column": {
"name": "first_name"
}
},
{
"type": "unary_comparison_operator",
"operator": "is_null",
"column": {
"name": "last_name"
}
}
]
}
Disjunction of expressions
To express the disjunction of multiple expressions, specify a type field of or, and provide the expressions in the expressions field.
For example, to test if the first_name column is null or the last_name column is also null:
{
"type": "or",
"expressions": [
{
"type": "unary_comparison_operator",
"operator": "is_null",
"column": {
"name": "first_name"
}
},
{
"type": "unary_comparison_operator",
"operator": "is_null",
"column": {
"name": "last_name"
}
}
]
}
Negation
To express the negation of an expressions, specify a type field of not, and provide that expression in the expression field.
For example, to test if the first_name column is not null:
{
"type": "not",
"expression": {
"type": "unary_comparison_operator",
"operator": "is_null",
"column": {
"name": "first_name"
}
}
}
See also
- Type
Expression