Azure Cosmos DB for MongoDB で JavaScript を使用してデータのクエリを実行する

適用対象: MongoDB

クエリと集計パイプラインを使用して、コレクション内のドキュメントを検索および操作します。

MongoDB 用 API リファレンス ドキュメント | MongoDB パッケージ (npm)

ドキュメントのクエリ

ドキュメントを検索するには、クエリを使ってドキュメントの検索方法を定義します。

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

// assume doc exists

const product = {
  _id: new ObjectId('62b1f43a9446918500c875c5'),
  category: 'gear-surf-surfboards',
  name: 'Yamba Surfboard 7',
  quantity: 12,
  sale: false,
};

// For unsharded database: use id
const query1 = { _id: new 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: new 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(
  `Next 5 docs:\n${nextFiveDocs.map(doc => `\t${doc._id}: ${doc.name}\n`)}`
);

上記のコード スニペットでは、次のようなコンソール出力例が表示されます。

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

集計パイプライン

集計パイプラインは、コストの高いクエリ計算、変換、その他の処理を Azure Cosmos DB サーバー上で分離し、これらの操作をクライアントで実行しないようにするために役立ちます。

特定の集計パイプラインのサポートについては、以下を参照してください。

• バージョン 4.2
• Version 4.0
• バージョン 3.6
• Version 3.2

集計パイプラインの構文

パイプラインは、一連のステージを表す JSON オブジェクトの配列です。

const pipeline = [
    stage1,
    stage2
]

パイプライン ステージの構文

"ステージ" は、操作とその適用対象のデータを次のように定義します。

• $match - ドキュメントを検索する
• $addFields - (通常は前のステージから) カーソルにフィールドを追加する
• $limit - カーソルで返される結果の数を制限する
• $project - 新規または既存のフィールド (計算フィールド可) を渡す
• $group - パイプライン内の 1 つまたは複数のフィールドで結果をグループ化する
• $sort - 結果を並べ替える

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

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

パイプラインを集計して反復可能なカーソルを取得する

パイプラインが集計され、反復可能なカーソルが生成されます。

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

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

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

JavaScript で集計パイプラインを使用する

パイプラインを使用して、クライアントに戻る前にサーバー上でデータ処理を続けます。

商品データの例

この集計では、次のような形式のデータを持つサンプル商品コレクションを使用します。

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

例 1: 商品サブカテゴリ、商品数、平均価格

次のサンプル コードを使用して、各商品サブカテゴリの平均価格をレポートします。

// 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
import 'dotenv/config';

// Use official mongodb driver to connect to the server
import { MongoClient } from 'mongodb';

// New instance of MongoClient with connection string
// for Cosmos DB
const url = process.env.COSMOS_CONNECTION_STRING;
const client = new MongoClient(url);

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

例 2: 自転車の種類と価格帯

次のサンプル コードを使用して、Bikes サブカテゴリについてレポートします。

// Goal: Find the price range for the different bike subcategories.

import dotenv from 'dotenv';
import path from 'path';
const __dirname = path.resolve();

dotenv.config({ path: path.resolve(__dirname, '../.env') });

// Use official mongodb driver to connect to the server
import { MongoClient } from 'mongodb';

// New instance of MongoClient with connection string
// for Cosmos DB
const url = process.env.COSMOS_CONNECTION_STRING;
const client = new MongoClient(url);

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

関連項目

• Azure Cosmos DB for MongoDB と JavaScript を使ってみる
• データベースの作成