Coreo GraphQL API – Filtering Queries

Most Coreo GraphQL objects are filterable with a where argument. This where argument is a JSON object that specifies the conditions that should be met for the object to be returned.

For example, we can filter the above query to only include data from a single, specific user:

  // Get project with id=349 and 
  // then all records by user with userId=23203
  { 
    project(id: 349) {
        id
        name
        records(
            where: {
                userId: 23203
            }
        ) {
            id
            data
            userId
        }
    }
  }

This will ensure that the records returned will only be from the user with the id ‘23203’.

This type of filtering should work on every available non Graphql object field (such as userId, id or name). It is not possible to filter by a child object (GraphQL object, such as record), for example, filtering a project based on the contents of the records it contains. However, it is possible to filter by the content of primitive objects, such as the data, or geometry fields on a record.

Advanced filtering

The where argument also accepts some more complicated structures which enable advanced filtering on objects.

First of all, a where is not restricted to filtering on one field:

where: {
    id: 8306791,
    userId: 23203
}

The above query will ensure both conditions are met by the records returned.

For example, if we wanted to filter records that had an id greater than ‘8306791’, we could structure the where as:

where: {
    id: {
        gt: 8306791
    }
}

The result of this query will return all records that have an id greater than 8306791.

If we want to apply multiple filters on the same field we can do so with the and operator, which takes any number of conditions and ensures they are all met:

where: {
    and: [
        id: {
            not: 8306791
        }, 
        id: {
            gt: 8306780
        }
    ]
}

not is another valid operator that where accepts. The full list of valid operators are listed here:

and: [{ someField: 5 }, { anotherField2: 6 }],     // (someField = 5) AND (anotherField = 6)
or: [{ someField: 5 }, { anotherField2: 6 },     // (someField = 5) OR (anotherField = 6)
someField: {
    // Basic Operators
    .eq: 3,                              // = 3
    ne: 20,                             // != 20
    is: null,                           // IS NULL
    not: true,                          // IS NOT TRUE
    or: [5, 6],                         // (someAttribute = 5) OR (someAttribute = 6)

    // Numeric Operators
    gt: 6,                              // > 6
    gte: 6,                             // >= 6
    lt: 10,                             // < 10
    lte: 10,                            // <= 10
    between: [6, 10],                   // BETWEEN 6 AND 10
    notBetween: [11, 15],               // NOT BETWEEN 11 AND 15

    // Other Operators

    in: [1, 2],                         // IN [1, 2]
    notIn: [1, 2],                      // NOT IN [1, 2]

    like: '%hat',                       // LIKE '%hat'
    notLike: '%hat',                    // NOT LIKE '%hat'
    startsWith: 'hat',                  // LIKE 'hat%'
    endsWith: 'hat',                    // LIKE '%hat'
    substring: 'hat',                   // LIKE '%hat%'
    iLike: '%hat',                      // ILIKE '%hat'
    notILike: '%hat',                   // NOT ILIKE '%hat'
    regexp: '^[h|a|t]',                 // REGEXP/~ '^[h|a|t]'
    notRegexp: '^[h|a|t]',              // NOT REGEXP/!~ '^[h|a|t]' 
    iRegexp: '^[h|a|t]',                // ~* '^[h|a|t]' 
    notIRegexp: '^[h|a|t]',             // !~* '^[h|a|t]' 

    any: [2, 3],                        // ANY ARRAY[2, 3]::INTEGER

    //  like/iLike/notLike can be combined to Op.any:
    like: { any: ['cat', 'hat'] }  // LIKE ANY ARRAY['cat', 'hat']
}

Filtering by Data

The data submitted by the user when creating record is stored in the data object of the record. To filter by this, submitted a data object within the where containing the query.

  // Find all records where species is cat
  {
    records(where:{ projectId: 9999, data: { species: 'cat' } } ){
      id
      data
    }
  }

  // Find all records after a certain timestamp
  // As we store timestamps in ISO format we 
  // can use string comparison with gt (greater-than).
  {
    records(where:{ projectId: 9999, data: { timestamp: { gt: '2017-07-28T06:40:57.437Z' } } } ){
      id
      data
    }
  }