Query Expressions

Airscript's Query Expressions are similar to LINQ. They are used to select and modify values from Lists, Objects, or, most commonly, Lists of Objects.

Query Expressions are designed to precisely sort, filter, or otherwise restructure long, complex data. They will often contain Path Expressions within them to refer to values nested within the data they are querying.

Syntax

Declaration

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

Declarations in square brackets are not required.

Expressions

FROM expression (required, expects type: string)

The designation of the items in the data.

This designation will be used throughout the rest of the Query Expression to refer to each item; nested Path Expressions that refer to values stored in Listed Objects will begin with this designation.

IN expression (required, expects type: List or Object )

The data to Query.

WHERE expression (optional, expects type: Boolean)

The condition under which items will be returned. Items will be returned only when the result in this conditional evaluating to TRUE.

ORDER BY expression (optional, expects type: any)

Designates what will be used to sort the returned datat. Ordering defaults to least to greatest, but appending DESCENDING after the sorting_property will reverse this.

LIMIT expression (optional, expects type: Number)

The number of instances to include in the resulting data.

**SELECT expression ** (required, expects type: any)

Defines what will be returned by the Query Expression and how it will be structured. Most commonly, this defines each item in the returned List, although it can also be used to define properties in an Object.

Examples

To demonstrate how this syntax is applied, assume all of the following examples have access to the List of Objects, list_of_books:

list_of_books = [
  {
    "title": "Angels & Demons",
    "author": "Dan Brown",
    "isbn": "9781416524793",
    "genres": [
      "Fiction"
    ]
  },
  {
    "title": "The Da Vinci Code",
    "author": "Dan Brown",
    "isbn": "9780307879257",
    "genres": [
      "Fiction"
    ]
  },
  {
    "title": "Hackers: Heroes of the Computer Revolution",
    "author": "Steven Levy",
    "isbn": "9780141000510",
    "genres": [
      "History",
      "Nonfiction"
    ]
  },
  {
    "title": "Moonwalking with Einstein: The Art and Science of Remembering Everything",
    "author": "Joshua Foer",
    "isbn": "9781594202292",
    "genres": [
      "Nonfiction"
    ]
  }
]

Simple Query Expressions

The simplest Query Expressions contain only the FROM, IN, and SELECT clauses, as these are the only clauses required to define a Query Expression.

The most basic Query Expression does nothing but return the original collection:

FROM book IN list_of_books
SELECT book -> [
  {
    "title": "Angels & Demons",
    "author": "Dan Brown",
    "isbn": "9781416524793",
    "genres": [
      "Fiction"
    ]
  },
  {
    "title": "The Da Vinci Code",
    "author": "Dan Brown",
    "isbn": "9780307879257",
    "genres": [
      "Fiction"
    ]
  },
  {
    "title": "Hackers: Heroes of the Computer Revolution",
    "author": "Steven Levy",
    "isbn": "9780141000510",
    "genres": [
      "History",
      "Nonfiction"
    ]
  },
  {
    "title": "Moonwalking with Einstein: The Art and Science of Remembering Everything",
    "author": "Joshua Foer",
    "isbn": "9781594202292",
    "genres": [
      "Nonfiction"
    ]
  }
]

To transform each instance in the returned List, you can modify the Airscript expression given to the SELECT clause. For instance, to a return a List of only the ISBN numbers given for each book, you would use the Path Expression in the following example:

FROM book IN list_of_books
SELECT book.isbn
=> [
  "9781416524793",
  "9781416524793",
  "9781594202292",
  "9780141000510"
]

Airscript functions an also be used to define the Airscript expression in the SELECT clause. For instance, the following example uses the UPPERCASE function to generate a List of book titles in entirely capital letters:

FROM
  book
IN
  list_of_books
SELECT
  UPPERCASE(book.title) -> [
  "ANGELS & DEMONS",
  "THE DA VINCI CODE",
  "HACKERS: HEROES OF THE COMPUTER REVOLUTION",
  "MOONWALKING WITH EINSTEIN: THE ART AND SCIENCE OF REMEMBERING EVERYTHING"
]

Sorting

The ORBER BY clause is used to sort a List of Objects by the value of one of the Object properties. For instance, the following example sorts list_of_books by the value of each Object's author property:

FROM
  book
IN
  list_of_books
ORDER BY
  book.author
SELECT
  book -> [
  {
    "title": "Angels & Demons",
    "author": "Dan Brown",
    "isbn": "9781416524793",
    "genres": [
      "Fiction"
    ]
  },
  {
    "title": "The Da Vinci Code",
    "author": "Dan Brown",
    "isbn": "9780307879257",
    "genres": [
      "Fiction"
    ]
  },
  {
    "title": "Moonwalking with Einstein: The Art and Science of Remembering Everything",
    "author": "Joshua Foer",
    "isbn": "9781594202292",
    "genres": [
      "Nonfiction"
    ]
  },
  {
    "title": "Hackers: Heroes of the Computer Revolution",
    "author": "Steven Levy",
    "isbn": "9780141000510",
    "genres": [
      "History",
      "Nonfiction"
    ]
  }
]

Note that the items in the returned List are in alphabetical order in respect to their author properties. To reverse that order, append DESCENDING after book.author, such as in the following example:

FROM
  book
IN
  list_of_books
ORDER BY
  book.author
DESCENDING
SELECT
  book -> [
  {
    "title": "Hackers: Heroes of the Computer Revolution",
    "author": "Steven Levy",
    "isbn": "9780141000510",
    "genres": [
      "History",
      "Nonfiction"
    ]
  },
  {
    "title": "Moonwalking with Einstein: The Art and Science of Remembering Everything",
    "author": "Joshua Foer",
    "isbn": "9781594202292",
    "genres": [
      "Nonfiction"
    ]
  },
  {
    "title": "Angels & Demons",
    "author": "Dan Brown",
    "isbn": "9781416524793",
    "genres": [
      "Fiction"
    ]
  },
  {
    "title": "The Da Vinci Code",
    "author": "Dan Brown",
    "isbn": "9780307879257",
    "genres": [
      "Fiction"
    ]
  }
]

For more on how Airscript handles the ordering of different data types, see Ordering. This document is focused primarily on Airscript's out-of-the-box comparison operators, but the ordering conventions discussed are also used by the ORDER BY clause while sorting.

Limiting Returned Instances

The LIMIT clause can be used to limit the number of items in the returned List by defining the maximum number of items that the returned List can contain. For instance, the follow example returns a List that contains only two book titles:

FROM
  book
IN
  list_of_books
LIMIT
  2
SELECT
  book.title -> [
  "Angels & Demons",
  "The Da Vinci Code"
]

Filtering

The WHERE clause is used to define which items in a List of Objects will be included in the returned List. An item will be included if the conditional given to the WHERE clause evaluates to TRUE when that item is used to evaluate it.

For instance, to search the list_of_books for all books written by a specific author, we could use the following expression to require that the author property of the book be equal to “Dan Brown”:

FROM book IN list_of_books
WHERE book.author = "Dan Brown"
SELECT book
=> [
  { 
    title: "Angels & Demons"
    author: "Dan Brown"
    isbn: "9781416524793"
    genres: ["Fiction"]
  },
  {
    title: "The Da Vinci Code",
    author: "Dan Brown",
    isbn: "9780307879257",
    genres: ["Fiction"]
  }
]

There are many ways to create conditional expressions. For a more detailed dive into what sort of conditional expressions are available out-of-the-box, see the reference documentation on Conditional Functions and Comparison Operators.

To filter any duplicate items out of the resulting list, append the DISTINCT keyword to the DISTINCT clause. The following example shows how this methodology can be used to generate a List of authors where each author is unique:

FROM
  book
IN
  list_of_books
SELECT DISTINCT
  book.author -> [
  "Dan Brown",
  "Steven Levy",
  "Joshua Foer"
]

Joining

Query Expressions can also be used to relate one data set to another. For instance, say we have variable named list_of_genres that contains a List of genre Objects, like so:

list_of_genres = [
  {
    "name": "Fiction",
    "description": "..."
  },
  {
    "name": "History",
    "description": "..."
  },
  {
    "name": "Nonfiction",
    "description": "..."
  },
  {
    "name": "Mystery",
    "description": "..."
  }
]

If we want to generate a List of genres that only contains the genres referenced in list_of_books, we can filter list_of_genres by each distinct genre in list_of_books. Doing so requires nesting one Query Expression within another and using the FLAT Airscript function to merge nested Lists:

FROM
  genre_name
IN
  FLAT(list_of_books[*].genres)
SELECT DISTINCT
  (
    FROM
      genre
    IN
      list_of_genres
    WHERE
      genre.name = genre_name
    SELECT
      genre
  )[0] -> [
  {
    "name": "Fiction",
    "description": "..."
  },
  {
    "name": "History",
    "description": "..."
  },
  {
    "name": "Nonfiction",
    "description": "..."
  }
]

The same functionality can also be achieved by using a Path Expression rather than nesting another Query Expression:

FROM
  genre_name
IN
  FLAT(list_of_books[*].genres)
SELECT DISTINCT
  (list_of_genres[?(@.name = genre_name)]
  )[0] -> [
  {
    "name": "Fiction",
    "description": "..."
  },
  {
    "name": "History",
    "description": "..."
  },
  {
    "name": "Nonfiction",
    "description": "..."
  }
]

Creating Objects through Query Expressions

Query Expressions can be used to create new Objects by defining key: value pairs in the SELECT clause. Note that in order to use a value as a key, it will need to explicitly placed inside a string and designated an Airscript expression to be parsed before evaluation by placing it between double curly brackets:

FROM
  book
IN
  list_of_books
SELECT
  "{{book.isbn}}": {
    "title": book.title,
    "author": book.author,
    "genres": book.genres
  } -> {
  "9781416524793": {
    "title": "Angels & Demons",
    "author": "Dan Brown",
    "genres": [
      "Fiction"
    ]
  },
  "9780307879257": {
    "title": "The Da Vinci Code",
    "author": "Dan Brown",
    "genres": [
      "Fiction"
    ]
  },
  "9780141000510": {
    "title": "Hackers: Heroes of the Computer Revolution",
    "author": "Steven Levy",
    "genres": [
      "History",
      "Nonfiction"
    ]
  },
  "9781594202292": {
    "title": "Moonwalking with Einstein: The Art and Science of Remembering Everything",
    "author": "Joshua Foer",
    "genres": [
      "Nonfiction"
    ]
  }
}

Querying Objects

Query Expressions can query Objects as well as Lists. In such cases, you can use a special FROM clause to access both parts of each key/value pair within the Object you iterate through. The first string you give the FROM clause will bind to each value, and the second string will bind to each key. This allows you to pull a list of all the keys in an Object, such as in the following example:

FROM
  item,
  key
IN
  { "One": 1, "Two": 2 }
SELECT
  key -> [
  "One",
  "Two"
]

Similarly, you can generate a List of all the values in a Object, such as in the following example:

FROM
  item,
  key
IN
  { "One": 1, "Two": 2 }
SELECT
  item -> [
  1,
  2
]

Querying an Object is otherwise analogous to Querying a List. The WHERE clause, for instance, can be used to pull only values that meet certain conditions, the ORDER BY clause can be used to sort the returned values, and the returned values can also be used to create new Objects.

Want more complex examples?

The examples covered in this document are meant to highlight how Query Expressions can be used in some of the most simple use cases. If you want to learn more about how Query Expressions are being used in the wild, check out our Builder Community: