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¶

Response for 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:

GraphQL schema for complex filtering¶

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:

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¶

Response for 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:

GraphQL schema for sorting¶

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¶

Response for 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:

GraphQL schema for group by and aggregation functions¶

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)¶

Response to a 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"
                    }
                }
            }
        ]
    }
}

USER MANUALS