# Query Expressions

Airscript's **Query Expressions** are similar to [LINQ](https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/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](path-expressions) within them to refer to values nested within the data they are querying.

## Syntax

### Declaration

```javascript
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`:

```javascript
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:

```javascript
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:

```javascript
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](../../../airscript-expressions/text-functions/uppercase) function to generate a List of book titles in entirely capital letters:

```javascript
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:

```javascript
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:

```javascript
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](../../../airscript-expressions/comparison-operators/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:

```javascript
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”`:

```javascript
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](../../../airscript-expressions/conditional-functions/equal) and [Comparison Operators](../../../airscript-expressions/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:

```javascript
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:

```javascript
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-expressions/list-functions/flat) Airscript function to merge nested Lists:

```javascript
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:

```javascript
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:

```javascript
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:

```javascript
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:

```javascript
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:
