Edit

SELECT clause in Azure Cosmos DB

  • Article
  • 2 minutes to read

APPLIES TO: NoSQL

Every query consists of a SELECT clause and optional FROM and WHERE clauses, per ANSI SQL standards. Typically, the source in the FROM clause is enumerated, and the WHERE clause applies a filter on the source to retrieve a subset of JSON items. The SELECT clause then projects the requested JSON values in the select list.

Syntax

SELECT <select_specification>  

<select_specification> ::=
      '*'
      | [DISTINCT] <object_property_list>
      | [DISTINCT] VALUE <scalar_expression> [[ AS ] value_alias]  
  
<object_property_list> ::=
{ <scalar_expression> [ [ AS ] property_alias ] } [ ,...n ]

Arguments

  • <select_specification>

    Properties or value to be selected for the result set.

  • '*'

    Specifies that the value should be retrieved without making any changes. Specifically if the processed value is an object, all properties will be retrieved.

  • <object_property_list>

    Specifies the list of properties to be retrieved. Each returned value will be an object with the properties specified.

  • VALUE

    Specifies that the JSON value should be retrieved instead of the complete JSON object. This, unlike <property_list> does not wrap the projected value in an object.

  • DISTINCT

    Specifies that duplicates of projected properties should be removed.

  • <scalar_expression>

    Expression representing the value to be computed. See Scalar expressions section for details.

Remarks

The SELECT * syntax is only valid if FROM clause has declared exactly one alias. SELECT * provides an identity projection, which can be useful if no projection is needed. SELECT * is only valid if FROM clause is specified and introduced only a single input source.

Both SELECT <select_list> and SELECT * are "syntactic sugar" and can be alternatively expressed by using simple SELECT statements as shown below.

  1. SELECT * FROM ... AS from_alias ...

    is equivalent to:

    SELECT from_alias FROM ... AS from_alias ...

  2. SELECT <expr1> AS p1, <expr2> AS p2,..., <exprN> AS pN [other clauses...]

    is equivalent to:

    SELECT VALUE { p1: <expr1>, p2: <expr2>, ..., pN: <exprN> }[other clauses...]

Examples

The following SELECT query example returns address from Families whose id matches AndersenFamily:

    SELECT f.address
    FROM Families f
    WHERE f.id = "AndersenFamily"

The results are:

    [{
      "address": {
        "state": "WA",
        "county": "King",
        "city": "Seattle"
      }
    }]

Next steps