cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Filter data (where condition) in REST API with GraphQL

oguimard
Retired

When building graphQL to query element, it is sometime interesting to filter the data by different criteria : filtering by name, by Id, by relationships... Filters represent a "where" condition in queries.

 

Filtering can  :

  • filter by fields (MetaAttributes)
  • filter by relationships (MetaAssociation)
  • Combine criteria : and, or
  • adapt the criteria to the type : string, date, numbers...

 

How to make a filter ?

 

 The filter is an argument of the query with the keyword "filter". When using the auto-completion (with GraphiQl or Postman) the system propose the possible filter criteria. Technically the filter represent a "where" in an query (ERQL / SQL)

 

  • Filtering an application by it's name

 

Query Result
query {
  application(filter:{name:"Account Management"})
  {
    id
    name
  }
}


{
  "data": {
    "application": [
      {
        "id": "IubjeRlyFfT1",
        "name": "Account Management"
      }
    ]
  }
}

 

The current filter will limit the application that have the name strictly equals to the element given in the double quotes. See below for all string filters options.

 

  • Filtering an application by it's status

 

Query Result
query {
  application(filter:{statusReview_in:[Validated,UpdateInProgress]})
  {
    id
    name
  }
}






{
  "data": {
    "application": [
      {
        "id": "IubjeRlyFfT1",
        "name": "Account Management"
      },
      {
        "id": "pGCe26yn8zc3",
        "name": "Booking Management"
      }
    ]
  }
}

 

The current filter will limit the application that have their status review MetaAttribute strictly equals to Validated or UpdateInProgress . See below for all enumeration filters options.

 

  • Filtering an application by it's owner
Query Result
query {
  application(filter:{iTOwner_PersonSystem_some:{name:"Eric"}})
  {
    name
    iTOwner_PersonSystem {
      name
    }
  }
}










{
  "data": {
    "application": [
      {
        "name": "MyCompany.com",
        "iTOwner_PersonSystem": [
          {
            "name": "Eric"
          }
        ]
      },
      {
        "name": "Splio EmailForge",
        "iTOwner_PersonSystem": [
          {
            "name": "Eric"
          }
        ]
      },
...

 

The current filter will limit the application that have at least one IT owner strictly named "Eric" . See below for all relationships filters options.

 

How to combine filters ?

 

By default combining filtering criteria will be an "and" operator. You can explicitly select the "and" or "or" operator.

 

  • Default "and" behavior
Query Result
query {
  application(filter:{
modificationDate:"2020-04-07" 
name_contains:"CRM"})
  {
    name
    modificationDate  
  }
}






{
  "data": {
    "application": [
      {
        "name": "CRM US",
        "modificationDate": "2020-04-07"
      },
      {
        "name": "CRM Europe",
        "modificationDate": "2020-04-07"
      }
    ]
  }
}

 

In the filter curly bracket just add the filtering criteria and they will need to be all valid to return a result.

 

  • Explicit "and" or "or"

In the filter you can select as a filter "and" or "or" as shown in the screenshot :

filter_and_or.png

 

Query Result
query {
  application(filter:{or:{modificationDate:"2020-04-07" name_contains:"CRM"}})
  {
    name
    modificationDate
  }
}





{
  "data": {
    "application": [
      {
        "name": "MyCompany.com",
        "modificationDate": "2020-04-07"
      },
      {
        "name": "CRM Management",
        "modificationDate": "2017-09-28"
      },
 ...

 

Because this filter contains an "or" one of the criteria must be valid to return a result. In this example either the date or the name.

 

 

Type of filters by types of fields

 

There are several type of fields :

  • String
  • Date and DateTime
  • Numbers : float, int, double, currency
  • Enumeration
  • Boolean

For each fields the available filter allows various combination of criteria : equals, contains, greater than, in, not in...

 

String filters

 

All string fields will propose the following filter criteria. For instance for Name :

 

name: String # matches all fields with exact value
name_not: String # matches all fields with different value
name_in: [String!] # matches all fields with value in the passed list
name_not_in: [String!] # matches all fields with value not in the passed list
name_lt: String # matches all fields with lesser value
name_lte: String # matches all fields with lesser or equal value
name_gt: String # matches all fields with greater value
name_gte: String # matches all fields with greater or equal value
name_contains: String # matches all fields with a value that contains given substring
name_not_contains: String # matches all fields with a value that does not contain given substring
name_starts_with: String # matches all fields with a value that starts with given substring
name_not_starts_with: String # matches all fields with a value that does not start with given substring
name_ends_with: String # matches all fields with a value that ends with given substring
name_not_ends_with: String # matches all fields with a value that does not end with given substring

 

name.png

 

Date or DateTime filters

 

All date fields will propose the following filter criteria. For instance for Creation Date:

 

creationDate: DateTime # matches all fields with exact value
creationDate_not: DateTime # matches all fields with different value
creationDate_in: [DateTime!] # matches all fields with value in the passed list
creationDate_not_in: [DateTime!] # matches all fields with value not in the passed list
creationDate_lt: DateTime # matches all fields with lesser value
creationDate_lte: DateTime # matches all fields with lesser or equal value
creationDate_gt: DateTime # matches all fields with greater value
creationDate_gte: DateTime # matches all fields with greater or equal value

 

creationdate.png

 

Numbers filters

 

All numbers (int, float, double) fields will propose the following filter criteria. For instance for Cost Per User :

 

costPerUser: Number # matches all fields with exact value
costPerUser_not: Number # matches all fields with different value
costPerUser_in: [Number!] # matches all fields with value in the passed list
costPerUser_not_in: [Number!] # matches all fields with value not in the passed list
costPerUser_lt: Number # matches all fields with lesser value
costPerUser_lte: Number # matches all fields with lesser or equal value
costPerUser_gt: Number # matches all fields with greater value
costPerUser_gte: Number # matches all fields with greater or equal value

 

costperuser.png 

Enumeration filters

 

All enumeration fields (MetaAttribute Value) will propose the following filter criteria. For instance for Cloud Computing :

 

cloudComputing: cloudComputing # matches all fields with exact value
cloudComputing_not: cloudComputing # matches all fields with different value
cloudComputing_in: [cloudComputing] # matches all fields with value in the passed list
cloudComputing_not_in: [cloudComputing] # matches all fields with value not in the passed list

 

cloudcomputing.png

 

Boolean filters

 

All boolean will propose the following filter criteria. For instance for Freeze Past Time Period :

 

FreezePastTimePeriod: Boolean # matches all fields with exact value
FreezePastTimePeriod_not: Boolean # matches all fields with different value

 

boolean filter.png

 

 

 

 

 

 

4 Replies

There is no configuration that can allow that.

 

What you can do is created a new fields "namelong" and then you get access to filter on this field. Follow this tutorial https://community.mega.com/t5/REST-API/Create-custom-schema-SDL-JSON-custom-endpoint/m-p/24218#M16 

 

 

Thank you Olivier,

 

Is it possible to create a custom filter that will search on the long name ?

Hello,

 

Question 1 : Search on Namespace

Search is not possible on the long name. 

 

Question 2 : Managing same attribute several time

You need to use aliases in your queries

example :

query {
  concept(filter:{n}) {
    id
    name
    namePT:name(language:PT)
    nameEN:name(language:EN)
    namespacelong:name(nameSpace:LONG)
    definitionName
    definitionText
    definitionTextPT:definitionText(language:PT)   
  }
}

 

VCI
MEGA
MEGA

Hi @oguimard, I have a question regarding the filters. I made a custom schema to make some requests on the metaclasse concept. I am using Hopex V3P5. Here is my query : 

query
{
concept{

  name(nameSpace:LONG)
  definitionText
  name(language:PT)
  definitionText(language:PT)
}
}

 

I would like to filter this query like this : concept(filter:{name_starts_with:"STUFF"}) but rather than filtering on the name (ie short name) I want my filter to be on the attribute name(nameSpace:LONG) and I cannot find the right syntax to do so, is it even possible ?

 

Another question on the same query, when I execute it i get something like this :

"data": {
"concept": [
{
"name": "Accounting Rule",
"definitionText": ""
},

I dont get the other 2 attribute in portugese, is there a way to get the value of an attribute mutltiple time with the same request ? As I am asking for the value in english and in portuguese I'd like to get something like this : 

 

data": {
"concept": [
{
"name": "Accounting Rule",
"definitionText": ""

"Name(portuguese)":""

"definition(portuguese)":""
},

 

Thank you in advance for your help