A prescription for a rest endpoint.

REST relational algebra.md

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

vs

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?

@jlank

I liked the ideas here: https://twitter.com/ecentinela/status/248699624859258880

name:like=john

Yeah this is a problem we run into all the time and it doesn't have a standard solution. I like it. Moving this logic into a library makes the server even thinner (in terms of application code).