0.0.5 • Published 6 years ago
cosmosdb-query-builder v0.0.5
CosmosDB Query Builder
An yet another CosmosDB(DocumentDB) query builder which inspired by SQL::Format.
SYNOPSIS
Pass the format, a table name, and your binding parameters.
const builder = require('cosmosdb-query-builder');
const querySpec = builder.query(
'SELECT %l %c FROM %t WHERE %w %o',
20,
['id', 'age'],
'c',
{ id: 200 },
{
order: {
sort: 'desc',
key: 'createdAt'
}
}
);
console.log(querySpec);
/*
{ query: 'SELECT TOP @_top c.id, c.age FROM c WHERE ( c.id = @id588 ) ORDER BY c.createdAt desc',
parameters:
[ { name: '@_top', value: 20 },
{ name: '@id588', value: '200' } ] }
*/
const queryIterator = this.documentClient.queryDocuments(this.collectionUrl, querySpec);
// Enjoy!
Operators
%l
Limit (means TOP
).
builder.query('SELECT %l * FROM c', 'c', 20);
// 'SELECT TOP @_top * FROM c'
%c
Columns which processed "table" prefix.
builder.query('SELECT %c FROM c', 'c', ['id', 'age']);
// 'SELECT c.id, c.age FROM c'
You can use any functions (set as func
due to reserved word).
builder.query('SELECT %c FROM c', 'c',
[
{
1: {
function: 'COUNT',
alias: 'count'
}
}
]
);
// 'SELECT COUNT(1) count FROM c'
builder.query('SELECT %c FROM c', 'c',
[
'id',
{
viewed: {
func: 'IS_DEFINED',
alias: 'viewed'
}
}
]
);
// 'SELECT c.id, IS_DEFINED(c.viewed) viewed FROM c'
%t
Replace table name. Unfortunately, currently there is almost no meaning!
builder.query('SELECT * FROM %t', 'c');
// 'SELECT * FROM c'
%w
Where clauses. You can specify it various ways.
Also there will be added "table" prefix.
Simplest one:
builder.query(
'SELECT * FROM c WHERE %w', 'c',
{ id: 'AndersenFamily' }
);
// 'SELECT * FROM c WHERE c.id = "AndersonFamily"'
Specify operator:
builder.query(
'SELECT * FROM c WHERE %w', 'c',
{ id: {
op: '>',
value: 1000
}
}
);
// 'SELECT * FROM c WHERE c.id > 1000'
Specify type (default: string):
builder.query(
'SELECT * FROM c WHERE %w', 'c',
{ id: {
type: 'string',
value: 1000
}
}
);
// 'SELECT * FROM c WHERE c.id = "1000"'
Functions (set as func
due to reserved word):
builder.query(
'SELECT * FROM c WHERE %w', 'c',
{
viewed: {
func: 'NOT IS_DEFINED',
}
}
);
// 'SELECT * FROM c WHERE NOT IS_DEFINED(c.viewed)'
AND/OR (Can be nest):
builder.query(
'SELECT * FROM c WHERE %w', 'c',
{
OR: {
id: 1000,
age: 20,
}
}
);
// 'SELECT * FROM c WHERE c.id = "1000" OR age = "20"'
%o
Order options. A default sort order is desc
.
builder.query(
'SELECT * FROM c %o', 'c',
{
sort: 'desc',
key: 'createdAt'
}
);
// 'SELECT * FROM c ORDER BY c.createdAt DESC
NOTES
- Currently JOIN and GROUP options is not supported (Welcome to your PR!)