Skip to content

Instantly share code, notes, and snippets.

@e0ipso

e0ipso/JSON API Fancy Filters.md

Last active April 7, 2024 08:19
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save e0ipso/efcc4e96ca2aed58e32948e4f70c2460 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save e0ipso/efcc4e96ca2aed58e32948e4f70c2460 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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 or not.
  • Filter groups.: These allow the creation of complex, nested queries. For example, queries with AND operators within a higher level OR condition.

Filter conditions

Conditional objects represent a conditional statement for the backend to execute in order to retrieve the eligible records. In many data stores these are called where clauses.

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

  • path. The path property MUST be a complex property identifier that identifies the property and the entity type that hosts it, upon which the condition is tested. A path identifies the record series of attributes and or relationships to follow in order to fetch the data against with the filter condition should be evaluated.
  • 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, NOT IN, BETWEEN, IS NULL and IS NOT NULL.
  • memberOf. If this filter condition should be grouped within a parent filter group, this is that filter group's arbitrary ID key. If the contents of the memberOf property does not match any group definition ID, then it MAY be ignored. If this property is not provided, then the current condition will be assigned to the implicit root group.

In coionals 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.

Complex property ID

A complex property identifier is used to identify a property or sub-property in the requested entity type, or any entity type accessible via relationships from the requested entity type. Complex property identifiers MUST contain a dot separated list of path elements. Each element MUST be either a relationship field, an attribute field, or the name of a property in an attribute. The end of the complex property ID MUST be an attribute followed by an optional group attribute's property names.

Example 1

In the simplest form, just an attribute on an entity, the complex property ID is just the name of the attribute. For example, the title of a blog entity would simply be: title

Example 2

You want 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 complex property ID 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 complex property ID will 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 complex property identifier will be: relAtoB.relBtoC.attr.prop.

Filter groups

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

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

  • conjunction. Support for AND and OR MUST be provided. 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. This group uses the AND conjuction.

The keys for a filter conditional MUST be wrapped in a group property to specify that the arbitrary ID is for a 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
@pcambra
Copy link

pcambra commented Apr 11, 2017 •
edited
Loading

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.

@starbeast
Copy link

starbeast commented Jul 5, 2017 •
edited
Loading

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

@DaveKin
Copy link

DaveKin commented Feb 19, 2018

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] }
        }
      ]
    }
  ]
}

@cosminstn
Copy link

cosminstn commented Dec 17, 2021 •
edited
Loading

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment