# Class: Query

# Hierarchy

# Constructors

# constructor

+ new Query(conditions: IConditionExpr, collection: string): Query

Overrides BaseQuery.constructor

summary Create an instance of Query.

name Query

example

const query = new Query({
 $select: [{ $field: 'address' }],
 $where: {
   $nill: [
     { address: { $like: '%57-59%' } },
     { free_breakfast: true },
     { free_lunch: [1] }
   ]
  }
 },
 'travel-sample');
1
2
3
4
5
6
7
8
9
10
11

Parameters:

Name Type Description
conditions IConditionExpr List of SELECT clause conditions
collection string Collection name

Returns: Query

Query

# Properties

# Protected _collection

_collection: string

Inherited from BaseQuery._collection


# Protected _conditions

_conditions: IConditionExpr

Inherited from BaseQuery._conditions

# Accessors

# collection

get collection(): string

Returns: string

set collection(value: string): void

Parameters:

Name Type
value string

Returns: void


# conditions

get conditions(): IConditionExpr

Returns: IConditionExpr

set conditions(value: IConditionExpr): void

Parameters:

Name Type
value IConditionExpr

Returns: void

# Methods

# build

build(options: QueryBuildOptionsType): string

Overrides BaseQuery.build

Build a N1QL query from the defined parameters.

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

example

const expr_where = {
  $or: [
    { address: { $like: '%57-59%', $ignoreCase: false } }, // ignoreCase will not be applied
    { free_breakfast: true },
    { name: 'John' } //  ignoreCase will be applied
  ],
};
const query = new Query({}, 'travel-sample');
const result = query.select([{ $field: 'address' }])
   .where(expr_where)
   .build({ ignoreCase: true }); // ignore case is enabled for where clause elements
console.log(result)
1
2
3
4
5
6
7
8
9
10
11
12
SELECT address
FROM `travel-sample`
WHERE (address LIKE "%57-59%" OR free_breakfast = true OR `(LOWER(name) = LOWER("John"))`)
1
2
3

method

Parameters:

Name Type Default
options QueryBuildOptionsType { ignoreCase: false }

Returns: string


# compileFromConditions

compileFromConditions(conditionals: IConditionExpr): void

Converts the conditional parameters passed to the constructor to the properties of the N1QL Query.

method

Parameters:

Name Type
conditionals IConditionExpr

Returns: void


# execute

executeResult›(options: QueryBuildOptionsType, queryOptions: QueryOptions, ottomanInstance: Ottoman‹›): Promise‹QueryResult‹Result›› | Promise‹Result›

Type parameters:

Result

Parameters:

Name Type Default
options QueryBuildOptionsType { ignoreCase: false }
queryOptions QueryOptions {}
ottomanInstance Ottoman‹› getDefaultInstance()

Returns: Promise‹QueryResult‹Result›› | Promise‹Result›


# groupBy

groupBy(value: IGroupBy[]): Query

Add GROUP BY expression to SELECT clause.

method

example

const groupByExpr = [{ expr: 'COUNT(amount_val)', as: 'amount' }];
const query = new Query({}, 'travel-sample');
const result = query.select([{ $field: 'address' }])
   .groupBy(groupByExpr)
   .build()
console.log(result)
1
2
3
4
5
6
SELECT address
FROM `travel-sample`
GROUP BY COUNT(amount) AS amount
1
2
3

Parameters:

Name Type
value IGroupBy[]

Returns: Query


# having

having(value: LogicalWhereExpr): Query

Add HAVING expression to GROUP BY clause.

method

example

const groupByExpr = [{ expr: 'COUNT(amount_val)', as: 'amount' }];
const having = { address: { $like: '%58%' } };
const query = new Query({}, 'travel-sample');
const result = query.select([{ $field: 'address' }])
   .groupBy(groupByExpr)
   .having(having)
   .build()
console.log(result)
1
2
3
4
5
6
7
8
SELECT address
FROM `travel-sample`
GROUP BY COUNT(amount) AS amount
HAVING address LIKE '%58%'
1
2
3
4

Parameters:

Name Type
value LogicalWhereExpr

Returns: Query


# index

index(type: IndexType, name: string): Query

Add index type and name to INDEX clause.

method

example

const result = new Query({}, 'travel-sample')
 .index('DROP', 'travel_sample_id_test')
 .build();
console.log(result)
1
2
3
4
DROP INDEX `travel-sample`.`travel_sample_id_test`
1

Parameters:

Name Type
type IndexType
name string

Returns: Query


# let

let(value: LetExprType): Query

Overrides BaseQuery.let

Add LET expression to SELECT clause.

method

example

// SELECT expression definition
const selectExpr = 't1.airportname, t1.geo.lat, t1.geo.lon, t1.city, t1.type';

// LET expression definition
const letExpr: LetExprType = {
  min_lat: 71,
  max_lat: 'ABS(t1.geo.lon)*4+1',
  place: '(SELECT RAW t2.country FROM `travel-sample` t2 WHERE t2.type = "landmark")',
};

// WHERE expression definition
const whereExpr: LogicalWhereExpr = {
  $and: [
    { 't1.type': 'airport' },
    { 't1.geo.lat': { $gt: { $field: 'min_lat' } } },
    { 't1.geo.lat': { $lt: { $field: 'max_lat' } } },
    { 't1.country': { $in: { $field: 'place' } } },
  ],
};

// QUERY creation
const query = new Query({}, 'travel-sample t1')
 .select(selectExpr)
 .let(letExpr)
 .where(whereExpr)
 .build();

console.log(query);

// QUERY output
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT t1.airportname,
       t1.geo.lat,
       t1.geo.lon,
       t1.city,
       t1.type
FROM `travel-sample` t1
LET min_lat=71,
    max_lat=ABS(t1.geo.lon)*4+1,
    place=(
        SELECT RAW t2.country
        FROM `travel-sample` t2
        WHERE t2.type = "landmark")
WHERE (t1.type="airport"
        AND t1.geo.lat>min_lat
        AND t1.geo.lat<max_lat
        AND t1.country IN place);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// OTTOMAN initialization
const ottoman = getDefaultInstance();
await startInTest(ottoman);

// QUERY execution
const { rows } = await ottoman.query(query);

// RESULTS
console.log(rows)
1
2
3
4
5
6
7
8
9
[
  {
    airportname: 'Wiley Post Will Rogers Mem',
    city: 'Barrow',
    lat: 71.285446,
    lon: -156.766003,
    type: 'airport',
  },
  {
    airportname: 'Dillant Hopkins Airport',
    city: 'Keene',
    lat: 72.270833,
    lon: 42.898333,
    type: 'airport',
  },
]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Parameters:

Name Type
value LetExprType

Returns: Query


# letting

letting(value: LetExprType): Query

Add LETTING expression to GROUP BY clause.

method

example

const groupByExpr = [{ expr: 'COUNT(amount_val)', as: 'amount' }];
const letExpr = { amount_val: 10 };
const query = new Query({}, 'travel-sample');
const result = query.select([{ $field: 'address' }])
   .groupBy(groupByExpr)
   .let(letExpr)
   .build()
console.log(result)
1
2
3
4
5
6
7
8
SELECT address
FROM `travel-sample`
GROUP BY COUNT(amount) AS amount LETTING amount = 10
1
2
3

Parameters:

Name Type
value LetExprType

Returns: Query


# limit

limit(value: number): Query

Overrides BaseQuery.limit

Add LIMIT expression to SELECT clause.

method

example

const query = new Query({}, 'travel-sample');
const result = query.select([{ $field: 'address' }])
   .limit(10)
   .build()
console.log(result)
1
2
3
4
5
SELECT address
FROM `travel-sample`
LIMIT 10
1
2
3

Parameters:

Name Type
value number

Returns: Query


# offset

offset(value: number): Query

Overrides BaseQuery.offset

Add OFFSET expression to SELECT clause.

method

example

const query = new Query({}, 'travel-sample');
const result = query.select([{ $field: 'address' }])
   .offset(10)
   .build()
console.log(result)
1
2
3
4
5
SELECT address
FROM `travel-sample`
OFFSET 10
1
2
3

Parameters:

Name Type
value number

Returns: Query


# on

on(value: IIndexOnParams[]): Query

Add items to ON clause in INDEX clause.

method

example

const on = [{ name: 'travel-sample.callsing', sort: 'ASC' }];
const result = new Query({}, 'travel-sample')
   .index('CREATE', 'travel_sample_id_test')
   .on(on)
   .build();
console.log(result)
1
2
3
4
5
6
CREATE INDEX `travel_sample_id_test` ON `travel-sample` (`travel-sample.callsing`['ASC'])
1

Parameters:

Name Type
value IIndexOnParams[]

Returns: Query


# orderBy

orderBy(value: Record‹string, SortType›): Query

Overrides BaseQuery.orderBy

Add ORDER BY expression to SELECT clause.

method

example

const query = new Query({}, 'travel-sample');
const result = query.select([{ $field: 'address' }])
   .orderBy({ size: 'DESC' })
   .build()
console.log(result)
1
2
3
4
5
SELECT address
FROM `travel-sample`
ORDER BY size = 'DESC'
1
2
3

Parameters:

Name Type
value Record‹string, SortType

Returns: Query


# plainJoin

plainJoin(value: string): Query

Add JOIN expression to SELECT clause.

method

example

const query = new Query({}, 'beer-sample brewery');
const result = query.select([{ $field: 'address' }])
   .plainJoin(
       'JOIN `beer-sample` beer ON beer.brewery_id = LOWER(REPLACE(brewery.name, " ", "_"))'
    )
   .build()
console.log(result)
1
2
3
4
5
6
7
SELECT beer.name
FROM `beer-sample` brewery
JOIN `beer-sample` beer ON beer.brewery_id = LOWER(REPLACE(brewery.name, " ", "_"))
1
2
3

Parameters:

Name Type
value string

Returns: Query


# select

select(value?: ISelectType[] | string | undefined): Query

Overrides BaseQuery.select

Add result selectors to SELECT clause.

method

example

const query = new Query({}, 'travel-sample');
const result = query.select([{ $field: 'address' }]).build()
console.log(result)
1
2
3
SELECT address
FROM `travel-sample`
1
2

Parameters:

Name Type
value? ISelectType[] | string | undefined

Returns: Query


# useKeys

useKeys(value: string[]): Query

Overrides BaseQuery.useKeys

Add USE KEYS expression to SELECT clause.

method

example

const query = new Query({}, 'travel-sample');
const result = query.select([{ $field: 'address' }])
   .useKeys(['airlineR_8093'])
   .build()
console.log(result)
1
2
3
4
5
SELECT address
FROM `travel-sample` USE KEYS ['airlineR_8093']
1
2

Parameters:

Name Type
value string[]

Returns: Query


# usingGSI

usingGSI(): Query

Create INDEX using General Secondary Index (GSI).

method

example

const result = new Query({}, 'travel-sample')
   .index('CREATE', 'travel_sample_id_test')
   .usingGSI()
   .build();
console.log(result)
1
2
3
4
5
CREATE INDEX `travel_sample_id_test` USING GSI
1

Returns: Query


# where

where(value: LogicalWhereExpr): Query

Overrides BaseQuery.where

Add WHERE expression to SELECT clause.

method

example

const expr_where = {
  $or: [
    { address: { $like: '%57-59%' } },
    { free_breakfast: true },
    { name: { $eq: 'John', $ignoreCase: true } }
  ]
};
const query = new Query({}, 'travel-sample');
const result = query.select([{ $field: 'address' }])
   .where(expr_where)
   .build()
console.log(result)
1
2
3
4
5
6
7
8
9
10
11
12
SELECT address
FROM `travel-sample`
WHERE (address LIKE "%57-59%" OR free_breakfast = true OR (LOWER(name) = LOWER("John")))
1
2
3

Parameters:

Name Type
value LogicalWhereExpr

Returns: Query


# with

with(value: IIndexWithParams): Query

Add items to WITH clause in INDEX clause.

method

example

const withExpr = { nodes: ['192.168.1.1:8078'], defer_build: true, num_replica: 2 };
const result = new Query({}, 'travel-sample')
   .index('CREATE', 'travel_sample_id_test')
   .with(withExpr)
   .build();
console.log(result)
1
2
3
4
5
6
CREATE INDEX `travel_sample_id_test`
WITH {'nodes': ['192.168.1.1:8078'],'defer_build': true,'num_replica': 2}
1
2

Parameters:

Name Type
value IIndexWithParams

Returns: Query