Consulta de datos en Azure Cosmos DB for MongoDB mediante JavaScript

  • Artículo

SE APLICA A: MongoDB

Use consultas y canalizaciones de agregación para buscar y manipular documentos de una colección.

Nota:

Los fragmentos de código de ejemplo están disponibles en GitHub como un proyecto de JavaScript.

Documentación de referencia de API para MongoDB | paquete de MongoDB (npm)

Consulta de documentos

Para buscar documentos, use una consulta para definir cómo se encuentran los documentos.

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

El fragmento de código anterior muestra la salida de la consola de ejemplo siguiente:

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

Canalizaciones de agregación

Las canalizaciones de agregación son útiles para aislar costosos cálculos de consulta, transformaciones y otros procesamientos en el servidor de Azure Cosmos DB, en lugar de realizar estas operaciones en el cliente.

Para compatibilidad específica con la canalización de agregación, consulte lo siguiente:

Sintaxis de canalización de agregación

Una canalización es una matriz con una serie de fases como objetos JSON.

const pipeline = [
    stage1,
    stage2
]

Sintaxis de fase de canalización

Una fase define la operación y los datos a los que se aplica, como:

  • $match: buscar documentos
  • $addFields: agregar campo al cursor, normalmente desde la fase anterior
  • $limit: limitar del número de resultados devueltos en el cursor
  • $project: pasar campos nuevos o existentes, pueden ser campos calculados
  • $group: agrupar los resultados por uno o varios campos en la canalización
  • $sort: ordenar los resultados
// reduce collection to relative documents
const matchStage = {
    '$match': {
        'categoryName': { $regex: 'Bikes' },
    }
}

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

Incorporación de la canalización para obtener un cursor iterable

La canalización se agrega para generar un cursor iterable.

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

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

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

Uso de una canalización de agregación en JavaScript

Use una canalización para mantener el procesamiento de datos en el servidor antes de volver al cliente.

Datos de producto de ejemplo

Las agregaciones siguientes usan la colección de productos de ejemplo con datos en 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": [
        ]
    },
]

Ejemplo 1: subcategorías de productos, recuento de productos y precio medio

Use el código de ejemplo siguiente para informar sobre el precio medio en cada subcategoría de producto.

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

Ejemplo 2: tipos de bicicletas con intervalo de precios

Use el código de ejemplo siguiente para informar sobre la subcategoría 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}

Consulta también