Edit

WHERE clause (NoSQL query)

  • Article

APPLIES TO: NoSQL

The optional WHERE clause (WHERE <filter_condition>) specifies condition(s) that the source JSON items must satisfy for the query to include them in results. A JSON item must evaluate the specified conditions to true to be considered for the result. The index layer uses the WHERE clause to determine the smallest subset of source items that can be part of the result.

Syntax

WHERE <filter_condition>  
<filter_condition> ::= <scalar_expression>
Description
<filter_condition> Specifies the condition to be met for the items to be returned.
<scalar_expression> Expression representing the value to be computed.

Note

For more information on scalar expressions, see scalar expressions

Examples

This first example uses a simple equality query to return a subset of items. The = operator is used with the WHERE clause to create a filter based on simple equality.

SELECT VALUE {
    employeeName: e.name,
    currentTeam: e.team
}
FROM
    employees e
WHERE
    e.team = "Hospitality"

[
  {
    "employeeName": "Jordan Mitchell",
    "currentTeam": "Hospitality"
  },
  {
    "employeeName": "Ashley Schroeder",
    "currentTeam": "Hospitality"
  },
  {
    "employeeName": "Tomas Richardson",
    "currentTeam": "Hospitality"
  }
]

In this next example, a more complex filter is composed of scalar expressions.

SELECT VALUE {
    employeeName: e.name,
    vacationDaysRemaining: e.vacationDays
}
FROM
    employees e
WHERE
    e.vacationDays >= 7

[
  {
    "employeeName": "Ana Bowman",
    "vacationDaysRemaining": 10
  },
  {
    "employeeName": "Madison Butler",
    "vacationDaysRemaining": 7
  }
]

In this final example, a property reference to a boolean property is used as the filter.

SELECT VALUE {
    employeeName: e.name,
    isPartTime: e.partTime
}
FROM
    employees e
WHERE
    e.partTime

[
  {
    "employeeName": "Riley Ramirez",
    "isPartTime": true
  },
  {
    "employeeName": "Caleb Foster",
    "isPartTime": true
  }
]

Remarks

  • In order for an item to be returned, an expression specified as a filter condition must evaluate to true. Only the boolean value true satisfies the condition, any other value: undefined, null, false, a number scalar, an array, or an object doesn't satisfy the condition.

  • If you include your partition key in the WHERE clause as part of an equality filter, your query automatically filters to only the relevant partitions.

  • You can use the following supported binary operators:

    Operators Examples
    Arithmetic +,-,*,/,%
    Bitwise \|, &, ^, <<, >>, >>> (zero-fill right shift)
    Logical AND, OR, NOT
    Comparison =, !=, <, >, <=, >=, <>
    String \|\| (concatenate)

Related content