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 {
  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:

Filtering on object fields¶

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.

GraphQL schema for the 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:

Filtering on list fields¶

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 involve array_field subfields. The subfield names must be relative to the array_field list and must be prefixed with _item. like in the above example.
Only one any expression per array_field is allowed by _filter argument.
any expressions cannot be children of not 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¶

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