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.
Updated over 1 year ago