SELECT Command

SELECT List

The SELECT list names the attributes, functions, and expressions that you want the query to return. The list represents the output of the query.

For tabular data an attribute refers to a column in a table, such as CSV, or a name/value pair, such as JSON.

SELECT *
SELECT *, *, *
SELECT projection1 AS column_alias_1, projection2 AS column_alias_2

The first form of SELECT with the * (asterisk) returns every row that passed the WHERE clause, as-is. The second form of SELECT creates a row with user-defined output scalar expressions projection1 and projection2 for each column.

Unlike S3 Select, both forms can be mixed with each other and the * may be used multiple times.

FROM Clause

Like S3 Select, AMSelect supports the following forms of the FROM clause:

FROM relation
FROM relation relation_alias
FROM relation AS relation_alias

In each form of the FROM clause, relation is the Object or an expression based on it that’s being queried. Users coming from traditional relational databases can think of this as a database schema that contains multiple views over a table.

Following standard SQL, the FROM clause creates rows that are filtered in the WHERE clause and projected in the SELECT list.

For JSON objects, you can also use the following forms of the FROM clause:

FROM S3Object[*].path
FROM S3Object[*].path alias
FROM S3Object[*].path AS alias

Using this form of the FROM clause, you can select from arrays or objects within a JSON object. You can specify path by using one of the following forms:

By name (in an object): .name or ['name']
By index (in an array): [index]
By wildcard character (in an object): .*
By wildcard character (in an array): [*]

Note

This form of the FROM clause works only with JSON objects.
Wildcard characters always emit at least one record. If no record matches, then Amazon S3 Select emits the value MISSING. During output serialization, after the query finishes running, Amazon S3 Select replaces MISSING values with empty records.
Aggregate functions, including AVG, COUNT, MAX, MIN, and SUM, skip MISSING values.
If you don’t provide an alias when using a wildcard character, you can refer to the row by using the last element in the path. For example, you could select all prices from a list of books by using the query SELECT price FROM S3Object[*].books[*].price. If the path ends in a wildcard character instead of a name, then you can use the value _1 to refer to the row. For example, instead of SELECT price FROM S3Object[*].books[*].price, you could use the query SELECT _1.price FROM S3Object[*].books[*].
AMSelect queries always treat a JSON document as an array of root-level values. Thus, even if the JSON object that you are querying has only one root element, the FROM clause must begin with S3Object[*]. However, for compatibility reasons, Amazon S3 Select allows you to omit the wildcard character if you don’t include a path. Thus, the complete clause FROM S3Object is equivalent to FROM S3Object[*] AS S3Object. If you include a path, you must also use the wildcard character. So, FROM S3Object and FROM S3Object[*].path are both valid clauses, but FROM S3Object.path is not.

Examples:

Example #1

This example shows results when using the following dataset and query:

{ "Rules": [ {"id": "1"}, {"expr": "y > x"}, {"id": "2", "expr": "z = DEBUG"} ] }
{ "created": "June 27", "modified": "July 6" }

SELECT id FROM S3Object[*].Rules[*].id

{"id":"1"}
{}
{"id":"2"}
{}

Amazon S3 Select produces each result for the following reasons:

{"id":"1"}: S3Object[0].Rules[0].id produced a match.
{}: S3Object[0].Rules[1].id did not match a record, so Amazon S3 Select emitted MISSING, which was then changed to an empty record during output serialization and returned.
{"id":"2"}: S3Object[0].Rules[2].id produced a match.
{}: S3Object[1] did not match on Rules, so Amazon S3 Select emitted MISSING, which was then changed to an empty record during output serialization and returned.

If you don’t want Amazon S3 Select to return empty records when it doesn’t find a match, you can test for the value MISSING. The following query returns the same results as the previous query, but with the empty values omitted:

SELECT id FROM S3Object[*].Rules[*].id WHERE id IS NOT MISSING

{"id":"1"}
{"id":"2"}

Example #2

This example shows results when using the following dataset and queries:

{ "created": "936864000", "dir_name": "important_docs", "files": [ { "name": "." }, { "name": ".." }, { "name": ".aws" }, { "name": "downloads" } ], "owner": "Amazon S3" }
{ "created": "936864000", "dir_name": "other_docs", "files": [ { "name": "." }, { "name": ".." }, { "name": "my stuff" }, { "name": "backup" } ], "owner": "User" }

SELECT d.dir_name, d.files FROM S3Object[*] d

{"dir_name":"important_docs","files":[{"name":"."},{"name":".."},{"name":".aws"},{"name":"downloads"}]}
{"dir_name":"other_docs","files":[{"name":"."},{"name":".."},{"name":"my stuff"},{"name":"backup"}]}

SELECT _1.dir_name, _1.owner FROM S3Object[*]

{"dir_name":"important_docs","owner":"Amazon S3"}
{"dir_name":"other_docs","owner":"User"}

WHERE Clause

The WHERE clause follows this syntax:

WHERE condition

The WHERE clause filters data based on the condition. A condition is an expression that has a Boolean result. Only data for which the condition evaluates to TRUE are returned in the result.

For tabular data, an evaluated filter is applied to entire rows. For example, the condition a < 5 applied to the following tabular data results in the exclusion of not only the value 7 in column a, but the entire last row.

Input:

a	b	c
1	2	3
4	5	6
7	8	9

Output filtered with a < 5:

a	b	c
1	2	3
4	5	6

Another example:

SELECT city, date FROM Object WHERE amt > 100

LIMIT Clause

The LIMIT clause follows this syntax:

LIMIT number

The LIMIT clause limits the number of records that you want the query to return based on number.

Attribute Access

The SELECT and WHERE clauses can refer to record data by using one of the methods in the following sections, depending on whether the file that is being queried is in CSV or JSON format.

CSV

Column Numbers - You can refer to the Nth column of a row with the column name _N, where N is the column position. The position count starts at 1. For example, the first column is named _1 and the second column is named _2.

You can refer to a column as _N or alias._N. For example, _2 and myAlias._2 are both valid ways to refer to a column in the SELECT list and WHERE clause.
Column Headers - For objects in CSV format that have a header row, the headers are available to the SELECT list and WHERE clause. In particular, as in traditional SQL, within SELECT and WHERE clause expressions, you can refer to the columns by alias.column_name or column_name.

JSON

Document - You can access JSON document fields as alias.name. You can also access nested fields, for example, alias.name1.name2.name3.
List - You can access elements in a JSON list by using zero-based indexes with the [] operator. For example, you can access the second element of a list as alias[1]. You can combine accessing list elements with fields, for example, alias.name1.name2[1].name3.

Examples: Consider this JSON object as a sample dataset:

{"name": "Susan Smith",
"org": "engineering",
"projects":
    [
     {"project_name":"project1", "completed":false},
     {"project_name":"project2", "completed":true}
    ]
}

Example #1:

SELECT s.name FROM S3Object s

{"name":"Susan Smith"}

Example #2:

SELECT s.projects[0].project_name FROM S3Object s

{"project_name":"project1"}

Case Sensitivity

Like S3 Select, with AMSelect you can use double quotation marks to indicate that column headers for CSV objects and attributes for JSON objects are case sensitive. Without double quotation marks, object headers and attributes are case insensitive. An error is thrown in cases of ambiguity.

The following examples are either:

Amazon S3 objects in CSV format with the specified column headers, and with FileHeaderInfo set to Use for the query request.
Amazon S3 objects in JSON format with the specified attributes.

Example #1: The object being queried has the header or attribute NAME.

The following expression successfully returns values from the object. Because there are no quotation marks, the query is case insensitive.

SELECT s.name from S3Object s

The following expression results in a 400 error MissingHeaderName. Because there are quotation marks, the query is case sensitive.

SELECT s."name" from S3Object s

Example #2: The Amazon S3 object being queried has one header or attribute with NAME and another header or attribute with name.

The following expression results in a 400 error AmbiguousFieldName. Because there are no quotation marks, the query is case insensitive, but there are two matches, so the error is thrown.

SELECT s.name from S3Object s

The following expression successfully returns values from the object. Because there are quotation marks, the query is case sensitive, so there is no ambiguity.

SELECT s."NAME" from S3Object s

Reserved Keywords

AMSelect has a set of reserved keywords that are needed to run the SQL expressions used to query object content. Reserved keywords include function names, data types, operators, and so on. In some cases, user-defined terms, such as the column headers for CSV files or attributes for JSON objects, might clash with a reserved keyword. When this happens, you must use double quotation marks to indicate that you are intentionally using a user-defined term that clashes with a reserved keyword. Otherwise a 400 parse error will result.

For the full list of reserved keywords, see Reserved Keywords.

Example: The object being queried has a header or attribute named CAST, which is a reserved keyword.

The following expression successfully returns values from the object. Because quotation marks are used in the query, S3 Select uses the user-defined header or attribute.

SELECT s."CAST" from S3Object s

The following expression results in a 400 parse error. Because no quotation marks are used in the query, CAST clashes with a reserved keyword.

SELECT s.CAST from S3Object s