FROM clause (NoSQL query)

APPLIES TO: NoSQL

The FROM (FROM <from_specification>) clause is optional, unless the source is filtered or projected later in the query. A query like SELECT * FROM products enumerates over an entire container regardless of the name. You can also use the special identifier ROOT for the container instead of using the container name.

The FROM clause enforces the following rules per query:

• The container can be aliased, such as SELECT p.id FROM products AS p or simply SELECT p.id FROM products p. Here, p is the alias for the container. The container doesn't necessarily need to be named products or p. AS is an optional keyword to alias the identifier.
• Once aliased, the original source name can't be bound. For example, SELECT products.id FROM products p is syntactically invalid because the identifier products has been aliased and can't be resolved anymore.
• All referenced properties must be fully qualified, to avoid any ambiguous bindings in the absence of strict schema adherence. For example, SELECT id FROM products p is syntactically invalid because the property id isn't bound. The query should instead reference the property id using p.id (or <alias>.<property-name>).

Syntax

FROM <from_specification>  
  
<from_specification> ::=
        <from_source> {[ JOIN <from_source>][,...n]}  
  
<from_source> ::=
          <container_expression> [[AS] input_alias]  
        | input_alias IN <container_expression>  
  
<container_expression> ::=
        ROOT
     | container_name  
     | input_alias  
     | <container_expression> '.' property_name  
     | <container_expression> '[' "property_name" | array_index ']'

Arguments

Remarks

All aliases provided or inferred in the <from_source>(s) must be unique. The Syntax <container_expression> '.' property_name is the same as <container_expression> '[' "property_name" ']'. However, the latter syntax can be used if a property name contains a nonidentifier character.

Handling missing properties, missing array elements, and undefined values

If a container expression accesses properties or array elements and that value doesn't exist, that value is ignored and not processed further.

Container expression context scoping

A container expression may be container-scoped or item-scoped:

• An expression is container-scoped, if the underlying source of the container expression is either ROOT or container_name. Such an expression represents a set of items retrieved from the container directly, and isn't dependent on the processing of other container expressions.

• An expression is item-scoped, if the underlying source of the container expression is input_alias introduced earlier in the query. Such an expression represents a set of items obtained by evaluating the container expression. This evaluation is performed in the scope of each item belonging to the set associated with the aliased container. The resulting set is a union of sets obtained by evaluating the container expression for each of the items in the underlying set.

Examples

In this first example, the FROM clause is used to specify the current container as a source, give it a unique name, and then alias it. The alias is then used to project specific fields in the query results.

SELECT VALUE {
    name: e.name,
    location: e.workLocation
}
FROM
    employees e

[
  {
    "name": "Tijana Stankovic",
    "location": {
      "office": "Redmond, WA"
    }
  },
  {
    "name": "Jean Nadeau",
    "location": {
      "type": "Remote"
    }
  }
]

In this next example, the FROM clause can also reduce the source to a smaller subset. To enumerate only a subtree in each item, the subroot can become the source. An array or object subroot can be used as a source.

SELECT 
    *
FROM
    employees.employment e

[
  {
    "hireDateTime": "2023-01-01T06:30:00.000Z",
    "weeklyHours": 40,
    "team": "Retail support"
  },
  {
    "hireDateTime": "2023-01-01T09:30:00.000Z",
    "team": "Retail support"
  },
  {
    "hireDateTime": "2023-01-01T15:30:00.000Z",
    "weeklyHours": 32,
    "team": "Retail support"
  }
]

Related content

• SELECT clause
• WHERE clause