Solved: Filter data (where condition) in REST API with Gra...

oguimard · ‎21-10-2020

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 :

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

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

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

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

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

VCI · ‎27-06-2022

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

oguimard · ‎29-06-2022

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 · ‎30-06-2022

Thank you Olivier,

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

oguimard · ‎30-06-2022

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

Filter data (where condition) in REST API with GraphQL

Filter data (where condition) in REST API with GraphQL

How to make a filter ?

How to combine filters ?

Type of filters by types of fields

String filters

Date or DateTime filters

Numbers filters

Enumeration filters

Boolean filters

Question 1 : Search on Namespace

Question 2 : Managing same attribute several time