Filtering Data using Query Expression

Airscript includes a Query Expression syntax that allows you to operate on lists of items. This syntax looks somewhat similar to SQL but is based on a concept called Language Integrated Query.

Query Expression Basics

Using this syntax you can filter or map a list of items.

The general syntax looks like the following:

FROM
  item
IN
  [{score: 100}, {score: 20}, {score: 40}, {score: 85}]
WHERE
  item.score > 80
SELECT
  item

In this simple example we go through a collection and filter out all items that have a score property greater than 80.

Any valid Airscript function is valid within our syntax. For example, below we use the Airscript STRING_FIND in the WHERE clause.

FROM
  p
IN
  persons
WHERE
  STRING_FIND(p.name, "Bob") >= 0
SELECT
  p.name

This example is going through all persons and finding all persons who have a name that contains Bob in it. Then we are returning the name of those persons. This will return as a list of p.name

While this is the basic example of our syntax here, we have even more functionality baked into our syntax. The supported technical arguments are:

FROM
    itemBinding = identifier,
  [ indexBinding = identifier, [ collectionBinding = identifier ] ]
IN
    expression
[WHERE expression]
[ORDER BY expression direction = (ASCENDING | DESCENDING)]
[LIMIT expression [OFFSET expression]]
SELECT [DISTINCT] selectExpression

Anything in brackets ([]) means that it is optional. We do not need to have a WHERE clause. There are also ORDER BY clause and LIMIT options. If you want your result to return only unique items you can put the DISTINCT modifier on your select and you receive a SET with duplicate items removed.

Object Key Binding

When Filtering through an object rather than a list, you can access both parts of each key value pair within the object you iterate through. For example, consider the the first part of the following query.

FROM
  item, key
IN
  {
    "foo": "bar", 
    "foo": "baz"
  }
SELECT
  key

In this query, key is a special reserved word that allows you to access keys in the JSON object you are iterating through. In the example above, the query would return a list of the keys in that object.

You can use this in various ways, including filtering by the keys themselves. Take for example the following object example.

var object = {
  "first_name": "Luke",
  "last_name": "Skywalker",
  "email": "[email protected]",
  "status": "unemployed"
}

Let's say I wanted to filter this object, and get an object that has everything but his "status." I would use the following query to filter by that key.

FROM
  item, key
IN
  object
WHERE
  key!="status"

This would return

{
  "first_name": "Luke",
  "last_name": "Skywalker",
  "email": "[email protected]"
}

You can use this reserved keyword to manipulate objects just as you would with other Airscript functions. For example, you can use the Order By function to sort objects by their keys. Take the following example.

FROM 
    item, key 
IN 
    { 
        "cat": "dog", 
        "apple": "bear" 
    } 
ORDER BY 
    key

This query will return

{
  "apple": "bear",
  "cat": "dog"
}

Let's say I wanted to replace the key names within an object with something else. Using the same key reserved namespace, we could use Airscript to replace a key name with something else. For example

FROM 
    item, key 
IN 
    {
    "cat": "dog",
    "apple": "bear"
    } 
SELECT 
    "{{SUBSTITUTE(key, "apple", "aardvark")}}": item

Index Binding

One other important note: there is an index binding, meaning that you can also get the index of the item you are at in your iteration. For example:

FROM
  item, index
IN
  [{"foo": "bar"}, {"foo": "baz"}]
SELECT
  {"{{index}}": item}

In this case, index is a special reserved word that you put at the end of your expression and you can then refer to it in the rest of your statement. The result will be: 

[
  {
    "0": {
      "foo": "bar"
    }
  },
  {
    "1": {
      "foo": "baz"
    }
  }
]

Nested Query Expressions

The following example shows one way to nest a Query Expression within a Query Expression.

var collectionOfPeople = [
  {
    "person": "ismaen",
    "qualified": true,
    "score": 81
  },
  {
    "person": "brooks",
    "qualified": true,
    "score": 91
  },
  {
    "person": "chandra",
    "qualified": false,
    "score": 50
  },
  {
    "person": "dai",
    "qualified": true,
    "score": 50
  }
]

FROM
  qualifiedPerson
IN
  FROM
    person
  IN
    collectionOfPeople
  WHERE
    person.qualified = TRUE
  SELECT
    person
WHERE
  qualifiedPerson.score > 80
SELECT
  qualifiedPerson

[
  {
    "person": "ismaen",
    "qualified": true,
    "score": 81
  },
  {
    "person": "brooks",
    "qualified": true,
    "score": 91
  }
]

In this example we are pulling only qualified people out of a collection of people and then looking for people in that collection that have a score greater than 80.