e0ipso/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 comparisons made against the data store that qualify a record in a collection as elegible or not.
Filter groups. If you have to use multiple conditions, groups are used to determine how the different conditions interact with each other.

Filter conditions

Conditional objects represent a conditional statement for the backend to execute in order to retrieve the elegible 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.
value. The value used in the comparison made against the contents of the property specified in 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. This is the group's arbitrary ID key this condition belongs to. 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 contidionals 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

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, let's 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.

Example 2

You want to get all TV shows, that contain a plublised video in a given streaming platform ("netflix"). Let's 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.

Filter groups

A group is used to evaluate multiple contidions or other groups together. They are used to provide a conjunction to apply to 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 plublised 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 · 2017-04-11T05:29:56Z

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 · 2017-07-05T10:56:39Z

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 · 2018-02-19T09:37:17Z

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 · 2021-12-17T11:03:00Z

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.

e0ipso/JSON API Fancy Filters.md

Select an option

No results found

Select an option

No results found

Status

Introduction

Filter conditions

Complex property ID

Example 1

Example 2

Filter groups

Examples

Example 1

pcambra commented Apr 11, 2017 •

edited

Loading

Uh oh!

starbeast commented Jul 5, 2017 •

edited

Loading

Uh oh!

DaveKin commented Feb 19, 2018

Uh oh!

cosminstn commented Dec 17, 2021 •

edited

Loading

Uh oh!

e0ipso/JSON API Fancy Filters.md

Status

Introduction

Filter conditions

Complex property ID

Example 1

Example 2

Filter groups

Examples

Example 1

pcambra commented Apr 11, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

starbeast commented Jul 5, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

DaveKin commented Feb 19, 2018

Uh oh!

cosminstn commented Dec 17, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

pcambra commented Apr 11, 2017 •

edited

Loading

starbeast commented Jul 5, 2017 •

edited

Loading

cosminstn commented Dec 17, 2021 •

edited

Loading