SELECT Statements
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
SELECT Syntax
The following syntax diagram outlines the syntax supported by the Amazon DynamoDB adapter:
SELECT {
[ TOP <numeric_literal> ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
}
[ WHERE <search_condition> ]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | != | > | < | >= | <= | IS NULL | IS NOT NULL | BEGINSWITH | CONTAINS | NOT CONTAINS | BETWEEN | IN | NOT IN | LIKE | NOT LIKE | AND | OR } [ <expression> ]
} [ { AND | OR } ... ]
Examples
Return all columns:
SELECT * FROM Account
Rename a column:
SELECT "Name" AS MY_Name FROM Account
Search data:
SELECT * FROM Account WHERE FirstName <> 'Bob';
The Amazon DynamoDB APIs support the following operators in the WHERE clause: =, !=, >, <, >=, <=, IS NULL, IS NOT NULL, BEGINSWITH, CONTAINS, NOT CONTAINS, BETWEEN, IN, NOT IN, LIKE, NOT LIKE, AND, OR.
SELECT * FROM Account WHERE FirstName <> 'Bob';
Projection Functions
JSON_AVG(json, jsonpath)
Computes the average value of a JSON array within a JSON object.
json: The column containing JSON data.
jsonpath: The path to the json array.
JSON_COUNT(json, jsonpath)
Returns the number of elements in a JSON array within a JSON object.
json: The column containing JSON data.
jsonpath: The path to the json array.
JSON_MAX(json, jsonpath)
Gets the maximum value in a JSON array within a JSON object.
json: The column containing JSON data.
jsonpath: The path to the json array.
JSON_MIN(json, jsonpath)
Gets the minimum value in a JSON array within a JSON object.
json: The column containing JSON data.
jsonpath: The path to the json array.
JSON_SUM(json, jsonpath)
Computes the sum of the elements in a JSON within a JSON object.
json: The column containing JSON data.
jsonpath: The path to the json array.
JSON_EXTRACT(json, jsonpath)
Selects any value in a JSON array or object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
json: The JSON document to extract.
jsonpath: The XPath used to select the nodes. The JSONPath must be a string constant. The values of the nodes selected will be returned in a token-separated list.
XML_EXTRACT(xml, xpath [, separator])
Extracts an XML document using the specified XPath to flatten the XML. A comma is used to separate the outputs by default, but this can be changed by specifying the third parameter.
xml: The XML document to extract.
xpath: The XPath used to select the nodes. The nodes selected will be returned in a token-separated list.
separator: The optional token used to separate the items in the flattened response. If this is not specified, the separator will be a comma.