JSON API Fancy Filters.md

Status

This extension was developed as part of the jsonapi module for Drupal.

Introduction

The JSON API specification is agnostic about how a server implements filtering strategies. In fact, the spec says:

Note: JSON API is agnostic about the strategies supported by a server. The filter query parameter can be used as the basis for any number of filtering strategies.

This specification covers the gap by specifying an strategy that can be adopted by any JSON API server. This specification will cover:

• Filter conditions: These are the assertions made against the data store which qualify a record for inclusion in a collection result.
• Filter groups.: These allow the creation of complex, nested queries. For example, queries with AND operators within a higher level OR condition.

Filter Conditions

Filter conditions are represented by conditional objects. These object codify a conditional statement that a JSON API server will execute in order to retrieve a subset of eligible records. These are called where clauses in many data query languages, like SQL.

A conditional object MUST have an arbitrary ID which uniquely idenfies the conditional object inside of the filter parameter of a JSON API request URI. Additionally, a conditional object MUST contain the following keys:

• path. The path property MUST adhere to the property accessor format. The path identifies the property within the entity type that hosts it. The path selects the data value against which the conditional object will be applied. A path MUST be able to select any attribute property of an entity. A path MAY be able to traverse relationships.
• value. The value used in the comparison made against the data value of the property identified by the path.

A conditional object MAY also contain the following keys:

• operator. The operator used during the comparison. If nothing is specified, then the operator defaults to '='. A JSON API server SHOULD implement, at least, the following operators: =, <, >, <>. In addition to those operators, a server MAY support IN, NOT IN, BETWEEN, IS NULL and IS NOT NULL.
• memberOf. If the conditional object should be grouped within a parent group object, this should be that group object's arbitrary ID. If the contents of the memberOf property does not match any group definition ID, then the conditional object MAY be ignored. If this property is not provided, then the current condition SHOULD be assigned to the implicit root group.

In conditionals with operators that act on multiple values (like IN), the value property MUST hold an array of values.

The keys for a filter conditional MUST be wrapped in a condition property to specify that the arbitrary ID is for a condition.

Property Accessor Format

A property accessor is used to identify a property or sub-property in the requested entity type, or, if the server supports it, any entity type accessible via relationships from the requested entity type. Property accessors MUST contain be a dot separated list of path elements. Each path element MUST be an attribute field or the name of a property in an attribute. If the JSON API server support conditions which traverse relationship, the property accessor MAY contain relationship names as path elements. The last path element of the property accessor MUST be an attribute followed by an optional group attribute's property names.

Example 1

In the simplest form, the property accessor is just the name of the attribute. For example, the propery accessor for the title attribute of a blog entity would simply be: title

Example 2

In a more complex form, you may wish to filter based on attributes accessed across a relationship. For example, you wish to get all post created by authors whose accounts were created within the last week. There exists an entity type, blog, and an entity type user. The blog type has a relationship to its creating user under the author relationship. The user entity type has an attribute of created representing the date that the user account was established. Thus, the property accessor is simply: author.created

Example 3

You want to get all TV shows, that contain a published video in a given streaming platform ("netflix"). Assume that there is an object attribute inside of the /videos resource that contains a list of streaming platforms as keys with a boolean as values.

In this case, the property accessor would be: seasons.videos.published.netflix. Where seasons and videos are relationships, published is an attribute in the videos entity type and netflix is a property inside of the published attribute.

Example 4

If property prop inside of the attribute attr, belonging to the entity type C wants to be used to include/exclude records in the resource A. Then there must be a relationship, or a chain of relationships, between entity type A and C. In this example, assume that there is a relationship relAtoB that goes from A to B, and relationship relBtoC that goes from B to C. In this scenario the property accessor will be: relAtoB.relBtoC.attr.prop.

Filter Groups

A group objecst are used to evaluate multiple conditions or other groups in tandem. They provide the ability to specify a conjunction between multiple conditional objects and/or group objects.

A group object MUST have an arbitrary ID that uniquely idenfies the group object inside of the filter parameter in the JSON API request URI. Additionally a group object MUST contain the following keys:

• conjunction. A JSON API server MUST support AND and OR. A server MAY support any of: NAND, NOR, XOR, XNOR and other binary logical operators.
• memberOf. The result of a group might need to be evaluated inside of a group with other groups and/or conditions. To specify the group this current group belongs to, the memberOf key MUST be used.

If no group is specified, a JSON API server MUST assume that all the conditional objects belong to a single implicit root group.

The keys for a filter conditional MUST be wrapped in a group property to specify that the arbitrary ID is for a group.

Implicit Root Group

Filter and group objects may omit the memberOf keys. In this case, a server MUST assume that these objects belong to an implicit root group. The JSON API MUST use the AND conjuntion for this root group.

Examples

Example 1

You want to get all TV shows, that contain a published video in "netflix" or in "hulu". The equivalent filter object shuold be:

• orGroup. A group with the or conjunction for the two conditions.
• hasNetflix. A condition for the netflix published flag.
• hasHulu. A condition for the hulu published flag.
• tags. A condition for the tags on the TV show seasons.

That translates to:

filter:
  orGroup:
    group:
      conjunction: OR
  hasNetflix:
    condition:
      path: seasons.videos.published.netflix
      value: true
      memberOf: orGroup
  hasHulu:
    condition:
      path: seasons.videos.published.hulu
      value: true
      memberOf: orGroup
  tags:
    condition:
      path: seasons.tags
      value:
        - awesome
        - great
      operator: IN

Serializing the filter object according to RFC 3986, results in a request like:

GET /api/shows?filter[orGroup][group][conjunction]=OR&filter[hasNetflix][condition][path]=seasons.videos.published.netflix&filter[hasNetflix][condition][value]=1&filter[hasNetflix][condition][memberOf]=orGroup&filter[hasHulu][condition][path]=seasons.videos.published.hulu&filter[hasHulu][condition][value]=1&filter[hasHulu][condition][memberOf]=orGroup&filter[tags][condition][path]=seasons.tags&filter[tags][condition][value][]=awesome&filter[tags][condition][value][]=great&filter[tags][condition][operator]=IN HTTP/1.1
Host: example.com
Content-Type: application/vnd.api+json; ext=fancyfilters
Accept: application/vnd.api+json; ext=fancyfilters

Note that all the operators supported by QueryInterface::condition are supported too:

   * @param $operator
   *   Possible values:
   *   - '=', '<>', '>', '>=', '<', '<=', 'STARTS_WITH', 'CONTAINS',
   *     'ENDS_WITH': These operators expect $value to be a literal of the
   *     same type as the column.
   *   - 'IN', 'NOT IN': These operators expect $value to be an array of
   *     literals of the same type as the column.
   *   - 'BETWEEN': This operator expects $value to be an array of two literals
   *     of the same type as the column.

why not using a nested structure like this:

{
    operation: 'and',
    expressions: [
        {
            field: 'a', cond: { '>': 10 },
            nested: {
                operation: 'and',
                expressions: [
                    {
                        field: 'b', cond: { '>': 20 }
                    }
                ]
            }
        },
        {
            field: 'c', cond: { '<': 30 },
            nested: {
                operation: 'or',
                expressions: [
                    {
                        field: 'd', cond: { 'in': [1,2,3] }
                    }
                ]
            }
        }
    ]
}

so it will get parsed into (a > 10 AND b > 20) AND (c < 30 OR d in (1,2,3))
It allows to create as complex queries as necessary and is pretty simple to get built/parsed on both client and server

Appropriate query string representation is: [and][a][gt]=10&[and][a][and][b][gt]=20&[and][c][lt]=30&[and][c][or][d][in][]=1&[and][c][or][d][in][]=2&[and][c][or][d][in][]=1

This has promise, but I think the syntax could be simplified (and made more readable), how about this?

{
  operator: 'and',
  expressions: [
    {
      operator: 'and',
      expressions: [
        {
          field: 'a',
          condition: { '>': 10 }
        },
        {
          field: 'b', 
          condition: { '>': 20 }
        }
      ]
    },
    {
      operator: 'or',
      expressions: [
        {
          field: 'c', 
          condition: { '<': 30 }
        },
        {
          field: 'd', 
          condition: { 'in': [1,2,3] }
        }
      ]
    }
  ]
}

Or, event better:

{
    $and: [{
            $and: [{
                    'a': {
                        '>': 10
                    }
                },
                {
                    'b': {
                        '>': 20
                    }
                }
            ]
        },
        {
            $or: [{
                    c: {
                        '<': 30
                    }
                },
                {
                    d: {
                        'in': [1, 2, 3]
                    }
                }
            ]
        }
    ]
}

If the field name itself starts with $ it could easily be escaped by using $$.
This is heavily inspired by how you would implement complex match conditions in an aggregation pipeline in MongoDB.