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:
{
"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 {
containsand: String
containsor: String
eq: String
field: String!
gt: String
gte: String
in: String
iscontained: 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!]
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 Limitations¶
The following custom scalar types are not supported as an input field in this kind of filtering:
Array
Blob
IntervalDaySecond
IntervalYearMonth
Struct
Xml
Also, fields cannot be specified at the same time in the _filter
argument
and in the field selection set:
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:
{
"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:
{
"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.
{
"data": {
"film": [
{
"_aggregation": {
"count": 1000,
"max1": {
"field": "rental_rate",
"value": "4.99"
},
"max2": {
"field": "rental_duration",
"value": "7"
},
"max3": {
"field": "length",
"value": "185"
}
}
}
]
}
}