Hey again! It's only a been a week since we announced FunctionScript, and the repository is nearing 1,000 GitHub Stars already! We're very grateful for the attention FunctionScript has been able to generate, and we're excited to keep rolling.

This week we're announcing another core piece of the Standard Library API development platform — KeyQL. While FunctionScript simplifies API development by uniting the code you write with the HTTP interface, KeyQL allows you to define logical queries of datasets using only JSON key-value pairs. It is the perfect complement to imperative API development and represents a way to add simple yet robust querying capabilities to existing APIs.

You'll begin to understand the power of KeyQL as specification in the coming weeks as we release more of the platform and new products.

KeyQL

KeyQL Logo

KeyQL is a language, specification and package for querying datasets using key-value pairs. It is heavily inspired by the simplicity and ease-of-use of Django and similar ORMs. The provided Node.js package can be used to filter large JSON datasets from within any codebase, but the primary purpose of KeyQL is to be used with FunctionScript APIs, where JSON or HTTP Query Parameter key-value pairs can be used to encode query requests to underlying datasets.

KeyQL is meant for easy querying of JSON datasets, spreadsheet data, information retrieved from APIs such as Airtable and more. It can be used to add robust querying capabilities to existing APIs without a massive architectural lift and shift.

The motivation for KeyQL differs from that of GraphQL. KeyQL is intended to provide a simple querying interface to existing imperative APIs and relatively flat datasets. The operators (comparators) are the most important feature and are meant to be easily interpretable by even the newest developer. KeyQL and GraphQL can, in theory, coexist within a single codebase or API implementation. KeyQL is not intended to be used to define an entire backend architecture and provides no opinions on the graph-based structure of output data (you do not define schemas with it).

Quick Example

A quick example of using KeyQL with a FunctionScript API would look like:

Filename: /dataset.json

[
  {
    "id": 1,
    "fields": {
      "name": "Alice",
      "birthdate": "12/01/1988",
      "pets": 2
    }
  },
  {
    "id": 2,
    "fields": {
      "name": "Bernard",
      "birthdate": "11/11/1972",
      "pets": 5
    }
  },
  {
    "id": 3,
    "fields": {
      "name": "Christine",
      "birthdate": "01/05/1991",
      "pets": 0
    }
  }
]

Filename: /functions/__main__.js

const KeyQL = require('keyql');
const dataset = require('../dataset.json');
// Searching through the "fields" object in each row
const kqlDataset = new KeyQL(dataset, row => row.fields);

/**
* Query a dataset based on an Array of Objects
* @param {object.keyql.query} where A list of fields to query for
* @returns {array} result The result list
*/
module.exports = async (where = {}) => {

  return kqlDataset.query()
    .select([where]) // Wrap in array if provided a raw object
    .values();

};

An HTTP POST request containing:

{
  "where": {
    "pets__gt": 3
  }
}

Would return:

[
  {
    "id": 2,
    "fields": {
      "name": "Bernard",
      "birthdate": "11/11/1972",
      "pets": 5
    }
  }
]

Writing Queries

Writing KeyQL Queries is as simple as preparing a JSON Object. For example, in a dataset that has records that look like...

// Example dataset in JavaScript
[
  {
    first_name: 'Dolores',
    last_name: 'Abernathy',
    is_host: true,
    eye_color: 'blue',
    hair_color: 'blonde',
    location_in_park: null,
    age: 250
  }
]

You could write a query against it that returns...

Query: All entries with first_name = Dolores

[
  {
    "first_name": "Dolores"
  }
]

Query: first_name = Dolores AND eye_color in blue, green

[
  {
    "first_name": "Dolores",
    "eye_color__in": ["blue", "green"]
  }
]

Query: first_name = Dolores OR first_name = Teddy

[
  {
    "first_name": "Dolores"
  },
  {
    "first_name": "Teddy"
  }
]

Supported Operators

All operators in KeyQL queries are preceded by a __ delimiter. To reiterate from the previous section, this means you can query the field first_name with;

"first_name" // (default to "is" operator)
"first_name__is"
"first_name__startswith"
"first_name__gte"

Full List of Supported Operators

The following table assumes that queryValue is the value you're searching for provided a specified key, and entryValue is the matching entry in a dataset.

Operator Behavior
is Finds all matching entries. Returns entryValue === queryValue (exact match, type included).
not Finds all non-matching entries. Returns entryValue !== queryValue (exact match, type included).
gt Finds all entries greater than specified value. Returns entryValue > queryValue.
gte Finds all entries greater than or equal to specified value. Returns entryValue >= queryValue.
lt Finds all entries less than specified value. Returns entryValue < queryValue.
lte Finds all entries less than or equal to specified value. Returns entryValue <= queryValue.
contains Finds all entries containing the exact provided value. Works when entryValue is a string or an array.
icontains Finds all entries containing the provided value, case-insensitive. Works when entryValue is a string or an array.
startswith Finds all entries starting with the exact provided value. Works when entryValue is a string.
istartswith Finds all entries starting with the provided value, case-insensitive. Works when entryValue is a string.
endswith Finds all entries ending with the exact provided value. Works when entryValue is a string.
iendswith Finds all entries ending with the provided value, case-insensitive. Works when entryValue is a string.
is_null Finds all entries where entryValue === null, queryValue is ignored.
is_true Finds all entries where entryValue === true, queryValue is ignored.
is_false Finds all entries where entryValue === false, queryValue is ignored.
not_null Finds all entries where entryValue !== null, queryValue is ignored.
not_true Finds all entries where entryValue !== true, queryValue is ignored.
not_false Finds all entries where entryValue !== false, queryValue is ignored.
in Finds all entries within the provided value, intended to match when queryValue is an array but works with string input.
not_in Finds all entries not in the provided value, intended to match when queryValue is an array but works with string input.
recency_lt Finds all entries where DATE(entryValue) is recent within less than queryValue in number of seconds. i.e. "field__recency__lt": 3600 would look for entries that have field as a date/timestamp within the past hour (exclusive). ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
recency_lte Finds all entries where DATE(entryValue) is recent within less than or equal to queryValue in number of seconds. i.e. "field__recency__lte": 3600 would look for entries that have field as a date/timestamp within the past hour (inclusive). ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
recency_gt Finds all entries where DATE(entryValue) has a recency greater than queryValue in number of seconds. i.e. "field__recency__gt": 3600 would look for entries that have field as a date/timestamp outside the past hour (exclusive). ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
recency_gte Finds all entries where DATE(entryValue) has a recency greater than or equal to queryValue in number of seconds. i.e. "field__recency__gte": 3600 would look for entries that have field as a date/timestamp outside the past hour (inclusive). ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
upcoming_lt Finds all entries where DATE(entryValue) is going to occur within less than queryValue in number of seconds. i.e. "field__upcoming_lt": 3600 would look for entries that have field as a date/timestamp within the next hour (exclusive). ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
upcoming_lte Finds all entries where DATE(entryValue) is going to occur within less than or equal to queryValue in number of seconds. i.e. "field__upcoming_lte": 3600 would look for entries that have field as a date/timestamp within the next hour (inclusive). ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
upcoming_gt Finds all entries where DATE(entryValue) is going to occur within greater than queryValue in number of seconds. i.e. "field__upcoming_gt": 3600 would look for entries that have field as a date/timestamp outside the next hour (exclusive). ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
upcoming_gte Finds all entries where DATE(entryValue) is going to occur within greater than or equal to queryValue in number of seconds. i.e. "field__upcoming_gte": 3600 would look for entries that have field as a date/timestamp outside the next hour (inclusive). ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
date_lt Finds all entries where DATE(entryValue) is less than DATE(queryValue), i.e. '12-06-1988' < '01-01-2019'. ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
date_lte Finds all entries where DATE(entryValue) is less than or equal to DATE(queryValue), i.e. '12-06-1988' <= '12-06-1988'. ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
date_gt Finds all entries where DATE(entryValue) is greater than DATE(queryValue), i.e. '12-06-1988' > '01-01-1980'. ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.
date_gte Finds all entries where DATE(entryValue) is greater than or equal to DATE(queryValue), i.e. '12-06-1988' >= '12-06-1988'. ISO8601 Timestamps suggested, if no timezone entered UTC will be assumed.

Read More

You can read more about KeyQL on the GitHub repository, FunctionScript/KeyQL. We encourage you to contribute to development with thoughts and feedback.

Still More Coming

KeyQL is the second major release after FunctionScript, and is just another step in a complete overhaul of Standard Library's available tooling and the ecosystem we've been building. As usual, stay tuned.

If you're as excited as we are, feel free to join our Slack workspace. You can also follow us on Twitter, @StdLibHQ.

Keith Horwood
Founder and CEO, Standard Library