A prescription for a rest endpoint.

Purpose

Client-side models are becoming more common. Most work with JSON-REST interfaces by default. However, there is no standards around manipulating the set of data returned by a rest service. This attempts to come to some standard.

Goal

Make it easy to understand and have close parallels with common SQL paradigms and relational algebra.
Make work with standard server-side Query string libraries.

Existing Problems

Filter operators for anything other than Equal (=) such as NotEqual (<>)

This: ownerId<>Foo would be so choice. But not supported by most query-string operators.

Example Data

// Task
{
  id:  1,
  name: "do dishes",
  ownerId: 5,
  cost: 10,
  parentTaskId: 7
}

// User
{
  id: 5,
  name: "Justin",
  efficiency: 6
}

Params

sort =(PROP_NAME ASC|DESC)* sort=owner.name ASC,cost ASC

Sorts the set by the property name in the direction described.

group =(PROP_NAME)* group=ownerId

Adds a grouping by a specified property name

join =(TYPE_NAME)* join=owner

Includes data for TYPE_NAME relationship within every data object.

include =(TYPE_NAME)* include=owner

Adds the unique related TYPE_NAME data to the data to be returned.

add add=count

Adds some additional information to the returned data. In this case, adds the total size of the result set without limit/offset.

sum sum=cost

Sums a specified column. This should be used with group.

limit limit=10

offset offset=20

FILTER_PROP_NAME owner.name=Justin

Examples

Get all tasks

GET /tasks

{
  data: [
    {id: 1, name: "do dishes", ownerId: 5}, ...
  ]
}

Get tasks for Owner 5

GET /tasks?ownerId=5

Get tasks for Owner Justin

GET /tasks?owner.name=Justin

Get tasks with owner

GET /tasks?join[]=owner

{
  data: [{id: 1, name: "dishes", ownerId: 5, owner: {id: 5, name: "Justin"}}, ...]
}

Get tasks with owners

GET /tasks?include[]=owner

{
  data: [{id: 1, name: "dishes", ownerId: 5}, ....],
  owners: [{id: 5, name: "Justin"}, ...]
}

Get cost of tasks by user

GET /tasks?
   group=ownerId
   sum=cost

[
  {cost: 20, ownerId: 5},
  {cost: 12, ownerId: 2},
  ...
]

Get 10 most expensive tasks for an owner

GET /tasks
   ownerId=5
   limit=10
   sort=cost DESC
   add=count

{
  data: [{id: 1, name: "dishes", ownerId: 5}, ... }]
  count: 300
}

Get 11-20th most expensive tasks for an owner

GET /tasks
   ownerId=5
   limit=10
   offset=10
   sort=cost DESC

Get task id=7 and its children

GET /tasks
    parentTaskId=7
    include=parentTask

[{
  data: [tasks],
  parentTask: [parentTasks] 
}]

To do implement this specialQs the way you propose you'd have to add a new query string delimiter to the URL's syntax (upside-down ¿ vs regular ? anyone...) to signify you'd be implementing these SQL-esque queries vs the traditional key=value query string. Even if that was the case my prior statement on twitter stands, you'd have to roll a good parsing engine to handle it (obviously), the only difference is how it looks:

GET /tasks¿
   ownerId=5&
   limit=10&
   sort=cost DESC&
   add=count

GET /tasks?specialQs=
   "ownerId=5
   limit=10
   sort=cost DESC
   add=count"

and you'd have to make specialQs (or maybe a sexier name) a reserved word, reserved in the circles that want to use this functionality at least.

That being said, you could also opt to GET a resource and pass a stringified JSON object containing your query, kind of like how CouchDB does with view collation, but that isn't exactly the model of "SQL" queries :), quite the opposite, however you could borrow the mechanism.

GET /tasks?specialQs={ 
    ownerId: 5,
    limit: 10,
    sort: "cost DESC",
    add: count }

Isn't the prettiest thing, but could work. I really don't see any other way to do this, maybe there is some obscure reserved character in the w3c spec nobody uses that you could use.

In the end a URL/URI/URN are supposed to locate resources, I don't know if it would be in scope to define a query string construct of sorts to manipulate X resource, it would require some agreement on the form of the underlying object/resource, wouldn't it?

justinbmeyer/REST relational algebra.md