NoSQL Query language

What is a NoSQL Query? Querying your NoSQL database is essential in many applications. The codehooks.io NoSQL database use a subset of the popular MongoDB NoSQL query language. NoSQL queries are used in the database API to find and filter data. NoSQL database queries are powerful tools for developing backend application logic.

NoSQL Query Example: A Complete Code Example for a Database REST API

The example serverless JavaScript function below shows how to create a REST API that runs a NoSQL query against the database to fetch 100 items from the customers collection where the customer.status equals GOLD.

import {app, Datastore} from 'codehooks-js'

async function getData(req, res) {
    const conn = await Datastore.open();
    const query = {"status": "GOLD"};
    const options = {
        limit:100
    }
    conn.getMany('customers', query, options).json(res);
}

// Serverless REST API and query route
app.get('/customers', getData);

export default app.init(); // Bind functions to the serverless runtime

note

All query fields are case sensitive.

Filtering data from the database

Filtering are performed using a combination of filters, logical and conditional operators explained below.

Quick overview

Operator	Description	Example
field	Match a single field value	{"field": "value"}
fields	Match multiple fields and values	{"field1": "value1", "field2": "value2"}
$regex	Match field with a regular expression	{"field" : {$regex : "^foo"}}
$startsWith	Match field with start string segment	{"field": {"$startsWith": "value"}}
$endssWith	Match field with end string segment	{"field": {"$endsWith": "value"}}

Match multiple fields

Multiple fields are matched by name-value pairs in the a query:

const query = {"field1": "value1", "field2": "value2"}

This is actually the same as using the $and operator:

const query = {$and: [{"field1": "value1"}, {"field2": "value2"}]}

Match sub fields

Sub fields are matched by dot.property in the URL query parameter:

const query = {"field1.property": "value1"}

If your sub propery is an array, you must use the $elemMatch operator.

$regex operator

Match a regular expression against field. Optional $options values docs.

const query = {"name" : {$regex : "^joe", $options: "i"}}

// or with native JS Regex
const query = {"name" : /^joe/}}

$startsWith operator

Field is matched by starting string of value:

const query = {"Player": {"$startsWith": "Lionel"}}

$endsWith operator

Field is matched by ending string of value:

const query = {"Player": {"$endsWith": "Messi"}}

Logical operators

Quick overview

Operator	Description	Example
$not	Negation logical operator	{"field" : {$not : val}}
$in	Match any value in array	{"field" : {$in : [value1, value2, ...]}}
$nin	Not match any value in array	{"field" : {$nin : [value1, value2, ...]}}
$or	Logical operator	{$or: [{"status": "GOLD"}, {"status": "SILVER"}]}
$and	Logical operator	{$and: [{"status": "GOLD"}, {"sales": 1000}]}

$not operator

Return documents not matching the query.

const query = {"name" : {$not : "Joe"}}

$in operator

Return documents matching any values.

const query = {"name" : {$in : ["Joe", "Jane", "Donald"]}}

$nin operator

Return documents not matching any of the values.

const query = {"name" : {$nin : ["Joe", "Jane", "Donald"]}}

$or operator

Return documents that matches one or the other field.

const query = {$or: [{"name": "Jane"}, {"name": "Donald"}]}

$and operator

Return documents both fields.

const query = {$and: [{"name": "Jane"}, {"last-name": "Cassidy"}]}

Conditional operators

Quick overview

Operator	Description	Example
$gt	Greater than	{"salary": {$gt: 10000}}
$gte	Greater than or equal	{"salary": {$gte: 10000}}
$lt	Less than	{"salary": {$lt: 10000}}
$lte	Less than or equal	{"salary": {$lte: 10000}}
$ne	Not equal	{"email": {$ne: ""}}
$exists	Check if field exists	{"field": {$exists: true|false}}
$elemMatch	Array element matching	{"contact":{$elemMatch:{"name":"Anderson", age:35}}}

$gt operator

Return documents that matches each field value greater than numeric value.

const query = {"salary": {$gt: 10000}}

$gte operator

Return documents that matches each field value greater than or equal to numeric value.

const query = {"salary": {$gte: 10000}}

$lt operator

Return documents that matches each field value less than numeric value.

const query = {"salary": {$lt: 10000}}

$lte operator

Return documents that matches each field value less than or equal to numeric value.

const query = {"salary": {$lte: 10000}}

$exists operator

Return documents that matches each field with a value.

const query = {"field": {$exists: true}}

$exists (sub array) operator

Return documents that matches each sub field with any value.

const query = {"field.0": {$exists: true}}

$elemMatch operator

Return documents that matches at least one of the elements in an array field.

const query = {"contact":{$elemMatch:{"name":"Anderson", age:35}}}

$date operator

Querying based on dates are done using the $date operator combined with ISO date strings. For example:

// between two dates
const query = {"_changed":{$gt:{"$date":"2016-08-01"}, $lt:{"$date":"2016-08-05"}}}

SQL to NoSQL query mapping examples

The following list shows SQL example statements expressed as NoSQL queries.

SELECT * FROM users

/*
* SQL statement: 
* SELECT * FROM users
* expressed as a nosql database query
*/
const db = await Datastore.open();
db.find('users')

SELECT user_id, status FROM users

const query = {};
const opt = {
    hints: {$fields: {user_id: 1, status: 1}}
}
db.find('users', query, opt)

SELECT * FROM users  WHERE status = "A"

const query = {status: "A"};
db.find('users', query)

SELECT * FROM users  WHERE status != "A"

const query = {"status":{"$not":"A"}}
db.find('users', query)

SELECT * FROM users  WHERE status = "A" AND age = 50

const query = {"status": "A", "age": 50 }
db.find('users', query)

SELECT * FROM users  WHERE status = "A" OR age = 50

const query = { "$or": [ { "status": "A" } ,{ "age": 50 } ] }
db.find('users', query)

SELECT * FROM users  WHERE age > 25

const query = { "age": { "$gt": 25 } }
db.find('users', query)

SELECT * FROM users  WHERE user_id like "bc%"

const query = { "user_id": /^bc/}
db.find('users', query)

SELECT * FROM users  WHERE status = "A" ORDER BY name ASC

// Use the CLI to create a sorted index
// $ codehooks createindex --collection users --index name
const query = { "status": "A" }
const opt = {
    sort: {"name": 1}
}
db.find('users', query, opt)

SELECT * FROM users  WHERE status = "A" ORDER BY name DESC

// Use the CLI to create a sorted index
// $ codehooks createindex --collection users --index name
const query = { "status": "A" }
const opt = {
    sort: {"name": -1}
}
db.find('users', query, opt)

SELECT COUNT(*) FROM users

const query = {}
const opt = {
    hints: {$onlycount: true}, 
}
db.find('users', query, opt)

SELECT COUNT(*) FROM users WHERE age > 30

const query = {age: {$gt: 30}}
const opt = {
    hints: {$onlycount: true}
}
db.find('users', query, opt)

SELECT * FROM users LIMIT 1

const query = {}
const opt = { limit: 1 }
db.find('users', query, opt)

SELECT * FROM users LIMIT 5 SKIP 10

const query = {}
const opt = {
    limit: 5,
    offset: 10
}
db.find('users', query, opt)