Query data in Azure Cosmos DB for MongoDB using JavaScript
APPLIES TO:
MongoDB
Use queries and aggregation pipelines to find and manipulate documents in a collection.
Note
The example code snippets are available on GitHub as a JavaScript project.
API for MongoDB reference documentation | MongoDB Package (npm)
Query for documents
To find documents, use a query to define how the documents are found.
// assume doc exists
const product = {
_id: ObjectId("62b1f43a9446918500c875c5"),
category: "gear-surf-surfboards",
name: "Yamba Surfboard 7",
quantity: 12,
sale: false
};
// For unsharded database: use id
const query1 = { _id: ObjectId(product._id) };
const foundById = await client.db("adventureworks").collection('products').findOne(query1);
console.log(`Read doc:\t\n${Object.keys(foundById).map(key => `\t${key}: ${foundById[key]}\n`)}`);
// For sharded database: point read doc from collection using the id and partitionKey
const query2 = { _id: ObjectId(product._id), category: product.category };
const foundByIdAndPartitionKey = await client.db("adventureworks").collection('products').findOne(query2);
console.log(`Read doc 2:\t\n${Object.keys(foundByIdAndPartitionKey).map(key => `\t${key}: ${foundByIdAndPartitionKey[key]}\n`)}`);
// Find one by unique doc property value
const query3 = { name: product.name};
const foundByUniqueValue = await client.db("adventureworks").collection('products').findOne(query3);
console.log(`Read doc 3:\t\n${Object.keys(foundByUniqueValue).map(key => `\t${key}: ${foundByUniqueValue[key]}\n`)}`);
// Find one (with many that match query) still returns one doc
const query4 = { category: product.category };
const foundByNonUniqueValue = await client.db("adventureworks").collection('products').findOne(query4);
console.log(`Read doc 4:\t\n${Object.keys(foundByNonUniqueValue).map(key => `\t${key}: ${foundByNonUniqueValue[key]}\n`)}`);
// Find all that match query
const query5 = { category: product.category };
const foundAll = await client.db("adventureworks").collection('products').find(query5).sort({_id: 1}).toArray();
console.log(`Matching all in product category:\n${foundAll.map(doc => `\t${doc._id}: ${doc.name}\n`)}`);
// Find all in collection with empty query {}
const foundAll2 = await client.db("adventureworks").collection('products').find({}).toArray();
console.log(`All docs:\n${foundAll2.map(doc => `\t${doc._id}: ${doc.name}\n`)}`);
// Pagination - next 5 docs
// sort by name require an index on name
const nextFiveDocs = await client.db("adventureworks").collection('products').find({}).sort({name: 1}).skip(5).limit(5).toArray();
console.log(`All docs:\n${foundAll2.map(doc => `\t${doc._id}: ${doc.name}\n`)}`);
The preceding code snippet displays the following example console output:
Read doc:
_id: 62b1f43a9446918500c875c5
, name: Yamba Surfboard-13
, category: gear-surf-surfboards
, quantity: 20
, sale: false
, discontinued: true
Read doc 2:
_id: 62b1f43a9446918500c875c5
, name: Yamba Surfboard-13
, category: gear-surf-surfboards
, quantity: 20
, sale: false
, discontinued: true
Read doc 3:
_id: 62b23a371a09ed6441e5ee31
, category: gear-surf-surfboards
, name: Yamba Surfboard 7
, quantity: 5
, sale: true
, discontinued: true
Read doc 4:
_id: 62b1f43a9446918500c875c5
, name: Yamba Surfboard-13
, category: gear-surf-surfboards
, quantity: 20
, sale: false
, discontinued: true
Matching all in product category:
62b1f43a9446918500c875c5: Yamba Surfboard-13
, 62b1f4670c7af8c2942b7c10: Yamba Surfboard-3
, 62b1f46fa6546d2afb5715ac: Yamba Surfboard-90
, 62b1f474e4b43498c05d295b: Yamba Surfboard-9
All docs:
62b1f43a9446918500c875c5: Yamba Surfboard-13
, 62b1f4670c7af8c2942b7c10: Yamba Surfboard-3
, 62b1f46fa6546d2afb5715ac: Yamba Surfboard-90
, 62b1f474e4b43498c05d295b: Yamba Surfboard-9
, 62b1f47896aa8cfa280edf2d: Yamba Surfboard-55
, 62b1f47dacbf04e86c8abf25: Yamba Surfboard-11
, 62b1f4804ee53f4c5c44778c: Yamba Surfboard-97
, 62b1f492ff69395b30a03169: Yamba Surfboard-93
, 62b23a371a09ed6441e5ee30: Yamba Surfboard 3
, 62b23a371a09ed6441e5ee31: Yamba Surfboard 7
All docs:
62b1f43a9446918500c875c5: Yamba Surfboard-13
, 62b1f4670c7af8c2942b7c10: Yamba Surfboard-3
, 62b1f46fa6546d2afb5715ac: Yamba Surfboard-90
, 62b1f474e4b43498c05d295b: Yamba Surfboard-9
, 62b1f47896aa8cfa280edf2d: Yamba Surfboard-55
, 62b1f47dacbf04e86c8abf25: Yamba Surfboard-11
, 62b1f4804ee53f4c5c44778c: Yamba Surfboard-97
, 62b1f492ff69395b30a03169: Yamba Surfboard-93
, 62b23a371a09ed6441e5ee30: Yamba Surfboard 3
, 62b23a371a09ed6441e5ee31: Yamba Surfboard 7
done
Aggregation pipelines
Aggregation pipelines are useful to isolate expensive query computation, transformations, and other processing on your Azure Cosmos DB server, instead of performing these operations on the client.
For specific aggregation pipeline support, refer to the following:
Aggregation pipeline syntax
A pipeline is an array with a series of stages as JSON objects.
const pipeline = [
stage1,
stage2
]
Pipeline stage syntax
A stage defines the operation and the data it's applied to, such as:
- $match - find documents
- $addFields - add field to cursor, usually from previous stage
- $limit - limit the number of results returned in cursor
- $project - pass along new or existing fields, can be computed fields
- $group - group results by a field or fields in pipeline
- $sort - sort results
// reduce collection to relative documents
const matchStage = {
'$match': {
'categoryName': { $regex: 'Bikes' },
}
}
// sort documents on field `name`
const sortStage = {
'$sort': {
"name": 1
}
},
Aggregate the pipeline to get iterable cursor
The pipeline is aggregated to produce an iterable cursor.
const db = 'adventureworks';
const collection = 'products';
const aggCursor = client.db(databaseName).collection(collectionName).aggregate(pipeline);
await aggCursor.forEach(product => {
console.log(JSON.stringify(product));
});
Use an aggregation pipeline in JavaScript
Use a pipeline to keep data processing on the server before returning to the client.
Example product data
The aggregations below use the sample products collection with data in the shape of:
[
{
"_id": "08225A9E-F2B3-4FA3-AB08-8C70ADD6C3C2",
"categoryId": "75BF1ACB-168D-469C-9AA3-1FD26BB4EA4C",
"categoryName": "Bikes, Touring Bikes",
"sku": "BK-T79U-50",
"name": "Touring-1000 Blue, 50",
"description": "The product called \"Touring-1000 Blue, 50\"",
"price": 2384.0700000000002,
"tags": [
]
},
{
"_id": "0F124781-C991-48A9-ACF2-249771D44029",
"categoryId": "56400CF3-446D-4C3F-B9B2-68286DA3BB99",
"categoryName": "Bikes, Mountain Bikes",
"sku": "BK-M68B-42",
"name": "Mountain-200 Black, 42",
"description": "The product called \"Mountain-200 Black, 42\"",
"price": 2294.9899999999998,
"tags": [
]
},
{
"_id": "3FE1A99E-DE14-4D11-B635-F5D39258A0B9",
"categoryId": "26C74104-40BC-4541-8EF5-9892F7F03D72",
"categoryName": "Components, Saddles",
"sku": "SE-T924",
"name": "HL Touring Seat/Saddle",
"description": "The product called \"HL Touring Seat/Saddle\"",
"price": 52.640000000000001,
"tags": [
]
},
]
Example 1: Product subcategories, count of products, and average price
Use the following sample code to report on average price in each product subcategory.
// Goal: Find the average price of each product subcategory with
// the number of products in that subcategory.
// Sort by average price descending.
// Read .env file and set environment variables
require('dotenv').config();
// Use official mongodb driver to connect to the server
const { MongoClient } = require('mongodb');
// New instance of MongoClient with connection string
// for Cosmos DB
const url = process.env.COSMOS_CONNECTION_STRING;
const client = new MongoClient(url);
async function main() {
try {
// Use connect method to connect to the server
await client.connect();
// Group all products by category
// Find average price of each category
// Count # of products in each category
const groupByCategory = {
'$group': {
'_id': '$categoryName',
'averagePrice': {
'$avg': '$price'
},
'countOfProducts': {
'$sum': 1
}
},
};
// Round price to 2 decimal places
// Don't return _id
// Rename category name help in `_id` to `categoryName`
// Round prices to 2 decimal places
// Rename property for countOfProducts to nProducts
const additionalTransformations = {
'$project': {
'_id': 0,
'category': '$_id',
'nProducts':'$countOfProducts',
'averagePrice': { '$round': ['$averagePrice', 2] }
}
};
// Sort by average price descending
const sort = { '$sort': { '$averagePrice': -1 } };
// stages execute in order from top to bottom
const pipeline = [
groupByCategory,
additionalTransformations,
sort
];
const db = 'adventureworks';
const collection = 'products';
// Get iterable cursor
const aggCursor = client.db(db).collection(collection).aggregate(pipeline);
// Display each item in cursor
await aggCursor.forEach(product => {
console.log(JSON.stringify(product));
});
return 'done';
} catch (err) {
console.log(JSON.stringify(err));
}
}
main()
.then(console.log)
.catch(console.error)
.finally(() => {
// Close the db and its underlying connections
client.close()
});
// Results:
// {"averagePrice":51.99,"category":"Clothing, Jerseys","nProducts":8}
// {"averagePrice":1683.36,"category":"Bikes, Mountain Bikes","nProducts":32}
// {"averagePrice":1597.45,"category":"Bikes, Road Bikes","nProducts":43}
// {"averagePrice":20.24,"category":"Components, Chains","nProducts":1}
// {"averagePrice":25,"category":"Accessories, Locks","nProducts":1}
// {"averagePrice":631.42,"category":"Components, Touring Frames","nProducts":18}
// {"averagePrice":9.25,"category":"Clothing, Socks","nProducts":4}
// {"averagePrice":125,"category":"Accessories, Panniers","nProducts":1}
// ... remaining fields ...
Example 2: Bike types with price range
Use the following sample code to report on the Bikes subcategory.
// Goal: Find the price range for the different bike subcategories.
// Read .env file and set environment variables
require('dotenv').config();
// Use official mongodb driver to connect to the server
const { MongoClient } = require('mongodb');
// New instance of MongoClient with connection string
// for Cosmos DB
const url = process.env.COSMOS_CONNECTION_STRING;
const client = new MongoClient(url);
async function main() {
try {
// Use connect method to connect to the server
await client.connect();
const categoryName = 'Bikes';
const findAllBikes = {
'$match': {
'categoryName': { $regex: categoryName},
}
};
// Convert 'Bikes, Touring Bikes' to ['Bikes', 'Touring Bikes']
const splitStringIntoCsvArray = {
$addFields: {
'categories': { '$split': ['$categoryName', ', '] }
}
};
// Remove first element from array
// Converts ['Bikes', 'Touring Bikes'] to ['Touring Bikes']
const removeFirstElement = {
$addFields: {
'subcategory': { '$slice': ['$categories', 1, { $subtract: [{ $size: '$categories' }, 1] }] }
}
}
// Group items by book subcategory, and find min, avg, and max price
const groupBySubcategory = {
'$group': {
'_id': '$subcategory',
'maxPrice': {
'$max': '$price'
},
'averagePrice': {
'$avg': '$price'
},
'minPrice': {
'$min': '$price'
},
'countOfProducts': {
'$sum': 1
}
},
};
// Miscellaneous transformations
// Don't return _id
// Convert subcategory from array of 1 item to string in `name`
// Round prices to 2 decimal places
// Rename property for countOfProducts to nProducts
const additionalTransformations = {
'$project': {
'_id': 0,
'name': { '$arrayElemAt': ['$_id', 0]},
'nProducts': '$countOfProducts',
'min': { '$round': ['$minPrice', 2] },
'avg': { '$round': ['$averagePrice', 2] },
'max': { '$round': ['$maxPrice', 2] }
}
};
// Sort by subcategory
const sortBySubcategory = { '$sort':
{ 'name': 1 }
};
// stages execute in order from top to bottom
const pipeline = [
findAllBikes,
splitStringIntoCsvArray,
removeFirstElement,
groupBySubcategory,
additionalTransformations,
sortBySubcategory
];
const db = 'adventureworks';
const collection = 'products';
// Get iterable cursor
const aggCursor = client.db(db).collection(collection).aggregate(pipeline);
// Display each item in cursor
await aggCursor.forEach(product => {
console.log(JSON.stringify(product));
});
return 'done';
} catch (err) {
console.log(JSON.stringify(err));
}
}
main()
.then(console.log)
.catch(console.error)
.finally(() => {
// Close the db and its underlying connections
client.close();
});
// Results:
// {'name':'Mountain Bikes','nProducts':32,'min':539.99,'avg':1683.37,'max':3399.99}
// {'name':'Road Bikes','nProducts':43,'min':539.99,'avg':1597.45,'max':3578.27}
// {'name':'Touring Bikes','nProducts':22,'min':742.35,'avg':1425.25,'max':2384.07}