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 {
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:
Blob
IntervalDaySecond
IntervalYearMonth
Xml
Besides these custom scalar types, GraphQL object types originated from VDP register
and array data types are not supported either as input fields.
Also, fields cannot be specified at the same time in the _filter argument
and in the field selection set:
Invalid query with complex filtering¶
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"
}
}
}
]
}
}