MongoDBのクエリの備忘録 (Comparison, Logical)

準備

Docker ComposeでMongoDBを起動する。

% docker compose up -d

compose.yml

services:
  mongo:
    image: mongo:6.0
    restart: always
    ports:
      - 27017:27017
    environment:
      MONGO_INITDB_ROOT_USERNAME: user1
      MONGO_INITDB_ROOT_PASSWORD: xxxx

MongoDB Shellで接続する。

% mongosh "mongodb://localhost:27017" --username user1
Enter password: ****
Current Mongosh Log ID: 633acb1ef03fa39a9f8e3ec1
Connecting to:      mongodb://<credentials>@localhost:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+1.6.0
Using MongoDB:      6.0.2
Using Mongosh:      1.6.0

For mongosh info see: https://docs.mongodb.com/mongodb-shell/

test>

テストデータを挿入する。

test> db.product.insertMany([
... {name: "product1", price: 100},
... {name: "product2", price: 200},
... {name: "product3", price: 300},
... {name: "product4", price: 400},
... {name: "product5", price: 500}
... ])
{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId("633acbf5f03fa39a9f8e3ec2"),
    '1': ObjectId("633acbf5f03fa39a9f8e3ec3"),
    '2': ObjectId("633acbf5f03fa39a9f8e3ec4"),
    '3': ObjectId("633acbf5f03fa39a9f8e3ec5"),
    '4': ObjectId("633acbf5f03fa39a9f8e3ec6")
  }
}

Comparison Query Operators

eq (equal)

price = 300

test> db.product.find({price: {$eq: 300}})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec4"),
    name: 'product3',
    price: 300
  }
]

ne (not equal)

price <> 300

test> db.product.find({price: {$ne: 300}})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec2"),
    name: 'product1',
    price: 100
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec3"),
    name: 'product2',
    price: 200
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec5"),
    name: 'product4',
    price: 400
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec6"),
    name: 'product5',
    price: 500
  }
]

gt (greater than)

price > 300

test> db.product.find({price: {$gt: 300}})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec5"),
    name: 'product4',
    price: 400
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec6"),
    name: 'product5',
    price: 500
  }
]

gte (greater than or equal)

price >= 300

test>  db.product.find({price: {$gte: 300}})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec4"),
    name: 'product3',
    price: 300
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec5"),
    name: 'product4',
    price: 400
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec6"),
    name: 'product5',
    price: 500
  }
]

lt (less than)

price < 300

test> db.product.find({price: {$lt: 300}})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec2"),
    name: 'product1',
    price: 100
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec3"),
    name: 'product2',
    price: 200
  }
]

lte (less than or equal)

price <= 300

test> db.product.find({price: {$lte: 300}})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec2"),
    name: 'product1',
    price: 100
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec3"),
    name: 'product2',
    price: 200
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec4"),
    name: 'product3',
    price: 300
  }
]

in

price in (200, 300)

test> db.product.find({price: {$in: [200,300]}})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec3"),
    name: 'product2',
    price: 200
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec4"),
    name: 'product3',
    price: 300
  }
]

nin

price not in (200, 300)

test> db.product.find({price: {$nin: [200,300]}})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec2"),
    name: 'product1',
    price: 100
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec5"),
    name: 'product4',
    price: 400
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec6"),
    name: 'product5',
    price: 500
  }
]

Logical Query Operators

and

price >= 300 and price <= 400

test> db.product.find({$and: [{price: {$gte: 300}}, {price: {$lte: 400}}]})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec4"),
    name: 'product3',
    price: 300
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec5"),
    name: 'product4',
    price: 400
  }
]

or

price = 300 or price = 400

test> db.product.find({$or: [{price: {$eq: 300}}, {price: {$eq: 400}}]})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec4"),
    name: 'product3',
    price: 300
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec5"),
    name: 'product4',
    price: 400
  }
]

nor

not (price = 300 or price = 400)

test> db.product.find({$nor: [{price: {$eq: 300}}, {price: {$eq: 400}}]})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec2"),
    name: 'product1',
    price: 100
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec3"),
    name: 'product2',
    price: 200
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec6"),
    name: 'product5',
    price: 500
  }
]

not

not price >= 300

test> db.product.find({price: {$not: {$gte: 300}}})
[
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec2"),
    name: 'product1',
    price: 100
  },
  {
    _id: ObjectId("633acbf5f03fa39a9f8e3ec3"),
    name: 'product2',
    price: 200
  }
]

my memo