Edit

SELECT clause (NoSQL query)

  • Article

APPLIES TO: NoSQL

Every query consists of a SELECT clause and optionally 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

Description
<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 are retrieved.
<object_property_list> Specifies the list of properties to be retrieved. Each returned value is an object with the properties specified.
VALUE Specifies that the JSON value should be retrieved instead of the complete JSON object. This argument, unlike <property_list> doesn't 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. For more information, see scalar expressions section for details.

Examples

This first example selects two static string values and returns an array with a single object containing both values. Since the values are unnamed, a sequential generated number is used to name the equivalent json field.

SELECT "Adventure", "Works"

[
  {
    "$1": "Adventure",
    "$2": "Works"
  }
]

In this next example, JSON projection is used to fine tune the exact structure and field names for the resulting JSON object. Here, a JSON object is created with fields named department and team. The outside JSON object is still unnamed, so a generated number ($1) is used to name this field.

SELECT {
    department: "Sales",
    team: "Field sales"
}

[
  {
    "$1": {
      "department": "Sales",
      "team": "Field sales"
    }
  }
]

This example illustrates flattening the result set from the previous example to simplify parsing. The VALUE keyword is used here to prevent the wrapping of the results into another JSON object.

SELECT VALUE {
    department: "Sales",
    team: "Field sales"
}

[
  {
    "department": "Sales",
    "team": "Field sales"
  }
]

In this example, the VALUE keyword is used with a static string to create an array of strings as the result.

SELECT VALUE "Sales"

[
  "Sales"
]

In this final example, assume that there's a container with two items with various fields of different data types.

[
  {
    "team": "Field sales",
    "identity": {
      "name": "Parker McLean"
    },
    "contact": [
      "206-555-0147"
    ]
  },
  {
    "team": "Field sales",
    "identity": {
      "name": "Beibit Shayakhmet"
    },
    "contact": [
      "206-555-0178"
    ]
  }
]

This final example query uses a combination of a SELECT clause, the VALUE keyword, a FROM clause, and JSON projection to perform a common query with the results transformed to a JSON object for the client to parse.

SELECT VALUE {
    name: e.identity.name,
    team: e.team,
    phone: e.contact[0]
}
FROM
    employees e

[
  {
    "name": "Parker McLean",
    "team": "Field sales",
    "phone": "206-555-0147"
  },
  {
    "name": "Beibit Shayakhmet",
    "team": "Field sales",
    "phone": "206-555-0178"
  }
]

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:
    • SELECT * FROM ... AS from_alias ... is equivalent to: SELECT from_alias FROM ... AS from_alias ....
    • 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...].

Related content