NoSQL query language

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 in developing backend application logic.

Query example: A complete code example for a REST API and a NoSQL database query

The example serverless JavaScript function below shows how to create a REST API to query the NoSQL 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 = {
        filter: query,
        limit:100
    }
    conn.getMany('customers', options).json(res);
}

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

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

note

All query fields are case sensitive.

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.

Filtering data from the database

Search-magnifier

Filtering are performed using a combination of logical and conditional operators.

Logical operators 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, ...]}}`
$regex	Match field	`{"field" : {$regex : "^foo"}}`
$or	Logical operator	`{$or: [{"status": "GOLD"}, {"status": "SILVER"}]}`
$and	Logical operator	`{$and: [{"status": "GOLD"}, {"sales": 1000}]}`

Conditional operators 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}}}`

$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"]}}

$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/}}

$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

$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 mapping

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 id, user_id, status FROM users`

const opt = {
    hints: {$fields: {_id_: 1, status: 1}}, 
    filter:{}
}
db.find('users', opt)

`SELECT * FROM users WHERE status = "A"`

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

`SELECT * FROM users WHERE status != "A"`

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

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

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

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

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

`SELECT * FROM users WHERE age > 25`

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

`SELECT * FROM users WHERE user_id like "bc%"`

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

`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 opt = {
    filter: { "status": "A" },
    useIndex: "name"
}
db.find('users', 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 opt = {
    filter: { "status": "A" },
    useIndex: "name",
    reverse: true
}
db.find('users', opt)

`SELECT COUNT(*) FROM users`

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

`SELECT COUNT(*) FROM users WHERE age > 30`

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

`SELECT * FROM users LIMIT 1`

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

`SELECT * FROM users LIMIT 5 SKIP 10`

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

Query example: A complete code example for a REST API and a NoSQL database query​

Match multiple fields​

Match sub fields​

Filtering data from the database​

Logical operators overview​

Conditional operators overview​

$not operator​

$in operator​

$nin operator​

$regex operator​

$or operator​

$and operator​

$gt operator​

$gte operator​

$lt operator​

$lte operator​

$exists operator​

$exists (sub array) operator​

$elemMatch operator​

$date operator​

SQL to NoSQL mapping​

SELECT * FROM users​

SELECT id, user_id, status FROM users​

SELECT * FROM users WHERE status = "A"​

SELECT * FROM users WHERE status != "A"​

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

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

SELECT * FROM users WHERE age > 25​

SELECT * FROM users WHERE user_id like "bc%"​

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

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

SELECT COUNT(*) FROM users​

SELECT COUNT(*) FROM users WHERE age > 30​

SELECT * FROM users LIMIT 1​

SELECT * FROM users LIMIT 5 SKIP 10​