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.

.. code-block:: sql

   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:

.. code-block:: sql

   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:

.. code-block:: sql

   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:

.. code-block:: json

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

.. code-block:: sql

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

.. code-block:: json

   {"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:

.. code-block:: sql

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

.. code-block:: json

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

Example #2
~~~~~~~~~~

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

.. code-block:: json

   { "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" }

.. code-block:: sql

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

.. code-block:: json

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

.. code-block:: sql

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

.. code-block:: json

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

WHERE Clause
------------

The ``WHERE`` clause follows this syntax:

.. code-block:: sql

   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:

.. list-table::
   :header-rows: 1
   :widths: 1 1 1

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

Output filtered with ``a < 5``:

.. list-table::
   :header-rows: 1
   :widths: 1 1 1

   * - a
     - b
     - c
   * - 1
     - 2
     - 3
   * - 4
     - 5
     - 6

Another example:

.. code-block:: sql

   SELECT city, date FROM Object WHERE amt > 100

LIMIT Clause
------------

The ``LIMIT`` clause follows this syntax:

.. code-block:: sql

   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:

  .. code-block:: json

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

  Example #1:

  .. code-block:: sql

     SELECT s.name FROM S3Object s

  .. code-block:: json

     {"name":"Susan Smith"}

  Example #2:

  .. code-block:: sql

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

  .. code-block:: json

     {"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.

.. code-block:: sql

   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.

.. code-block:: sql

   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.

.. code-block:: sql

   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.

.. code-block:: sql

   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 :doc:`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.

.. code-block:: sql

   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.

.. code-block:: sql

   SELECT s.CAST from S3Object s