Skip to main content

Query Builder

Query Builder is a flexible tool designed to create N1QL queries by specifying parameters and methods. Query Builder lets you: create queries of unlimited length and complexity without the need to know the syntax of N1QL Queries.

Using the Query Builder​

There are 3 ways to use the Query Builder: by using parameters, by using access functions, or by combining both.

Build a Query by Using Parameters​

To create queries by using parameters it is mandatory to define in the query constructor the parameters of the query and the name of the collection.

const params = {
  select: [
    {
      $count: {
        $field: {
          name: 'type',
        },
        as: 'odm',
      },
    },
  ],
  let: { amount_val: 10, size_val: 20 },
  where: {
    $or: [
      { price: { $gt: 'amount_val', $isNotNull: true } },
      { auto: { $gt: 10 } },
      { amount: 10 }
    ],
    $and: [
      { price2: { $gt: 1.99, $isNotNull: true } },
      {
        $or: [
          { price3: { $gt: 1.99, $isNotNull: true } },
          { id: '20' }
        ]
      },
      { name: { $eq: 'John', $ignoreCase: true } },
    ],
    $any: {
      $expr: [{ search: { $in: 'address' } }],
      $satisfies: { search: '10' },
    },
    search: { $in: ['address'] },
  },
  groupBy: [{ expr: 'type', as: 'sch' }],
  letting: { amount_v2: 10, size_v2: 20 },
  having: { type: { $like: '%hotel%' } },
  orderBy: { type: 'DESC' },
  limit: 10,
  offset: 1,
  use: ['airline_8093', 'airline_8094'],
};
const query = new Query(params, 'travel-sample').build();
console.log(query);
-- N1QL query result:
SELECT COUNT(type) AS odm
FROM `travel-sample`._default._default
 USE KEYS ["airline_8093","airline_8094"]
 LET amount_val=10, size_val=20
WHERE ((price > "amount_val"
  AND price IS NOT NULL)
  OR auto > 10
  OR amount = 10)
  AND ((price2 > 1.99
  AND price2 IS NOT NULL)
  AND ((price3 > 1.99
    AND price3 IS NOT NULL)
    OR id = "20")
  AND (LOWER(name) = LOWER("John")))
  AND ANY search IN address SATISFIES address = "10" END
  AND search IN ["address"]
GROUP BY type AS sch LETTING amount_v2=10, size_v2=20
HAVING type LIKE "%hotel%"
ORDER BY type DESC
LIMIT 10 OFFSET 1

Build a Query by Using Access Functions​

Creating queries by using the access function is very similar to create them with parameters. The difference is that the parameters are not passed directly to the constructor, instead, they are passed using the different functions available in the Query Class.

const select = [
  {
    $count: {
      $field: { name: 'type' },
      as: 'odm',
    },
  },
  {
    $max: {
      $field: 'amount',
    },
  },
];
const letExpr = { amount_val: 10, size_val: 20 };
const where = {
  $or: [
    { price: { $gt: 'amount_val', $isNotNull: true } },
    { auto: { $gt: 10 } },
    { amount: 10 }
  ],
  $and: [
    { price2: { $gt: 1.99, $isNotNull: true } },
    {
      $or: [
        { price3: { $gt: 1.99, $isNotNull: true } },
        { id: '20' }
      ]
    },
  ],
};
const groupBy = [{ expr: 'type', as: 'sch' }];
const having = { type: { $like: '%hotel%' } };
const lettingExpr = { amount_v2: 10, size_v2: 20 };
const orderBy = { type: 'DESC' };
const limit = 10;
const offset = 1;
const useExpr = ['airline_8093', 'airline_8094'];

const query = new Query({}, 'collection-name')
  .select(select)
  .let(letExpr)
  .where(where)
  .groupBy(groupBy)
  .letting(lettingExpr)
  .having(having)
  .orderBy(orderBy)
  .limit(limit)
  .offset(offset)
  .useKeys(useExpr)
  .build();
console.log(query);
-- N1QL query result:
SELECT COUNT(type) AS odm, MAX(amount)
FROM `travel-sample` USE KEYS ['airline_8093','airline_8094']
  LET amount_val = 10, size_val = 20
WHERE ((price > amount_val
  AND price IS NOT NULL)
  OR auto > 10
  OR amount = 10)
  AND ((price2 > 1.99
  AND price2 IS NOT NULL)
  AND ((price3 > 1.99
    AND price3 IS NOT NULL)
    OR id = '20'))
GROUP BY type AS sch LETTING amount_v2=10, size_v2=20
HAVING type LIKE "%hotel%"
ORDER BY type = 'DESC'
LIMIT 10 OFFSET 1

Build a Query by Using Parameters and Function Parameters​

const select = [{ $field: 'address' }];
const query = new Query({ where: { price: { $gt: 5 } } }, 'travel-sample')
  .select(select)
  .limit(10)
  .build();
console.log(query);
-- N1QL query result:
SELECT address
FROM `travel-sample`
WHERE price > 5
LIMIT 10

Advanced Example of Using the WHERE Clause​

const where = {
  $or: [
    { address: { $isNull: true } },
    { free_breakfast: { $isMissing: true } },
    { free_breakfast: { $isNotValued: true } },
    { id: { $eq: 8000 } },
    { id: { $neq: 9000 } },
    { id: { $gt: 7000 } },
    { id: { $gte: 6999 } },
    { id: { $lt: 5000 } },
    { id: { $lte: 4999 } },
  ],
  $and: [
    { address: { $isNotNull: true } },
    { address: { $isNotMissing: true } },
    { address: { $isValued: true } }
  ],
  $not: [
    {
      address: { $like: '%59%' },
      name: { $notLike: 'Otto%' },
      $or: [{ id: { $btw: [1, 2000] } }, { id: { $notBtw: [2001, 8000] } }],
    },
    {
      address: { $like: 'St%', $ignoreCase: true },
    },
  ],
};
const query = new Query({}, 'travel-sample')
  .select()
  .where(where)
  .limit(20)
  .build();
console.log(query);
-- N1QL query result:
SELECT *
FROM `travel-sample`
WHERE (address IS NULL
  OR free_breakfast IS MISSING
  OR free_breakfast IS NOT VALUED
  OR id = 8000
  OR id != 9000
  OR id > 7000
  OR id >= 6999
  OR id < 5000
  OR id <= 4999)
  AND (address IS NOT NULL
  AND address IS NOT MISSING
  AND address IS VALUED)
  AND NOT (address LIKE "%59%"
  AND name NOT LIKE "Otto%"
  AND (id BETWEEN 1 AND 2000
    OR id NOT BETWEEN 2001 AND 8000)
  AND (LOWER(address) LIKE LOWER("St%")))
LIMIT 20

::: tip Note Can also use ignoreCase as part of the build method, this will always prioritize the $ignoreCase value defined in clause

const expr_where = {
  $or: [
    { address: { $like: '%57-59%', $ignoreCase: false } }, // ignoreCase not applied
    { free_breakfast: true },
    { name: { $eq: 'John' } } //  ignoreCase applied
  ],
};
/**
 * Can also use:
 * const expr_where = {
 *   $or: [
 *     ...
 *     { name: 'John' } // ignoreCase applied
 *   ],
 * };
 *
 */
const query = new Query({}, 'travel-sample');
const result = query
  .select([{ $field: 'address' }])
  .where(expr_where)
  .build({ ignoreCase: true }); // ignoreCase enabled for WHERE clause
console.log(result)

Would have as output:

-- N1QL query result:
SELECT address
FROM `travel-sample`
WHERE (address LIKE "%57-59%"
  OR free_breakfast = TRUE
  OR (LOWER(name) = LOWER("John")));

:::

N1QL SELECT Clause Structure​

See definition here

The syntax of a SELECT clause in n1ql is documented here.

Available Result Expression Arguments​

keyvalue
\$allALL
\$distinctDISTINCT
\$rawRAW
\$elementELEMENT
\$valueVALUE

Available Aggregation Functions​

keyvalue
\$arrayAggARRAY_AGG
\$avgAVG
\$meanMEAN
\$countCOUNT
\$countnCOUNTN
\$maxMAX
\$medianMEDIAN
\$minMIN
\$stddevSTDDEV
\$stddevPopSTDDEV_POP
\$stddevSampSTDDEV_SAMP
\$sumSUM
\$varianceVARIANCE
\$variancePopVARIANCE_POP
\$varianceSampVARIANCE_SAMP
\$varPopVAR_SAMP
\$varSampVAR_SAMP

N1QL SELECT Nested Clause Example​

const query = new Query({}, 'travel-sample a');
const result = query
  .select([{
    $field: {
      name: '{ "latLon": { geo.lat, geo.lon } }',
      as: 'geo'
    }
  }])
  .where({ 'a.type': 'hotel' })
  .build();
SELECT {"latLon": {geo.lat, geo.lon} } AS geo
FROM `travel-sample` a
WHERE a.type = "hotel"

N1QL WHERE Clause Structure​

See definition here

The syntax of a WHERE clause in N1QL is documented here.

Available Comparison Operators​

keyvalue
\$isNullIS NULL
\$isNotNullIS NOT NULL
\$isMissingIS MISSING
\$isNotMissingIS NOT MISSING
\$isValuedIS VALUED
\$isNotValuedIS NOT VALUED
\$eq=
\$neq!=
\$gt>
\$gte>=
\$lt<
\$lte<=
\$likeLIKE
\$notLikeNOT LIKE
\$btwBETWEEN
\$notBtwNOT BETWEEN

Available Logical Operators​

keyvalue
\$andAND
\$orOR
\$notNOT

Available Collection Operators​

keyvalue
\$anyANY
\$everyEVERY
\$inIN
\$notInNOT IN
\$withinWITHIN
\$notWithinNOT WITHIN
\$satisfiesSATISFIES

Available String Modifiers:

keyvalue
\$ignoreCaseBoolean

Functional COLLECTION Operators Examples​

Let's take a deeper dive into using various collection operators with Ottoman's Query Builder

Using Deep Search Operators ( [NOT] IN | WITHIN )​

The IN operator specifies the search depth to include only the current level of an array and not to include any child or descendant arrays. On the other hand the WITHIN operator include the current level of an array and all of its child and descendant arrays.

// Defining our where clause
const where: LogicalWhereExpr = {
  type: 'airline',
  country: { $in: ['United Kingdom', 'France'] },
  [`"CORSAIR"`]: { $within: { $field: 't' } },
};
const query = new Query({}, `travel-sample t`)
  .select('name,country,id')
  .where(where)
  .build();

With the above implementation will get this sql query:

SELECT name, country, id
FROM `travel-sample` t
WHERE type = "airline"
  AND country IN ["United Kingdom","France"]
  AND "CORSAIR" WITHIN t;

Now we call to Ottoman instance

const ottoman = getDefaultInstance();
await ottoman.start();
// Query execution
const response = await ottoman.query(query);
console.log(response);

Here is the output:

[
  {
    country: 'France',
    id: 1908,
    name: 'Corsairfly',
  }
]

Using Range Predicate Operators ( ANY | EVERY )​

ANY is a range predicate that tests a Boolean condition over the elements or attributes of a collection, object, or objects. It uses the IN and WITHIN operators to range through the collection. If at least one item in the array satisfies the ANY expression, then it returns the entire array; otherwise, it returns an empty array. Let's see it in action:

// Defining our selection
const selectExpr = 'airline,airlineid,destinationairport,distance';

// Using LET operator to store some data
const letExpr: LetExprType = { destination: ['ATL'] };

For ANY and EVERY in Ottoman we use the operators $expr and $satisfies:

$expr: is an array of expressions with the structure: \ [{ SEARCH_EXPRESSION: { [$in|$within]: TARGET_EXPRESSION } }]

  • SEARCH_EXPRESSION: A string or expression that evaluates to a string representing the variable name in the ANY | EVERY loop.
  • TARGET_EXPRESSION: A string or expression that evaluates to a string representing the array to loop through.

$satisfies: An expression representing the limiting or matching clause to test against

Now we create the WHERE clause including the ANY operator:

const whereExpr: LogicalWhereExpr = {
  type: { $eq: 'route' },
  $and: [
    { sourceairport: { $eq: 'ABQ' } },
    {
      // Here is where the magic happen
      $any: {
        $expr: [
          { departure: { $in: 'schedule' } },
          { other: { $within: ['KL', 'AZ'] } }
        ],
        $satisfies: {
          $and: [
            { 'departure.utc': { $gt: '03:53' } },
            { other: { $field: 'airline' } }
          ],
        },
      },
    },
    { destinationairport: { $in: { $field: 'destination' } } },
  ],
};

const query = new Query({}, 'travel-sample')
  .select(selectExpr)
  .let(letExpr)
  .where(whereExpr)
  .build();
console.log(query);

We will obtain the query:

SELECT airline, airlineid, destinationairport, distance
FROM `travel-sample`
LET destination=["ATL"]
WHERE type = "route"
  AND (
    sourceairport = "ABQ"
    AND ANY departure IN schedule,
    other WITHIN ["KL","AZ"]
    SATISFIES (
        departure.utc > "03:53" AND other = airline
    ) END
    AND destinationairport IN destination)
const ottoman = getDefaultInstance();
await ottoman.start();

// After initializing Ottoman we run the query
const { rows } = await ottoman.query(query);
console.log(rows);

The query output:

[
  {
    airline: 'AZ',
    airlineid: 'airline_596',
    destinationairport: 'ATL',
    distance: 2038.3535078909663,
  },
  {
    airline: 'KL',
    airlineid: 'airline_3090',
    destinationairport: 'ATL',
    distance: 2038.3535078909663,
  },
]

EVERY is very similar to ANY with the main difference being that all the elements of the array must satisfy the defined condition. Let's see how it works:

// Changing a little the above where expression definition:
const whereExpr: LogicalWhereExpr = {
  type: { $eq: 'route' },
  $and: [
    { airline: { $eq: 'KL' } },
    { sourceairport: { $like: 'ABQ' } },
    { destinationairport: { $in: ['ATL'] } },
    {
      $every: {
        $expr: [{ departure: { $in: 'schedule' } }],
        $satisfies: { 'departure.utc': { $gt: '00:35' } },
      },
    },
  ],
};

// Building the query
const query = new Query({}, 'travel-sample')
  .select(selectExpr)
  .where(whereExpr)
  .build();
console.log(query);

The resulting query would be:

SELECT airline, airlineid, destinationairport, distance
FROM `travel-sample`
WHERE type = "route"
  AND (
    airline = "KL"
    AND sourceairport LIKE "ABQ"
    AND destinationairport IN ["ATL"]
    AND EVERY departure IN schedule SATISFIES departure.utc > "00:35" END
  );

Now let's run the query:

const ottoman = getDefaultInstance();
await ottoman.start();

const { rows } = await ottoman.query(query);
console.log(rows);

We would have as a result:

[
  {
    airline: 'KL',
    airlineid: 'airline_3090',
    destinationairport: 'ATL',
    distance: 2038.3535078909663,
  },
]

Query Builder & Model Find Method​

Let's start from query:

SELECT country, icao, name
FROM `travel-sample`
WHERE type = "airline"
  AND (country IN ["United Kingdom","France"])
  AND callsign IS NOT NULL
  AND ANY description WITHIN ["EU"] SATISFIES icao
  LIKE "%" || description END
LIMIT 2

Using Model find method

// Defining our SCHEMA
const airlineSchema = new Schema({
  callsign: String,
  country: String,
  iata: String,
  icao: String,
  id: Number,
  name: String,
  type: String,
});

// Model Airline creation
const Airline = model('airline', airlineSchema, { modelKey: 'type' });

//Start Ottoman instance
const ottoman = getDefaultInstance();
await ottoman.start();

// Our find method with a filter and options definitions
const response = await Airline.find(
  {
    type: 'airline',
    $and: [{ country: { $in: ['United Kingdom', 'France'] } }],
    callsign: { $isNotNull: true },
    $any: {
      $expr: [{ description: { $within: ['EU'] } }],
      $satisfies: { icao: { $like: { $field: '"%"||description' } } },
    },
  },
  { limit: 2, select: 'country,icao,name', lean: true },
);
// Print output
console.log(response.rows);

Via Query Builder

// Defining our query
const query = new Query({ select: 'country,icao,name' }, 'travel-sample')
  .where({
    type: 'airline',
    $and: [{ country: { $in: ['United Kingdom', 'France'] } }],
    callsign: { $isNotNull: true },
    $any: {
      $expr: [{ description: { $within: ['EU'] } }],
      $satisfies: { icao: { $like: { $field: '"%"||description' } } },
    },
  })
  .limit(2)
  .build();

//Start Ottoman instance
const ottoman = getDefaultInstance();
await ottoman.start();

// Runing query
const response = await ottoman.query(query);

// Print output
console.log(response.rows);

For above examples we get this output:

[
  {
    country: 'United Kingdom',
    icao: 'AEU',
    name: 'Astraeus'
  },
  {
    country: 'France',
    icao: 'REU',
    name: 'Air Austral'
  },
]

N1QL JOIN Clause Structure​

Notice: Currently the JOIN clause is only supported in string format.

See definition here

The syntax of a JOIN clause in n1ql is documented here.

N1QL GROUP BY Clause Structure​

See definition here

The syntax of a GROUP BY clause in n1ql is documented here.