Consultar dados no Azure Cosmos DB for MongoDB usando o JavaScript

APLICA-SE AO: MongoDB

Use consultas e pipelines de agregação para localizar e manipular documentos em uma coleção.

Documentação de referência da API for MongoDB | Pacote do MongoDB (npm)

Consulta de documentos

Para localizar documentos, use uma consulta para definir como os documentos são encontrados.

• MongoClient.Db.Collection.findOne
• MongoClient.Db.Collection.find
• FindCursor

// 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`)}`);

O snippet de código anterior exibe a seguinte saída de console de exemplo:

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

Pipelines de agregação

Os pipelines de agregação são úteis para isolar a computação de consulta cara, transformações e outros processamentos no servidor Azure Cosmos DB, em vez de executar essas operações no cliente.

Para obter suporte específico ao pipeline de agregação, consulte o seguinte:

• Versão 4.2
• Versão 4.0
• Versão 3.6
• Versão 3.2

Sintaxe de pipeline de agregação

Um pipeline é uma matriz com uma série de fases como objetos JSON.

const pipeline = [
    stage1,
    stage2
]

Sintaxe da fase do pipeline

Uma fase define a operação e os dados aos quais ela é aplicada, como:

• $match - localizar documentos
• $addFields - adicionar campo ao cursor, geralmente da fase anterior
• $limit - limitar o número de resultados retornados em um cursor
• $project - passar campos novos ou existentes, podem ser campos computados
• $group – agrupar resultados por um campo ou campos no pipeline
• $sort - classificar resultados

// reduce collection to relative documents
const matchStage = {
    '$match': {
        'categoryName': { $regex: 'Bikes' },
    }
}

// sort documents on field `name`
const sortStage = { 
    '$sort': { 
        "name": 1 
    } 
},

Agregar o pipeline para obter o cursor iterável

O pipeline é agregado para produzir um cursor iterável.

const db = 'adventureworks';
const collection = 'products';

const aggCursor = client.db(databaseName).collection(collectionName).aggregate(pipeline);

await aggCursor.forEach(product => {
    console.log(JSON.stringify(product));
});

Usar um pipeline de agregação no JavaScript

Use um pipeline para manter o processamento de dados no servidor antes de retornar ao cliente.

Exemplo de dados do produto

As agregações abaixo usam a coleção de produtos de exemplo com dados na forma de:

[
    {
        "_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": [
        ]
    },
]

Exemplo 1: subcategorias do produto, contagem de produtos e preço médio

Use o código de exemplo a seguir para relatar o preço médio em cada subcategoria do produto.

// 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 ...

Exemplo 2: tipos de bicicleta com intervalo de preço

Use o código de exemplo a seguir para relatar a subcategoria Bikes.

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

Veja também

• Introdução ao Azure Cosmos DB for MongoDB e ao JavaScript
• Criar um banco de dados