Advanced Querying¶
Beyond the GraphQL standard features, Denodo GraphQL Service allows to
specify complex filters, sort data, group results and calculate aggregation
functions when the configuration
property graphql.enable.filtering
is true
.
Filtering¶
When graphql.enable.filtering
is true
, a new optional argument
_filter
is added to each query definition in the schema generated by
Denodo GraphQL Service.
This way, the user can query the GraphQL Service using conditions other
than equality and combine them using the and
, or
and not
operators.
In the query of the example below, we select the films that have a rental duration equal to 3 days and a rental rate greater than 3:

Query with complex filtering¶
{
"data": {
"film": [
{
"film_id": 2,
"title": "ACE GOLDFINGER",
"description": "An Astounding Epistle of a
Database Administrator And an Explorer who
must Find a Car in Ancient China",
"length": 48,
"rental_duration": 3,
"rental_rate": 4.99
},
...
]
}
}
Besides the _filter
argument the required input types are added to the
schema:
type Query {
order(cust_id: Int, order_id: Int, total_price: BigInteger, order_desc: String, order_date: Timestamp, last_updated_time: Timestamp, _first: Int, _offset: Int, _filter: FilterInput, _orderBy: [SortInput!], _groupBy: [String!]): [order]!
}
# Only one is allowed: eq | neq | gt | gte | lt | lte | in | isnull | like | regexp_like | regexp_ilike
input FilterExpressionInput {
eq: String
field: String!
gt: String
gte: String
in: String
isnull: String
like: String
lt: String
lte: String
neq: String
regexp_ilike: String
regexp_like: String
}
# Only one is allowed: and | expression | not | or
input FilterInput {
and: [FilterInput!]
any: AnyFilterInput
expression: FilterExpressionInput
not: FilterInput
or: [FilterInput!]
}
The comparison operators that can be used in complex filtering are the
ones defined in the input type FilterExpressionInput
and are explained
in Comparison Operators.
Filtering on Complex Fields¶
Filtering on GraphQL list and object type fields (those coming from
associated entities and register
or array
types in VDP) is possible
by specifying the conditions on their respective subfields.
Although no extra query arguments are added to the entities schema for these
fields, it is possible to filter on them using the _filter
argument.
Filtering on Object Fields¶
Filtering individual (non-list) GraphQL object fields is very similar to
filtering any other scalar field. To filter on a specific subfield,
concatenate the object field name with each subfield name, separated by a
.
character. This can apply to subfields at any level of nesting.
For example, the following query will obtain all the orders effectuated by customers whose name is “Carl” and that are older than 30:
order (_filter: {
and: [
{ expression: {field: "customer.name", like: "Carl"},
{ expression: {field: "customer.age", gte: "30"},
]) {
order_id
order_desc
customer{
customer_id
name
age
}
}
Filtering on List Fields¶
On the other hand, filtering on list fields (arrays) can be done by using
any
expressions, inside the _filter
argument.
The any
expression can be used to check if at least one element in a
list meets a specific condition. It returns true if the condition
is satisfied by at least one item and false if none meet the condition.
The list field over which we are filtering is specified in the mandatory
array_field
argument.
any
filter operator¶ "Only one is allowed: and | expression | not | or"
input AnyFilterInput {
and: [FilterInput!]
array_field: String!
expression: FilterExpressionInput
not: FilterInput
or: [FilterInput!]
}
For example, to obtain all the orders that contain any product named “Frying pan” and with a price of less or equal than 15, we would write the following query:
order (_filter: {
any: { array_field: "products", and: [
{ expression: {field: "_item.name", like: "Frying pan"},
{ expression: {field: "_item.price", lte: "15"},
]}) {
order_id
order_desc
products{
prod_id
name
price
}
}
This function has some restrictions:
All conditions within
any
expressions must involvearray_field
subfields. The subfield names must be relative to thearray_field
list and must be prefixed with_item.
like in the above example.Only one
any
expression perarray_field
is allowed by_filter
argument.any
expressions cannot be children ofnot
operators at any level.
Filtering Limitations¶
The following custom scalar types are not supported as an input field in this kind of filtering:
Blob
IntervalDaySecond
IntervalYearMonth
Xml
Fields cannot be specified at the same time in the _filter
argument
and in the field selection set:

Invalid query with complex filtering¶
Besides, complex fields coming from VDP register
and array
types
inside associated entities cannot be filtered on.
Sorting¶
When graphql.enable.filtering
is true
, a new argument _orderby
is
added to each query in the schema generated by Denodo GraphQL Service.
This way, the user can sort data when querying the GraphQL Service.
In the query of the example below, we sort films by their length in a descending order and by their rental rate in ascending order:

Query with sorting¶
{
"data": {
"film": [
…
{
"film_id": 991,
"title": "WORST BANGER",
"description": "A Thrilling Drama of a Madman And
a Dentist who must Conquer a Boy in The
Outback",
"length": 185,
"release_year": "2006-01-01",
"rental_rate": 2.99
},
{
"film_id": 141,
"title": "CHICAGO NORTH",
"description": "A Fateful Yarn of a Mad Cow And a
Waitress who must Battle a Student in
California",
"length": 185,
"release_year": "2006-01-01",
"rental_rate": 4.99
},
...
]
}
}
Besides the _orderby
argument the required input type is added to the
schema:
type Query {
order(cust_id: Int, order_id: Int, total_price: BigInteger, order_desc: String, order_date: Timestamp, last_updated_time: Timestamp, _first: Int, _offset: Int, _filter: FilterInput, _orderBy: [SortInput!], _groupBy: [String!]): [order]!
}
#Only one is allowed: asc | desc
input SortInput {
asc: String
desc: String
}
Group BY and Aggregation Functions¶
In the schema generated by Denodo GraphQL Service, when
graphql.enable.filtering
is true
, a new argument _groupBy
is
added to each query and an _aggregation
field is added to each view type.
This way, users can group the results and calculate aggregate values (average, sum, max, …) of their data.
In the query of the example below, we retrieve the average rental rate according to the length of films:

Query with group by and aggregation functions¶
{
"data": {
"film": [
{
"_aggregation": [
{
"avg": {
"field": "rental_rate",
"value": 2.59
}
}
"length": 46
},
...
]
}
}
Besides the _groupBy
argument and the _aggregation
field, the required
types are added to the schema:
type Query {
order(cust_id: Int, order_id: Int, total_price: BigInteger, order_desc: String, order_date: Timestamp, last_updated_time: Timestamp, _first: Int, _offset: Int, _filter: FilterInput, _orderBy: [SortInput!], _groupBy: [String!]): [order]!
}
type order {
cust_id: Int
order_id: Int
total_price: BigInteger
order_desc: String
last_updated_time: Timestamp
order_date: Timestamp
order_detail: [order_detail]! # Denodo association
_aggregation: AggregationFunction!
}
type Aggregated {
field: String!
value: String!
}
type AggregationFunction {
avg(field: String): Aggregated
count: BigInteger
first(field: String): Aggregated
last(field: String): Aggregated
max(field: String): Aggregated
median(field: String): Aggregated
min(field: String): Aggregated
stdev(field: String): Aggregated
stdevp(field: String): Aggregated
sum(field: String): Aggregated
var(field: String): Aggregated
varp(field: String): Aggregated
}
The aggregation functions that can be used are the ones defined in the type
AggregationFunction
and are explained in
Aggregation Functions.
Using an Aggregation Function More than Once in a Query¶
As functions are fields in the AggregationFunction
type, we are not able
to use the same aggregation function more than once unless we use the alias
mechanism provided by GraphQL. Naming each function call with a unique name
inside the _aggregation
clause will let you repeat any function.

Query with a repeated aggregation function (max)¶
{
"data": {
"film": [
{
"_aggregation": {
"count": 1000,
"max1": {
"field": "rental_rate",
"value": "4.99"
},
"max2": {
"field": "rental_duration",
"value": "7"
},
"max3": {
"field": "length",
"value": "185"
}
}
}
]
}
}