query-challenge.md

Background

We need to choose an API design for the JSON payload that developers will use to query datasets. We have two competing proposals. Both work just as well. Your objective is to solve the challenge below and then pick the API you think is better and explain why (or recommend a better one).

Challenge

Given the following SQL statement...

select * 
from `users` 
where (
  `first_name` = 'Taylor'
  and `last_name` <> 'McGann'
  and (
    `age` < 20
    or (
      `age` >= 30
      and `age` < 40
    )
    or `age` >= 50
  )
)
order by `first_name` asc, `age` desc
limit 5000
offset 0;

...construct two separate but identically functional JSON payloads based on the APIs below (i.e. one payload should use API A and the other should use API B). The JSON payloads you construct are essentially translations of the SQL statement above.

For brevity, the API documentation below is not comprehensive and does not include an API "grammar" that outlines all possible expressions. Instead, each section offers two examples of the respective API syntax. You'll notice that API A makes use of more array literals and API B makes use of more object literals. This is the most significant difference and is what we are most interested in evaluating.

At the end of this document is a list of all the operators you would need to construct your two JSON payloads.

API A

Example 1:

{
  "condition": [ "firstName", "_eq", "Taylor" ]
}

Example 2:

{
  "offset": 2000,
  "limit": 1000,
  "condition": {
    "_and": [
      [ "firstName", "_eq", "Taylor" ],
      [ "age", "_gte", 30 ]
    ]
  },
  "orderBy": [
    [ "lastName", "asc" ]
  ]
}

API B

Example 1:

{
  "condition": {
    "column": "firstName",
    "operator": "_eq",
    "value": "Taylor"
  }
}

Example 2:

{
  "offset": 2000,
  "limit": 1000,
  "condition": {
    "operator": "_and",
    "value": [
      {
        "column": "firstName",
        "operator": "_eq",
        "value": "Taylor"
      },
      {
        "column": "age",
        "operator": "_gte",
        "value": 30
      }
    ]
  },
  "orderBy": [
    { 
      "column": "lastName",
      "order": "asc"
    }
  ]
}

API Operators

• _and
• _or
• _eq
• _neq
• _gt
• _gte
• _lt
• _lte
• _contains
• _ncontains
• _in
• _nin

My stab:

A

{
  "offset": 0,
  "limit": 5000,
  "condition": {
    "_and": [
      ["first_name", "_eq", "Taylor"],
      ["last_name", "_neq", "McGann"],
      ["", "_or", [
        ["age", "_lt", "20"],
        ["", "_and", [
          ["age", "_gte", "30"],
          ["age", "_lt", "40"]
        ]]
        ["age", "_gte", "50"]
      ]]
    ]
  },
  "orderBy": [
    ["first_name", "asc"],
    ["age", "desc"]
  ]
}

B

{
  "offset": 0,
  "limit": 5000,
  "condition": {
    "_and": [
      {
        "column": "first_name",
        "operator": "_eq",
        "value": "Taylor"
      },
      {
        "column": "last_name",
        "operator": "_neq",
        "value": "McGann"
      },
      {
        "operator": "_or",
        "value": [
          {
            "column": "age",
            "operator": "_lt",
            "value": 20
          },
          {
            "operator": "_and",
            "value": [
              {
                "column": "age",
                "operator": "_gte",
                "value": 30
              },
              {
                "column": "age",
                "operator": "_lt",
                "value": 40
              }
            ]
          },
          {
            "column": "age",
            "operator": "_gte",
            "value": 50
          }
        ]
      }
    ]
  },
  "orderBy": [
    {
      "column": "first_name",
      "order": "asc"
    },
    {
      "column": "age",
      "order": "desc"
    }
  ]
}

Review

I like style B better. I like the explicitness of objects, even though they are considerably less terse. I also feel like if we decide to add things to certain operators it's easier to do it under a property name than an index into an array. Something that I think could be useful is a way to specify multiple operators for a single column but I'm not sure how this would work (maybe _or and _and blocks could have an optional column field). Additionally I think that condition should be an array that is implicitly the value of an _and block.

This all sucks. We should be using protobuf for everything. The end.