A newer version of this documentation is available.

WHERE clause

reference

March 23, 2025

+ 12

The WHERE clause filters resultsets based specified conditions.

Purpose

When you want to narrow down your resultset by one or more criteria, use the WHERE clause to filter your resultset.

For example, list airports in France, detailed in Example 1:

SELECT airportname, city, country
FROM `travel-sample`.inventory.airport
WHERE country = "France"
LIMIT 4;

Syntax

where-clause ::= WHERE cond

Arguments

cond: [Required] Conditional expression that represents a filter to be applied to the resultset. Records for which the condition resolves to TRUE are propagated to the resultset.

You can construct complex conditional expressions, for example by using the logical operators AND, OR, and NOT.

Examples

Example 1. Use WHERE filter the resultset

To list only airports that are in France, use the WHERE clause for the "country" field.

n1ql
Copy
SELECT airportname, city, country
FROM `travel-sample`.inventory.airport
WHERE country = "France"
LIMIT 4;

Results

json
Copy
[
  {
    "airportname": "Calais Dunkerque",
    "city": "Calais",
    "country": "France"
  },
  {
    "airportname": "Peronne St Quentin",
    "city": "Peronne",
    "country": "France"
  },
  {
    "airportname": "Les Loges",
    "city": "Nangis",
    "country": "France"
  },
  {
    "airportname": "Couterne",
    "city": "Bagnole-de-l'orne",
    "country": "France"
  }
]

Example 2. Use WHERE and OR to filter the resultset

List only the landmarks that start with the letter "C" or "K". Note that the first position of the SUBSTR function is 0.

n1ql
Copy
SELECT name
FROM `travel-sample`.inventory.landmark
WHERE CONTAINS(SUBSTR(name,0,1),"C")
   OR CONTAINS(SUBSTR(name,0,1),"K")
LIMIT 4;

Results

json
Copy
[
  {
    "name": "City Chambers"
  },
  {
    "name": "Kingston Bridge"
  },
  {
    "name": "Clyde Arc"
  },
  {
    "name": "Clyde Auditorium"
  }
]

Example 3. Use WHERE, AND and NOT to filter the resultset

List landmark restaurants, except Thai restaurants.

n1ql
Copy
SELECT name, activity
FROM `travel-sample`.inventory.landmark
WHERE activity = "eat"
AND NOT CONTAINS(name,"Thai")
LIMIT 4;

Results

json
Copy
[
  {
    "activity": "eat",
    "name": "Hollywood Bowl"
  },
  {
    "activity": "eat",
    "name": "Spice Court"
  },
  {
    "activity": "eat",
    "name": "Beijing Inn"
  },
  {
    "activity": "eat",
    "name": "Ossie's Fish and Chips"
  }
]