About Constructing queries in MongoDB

DB架构

已于 2022-04-05 07:15:08 修改

阅读量292

点赞数

分类专栏： MongoDB海洋航线文章标签： mongodb ruby

于 2022-04-02 13:55:55 首次发布

本文链接：https://blog.csdn.net/u011868279/article/details/123761417

版权

MongoDB海洋航线专栏收录该内容

21 篇文章 0 订阅

订阅专栏

Querying an e-commerce data model
The MongoDB query language in detail
Query selectors and options

Products,categories, and reviews

> use products
switched to db products
> show tables;
orders
products
> product = db.products.findOne({"slug" : "wheelbarrow-9092"})
{
"_id" : ObjectId("4c4b1476238d3b4dd5003981"),
"slug" : "wheelbarrow-9092",
"sku" : "9092",
"name" : "Extra Large Wheelbarrow",
"description" : "Heavy duty wheelbarrow...",
"details" : {
"weight" : 47,
"weight_units" : "lbs",
"model_num" : 4039283402,
"manufacturer" : "Acme",
"color" : "Green"
},
"total_reviews" : 4,
"average_review" : 4.5,
"pricing" : {
"retail" : 589700,
"sale" : 489700
},
"price_history" : [
{
"retail" : 529700,
"sale" : 429700,
"start" : ISODate("2010-04-30T16:00:00Z"),
"end" : ISODate("2010-05-07T16:00:00Z")
},
{
"retail" : 529700,
"sale" : 529700,
"start" : ISODate("2010-05-08T16:00:00Z"),
"end" : ISODate("2010-05-15T16:00:00Z")
}
],
"primary_category" : ObjectId("6a5b1476238d3b4dd5000048"),
"category_ids" : [
ObjectId("6a5b1476238d3b4dd5000048"),
ObjectId("6a5b1476238d3b4dd5000049")
],
"main_cat_id" : ObjectId("6a5b1476238d3b4dd5000048"),
"tags" : [
"tools",
"gardening",
"soil"
]
}
>

> use categories
switched to db categories
> db.categories.findOne({"_id": product["main_cat_id"]})
{
"_id" : ObjectId("6a5b1476238d3b4dd5000048"),
"slug" : "gardening-tools",
"name" : "Gardening Tools",
"description" : "Gardening gadgets galore!",
"parent_id" : ObjectId("55804822812cb336b78728f9"),
"ancestors" : [
{
"name" : "Home",
"_id" : ObjectId("558048f0812cb336b78728fa"),
"slug" : "home"
},
{
"name" : "Outdoors",
"_id" : ObjectId("55804822812cb336b78728f9"),
"slug" : "outdoors"
}
]
}

FindOne vs. FIND Queries

find return a cursor object, whereas findOne return a document

> use products
switched to db products
> db.products.find({"slug" : "wheelbarrow-9092"}).limit(1)
{ "_id" : ObjectId("4c4b1476238d3b4dd5003981"), "slug" : "wheelbarrow-9092", "sku" : "9092", "name" : "Extra Large Wheelbarrow", "description" : "Heavy duty wheelbarrow...", "details" : { "weight" : 47, "weight_units" : "lbs", "model_num" : 4039283402, "manufacturer" : "Acme", "color" : "Green" }, "total_reviews" : 4, "average_review" : 4.5, "pricing" : { "retail" : 589700, "sale" : 489700 }, "price_history" : [ { "retail" : 529700, "sale" : 429700, "start" : ISODate("2010-04-30T16:00:00Z"), "end" : ISODate("2010-05-07T16:00:00Z") }, { "retail" : 529700, "sale" : 529700, "start" : ISODate("2010-05-08T16:00:00Z"), "end" : ISODate("2010-05-15T16:00:00Z") } ], "primary_category" : ObjectId("6a5b1476238d3b4dd5000048"), "category_ids" : [ ObjectId("6a5b1476238d3b4dd5000048"), ObjectId("6a5b1476238d3b4dd5000049") ], "main_cat_id" : ObjectId("6a5b1476238d3b4dd5000048"), "tags" : [ "tools", "gardening", "soil" ] }
>

if you're expecting a single document,findOne will return that document if it exists. if you need to return multiple documents.use find.You'll then need to iterate over that cursor somewhere in your application.

SKIP,LIMIT,AND SORT QUERY OPTIONS

> db.reviews.find({'product_id': product['_id']}).skip(0).limit(12)

> page_number = 1
1
> product = db.products.findOne({"slug" : "wheelbarrow-9092"})
null
> use products
switched to db products
> product = db.products.findOne({"slug" : "wheelbarrow-9092"})
{
"_id" : ObjectId("4c4b1476238d3b4dd5003981"),
"slug" : "wheelbarrow-9092",
"sku" : "9092",
"name" : "Extra Large Wheelbarrow",
"description" : "Heavy duty wheelbarrow...",
"details" : {
"weight" : 47,
"weight_units" : "lbs",
"model_num" : 4039283402,
"manufacturer" : "Acme",
"color" : "Green"
},
"total_reviews" : 4,
"average_review" : 4.5,
"pricing" : {
"retail" : 589700,
"sale" : 489700
},
"price_history" : [
{
"retail" : 529700,
"sale" : 429700,
"start" : ISODate("2010-04-30T16:00:00Z"),
"end" : ISODate("2010-05-07T16:00:00Z")
},
{
"retail" : 529700,
"sale" : 529700,
"start" : ISODate("2010-05-08T16:00:00Z"),
"end" : ISODate("2010-05-15T16:00:00Z")
}
],
"primary_category" : ObjectId("6a5b1476238d3b4dd5000048"),
"category_ids" : [
ObjectId("6a5b1476238d3b4dd5000048"),
ObjectId("6a5b1476238d3b4dd5000049")
],
"main_cat_id" : ObjectId("6a5b1476238d3b4dd5000048"),
"tags" : [
"tools",
"gardening",
"soil"
]
}
> use categories
switched to db categories
> category = db.categories.findOne({"_id": product["main_cat_id"]})
{
"_id" : ObjectId("6a5b1476238d3b4dd5000048"),
"slug" : "gardening-tools",
"name" : "Gardening Tools",
"description" : "Gardening gadgets galore!",
"parent_id" : ObjectId("55804822812cb336b78728f9"),
"ancestors" : [
{
"name" : "Home",
"_id" : ObjectId("558048f0812cb336b78728fa"),
"slug" : "home"
},
{
"name" : "Outdoors",
"_id" : ObjectId("55804822812cb336b78728f9"),
"slug" : "outdoors"
}
]
}
> use reviews
switched to db reviews
> reviews_count = db.reviews.count({"product_id": product["_id"]})
0
> reviews_count = db.reviews.count({'product_id': product['_id']})
0
> reviews = db.reviews.find({'product_id': product['_id']}).
... skip((page_number - 1) * 12).
... limit(12).
... sort({'helpful_votes': -1})
>

Product Listing page

> show tables;
categories
> category = db.categories.findOne({"slug": "gardening-tools"})
{
"_id" : ObjectId("6a5b1476238d3b4dd5000048"),
"slug" : "gardening-tools",
"name" : "Gardening Tools",
"description" : "Gardening gadgets galore!",
"parent_id" : ObjectId("55804822812cb336b78728f9"),
"ancestors" : [
{
"name" : "Home",
"_id" : ObjectId("558048f0812cb336b78728fa"),
"slug" : "home"
},
{
"name" : "Outdoors",
"_id" : ObjectId("55804822812cb336b78728f9"),
"slug" : "outdoors"
}
]
}
> siblings = db.categories.find({"parent_id": category["_id"]})
> products = db.products.find({'category_id': category['_id']})
> products = db.products.find({'category_id': category['_id']}).skip((page_number - 1) * 12).limit(12).sort({'helpful_votes': -1})
> categories = db.categories.find({'parent_id': null})
>

Users and orders

> db.users.findOne({"username": "kbanker","hashed_password": "bd1cfa194c3a603e7186780824b04419"})
null

FindOne VS. FIND QUERIES

find returns a cursor object.

findOne returns a document

> db.products.find({"slug" : "wheelbarrow-9092"}).pretty()
{
        "_id" : ObjectId("4c4b1476238d3b4dd5003981"),
        "slug" : "wheelbarrow-9092",
        "sku" : "9092",
        "name" : "Extra Large Wheelbarrow",
        "description" : "Heavy duty wheelbarrow...",
        "details" : {
                "weight" : 47,
                "weight_units" : "lbs",
                "model_num" : 4039283402,
                "manufacturer" : "Acme",
                "color" : "Green"
        },
        "total_reviews" : 4,
        "average_review" : 4.5,
        "pricing" : {
                "retail" : 589700,
                "sale" : 489700
        },
        "price_history" : [
                {
                        "retail" : 529700,
                        "sale" : 429700,
                        "start" : ISODate("2010-04-30T16:00:00Z"),
                        "end" : ISODate("2010-05-07T16:00:00Z")
                },
                {
                        "retail" : 529700,
                        "sale" : 529700,
                        "start" : ISODate("2010-05-08T16:00:00Z"),
                        "end" : ISODate("2010-05-15T16:00:00Z")
                }
        ],
        "primary_category" : ObjectId("6a5b1476238d3b4dd5000048"),
        "category_ids" : [
                ObjectId("6a5b1476238d3b4dd5000048"),
                ObjectId("6a5b1476238d3b4dd5000049")
        ],
        "main_cat_id" : ObjectId("6a5b1476238d3b4dd5000048"),
        "tags" : [
                "tools",
                "gardening",
                "soil"
        ]
}
>

SKIP,LIMIT,AND SORT QUERY OPTIONS

> page_number = 1
1
> product = db.products.findOne({"slug" : "wheelbarrow-9092"})
{
        "_id" : ObjectId("4c4b1476238d3b4dd5003981"),
        "slug" : "wheelbarrow-9092",
        "sku" : "9092",
        "name" : "Extra Large Wheelbarrow",
        "description" : "Heavy duty wheelbarrow...",
        "details" : {
                "weight" : 47,
                "weight_units" : "lbs",
                "model_num" : 4039283402,
                "manufacturer" : "Acme",
                "color" : "Green"
        },
        "total_reviews" : 4,
        "average_review" : 4.5,
        "pricing" : {
                "retail" : 589700,
                "sale" : 489700
        },
        "price_history" : [
                {
                        "retail" : 529700,
                        "sale" : 429700,
                        "start" : ISODate("2010-04-30T16:00:00Z"),
                        "end" : ISODate("2010-05-07T16:00:00Z")
                },
                {
                        "retail" : 529700,
                        "sale" : 529700,
                        "start" : ISODate("2010-05-08T16:00:00Z"),
                        "end" : ISODate("2010-05-15T16:00:00Z")
                }
        ],
        "primary_category" : ObjectId("6a5b1476238d3b4dd5000048"),
        "category_ids" : [
                ObjectId("6a5b1476238d3b4dd5000048"),
                ObjectId("6a5b1476238d3b4dd5000049")
        ],
        "main_cat_id" : ObjectId("6a5b1476238d3b4dd5000048"),
        "tags" : [
                "tools",
                "gardening",
                "soil"
        ]
}
> category = db.categories.findOne({'_id': product['main_cat_id']})
null
> use categories
switched to db categories
> category = db.categories.findOne({'_id': product['main_cat_id']})
{
        "_id" : ObjectId("6a5b1476238d3b4dd5000048"),
        "slug" : "gardening-tools",
        "name" : "Gardening Tools",
        "description" : "Gardening gadgets galore!",
        "parent_id" : ObjectId("55804822812cb336b78728f9"),
        "ancestors" : [
                {
                        "name" : "Home",
                        "_id" : ObjectId("558048f0812cb336b78728fa"),
                        "slug" : "home"
                },
                {
                        "name" : "Outdoors",
                        "_id" : ObjectId("55804822812cb336b78728f9"),
                        "slug" : "outdoors"
                }
        ]
}
> reviews_count = db.reviews.count({'product_id': product['_id']})
0
> reviews = db.reviews.find({'product_id': product['_id']}).
... skip((page_number - 1) * 12).
... limit(12).
... sort({'helpful_votes': -1})

MongoDB's query language

Select Matching

db.users.find({'last_name': "Banker"})

db.users.find({'first_name': "Smith", birth_year: 1975})

Ranges

$lt less than

$gt greater than

$lte less than or equal

$gte greater than or equal

db.users.find({'birth_year': {'$gte': 1985}, 'birth_year': {'$lte': 2015}})

db.users.find({'birth_year': {'$gte': 1985, '$lte': 2015}})

db.items.find({'value': {'$gte': 97}})

db.items.find({'value': {'$gte': "a"}})

Set Operator:

db.products.find({ 'main_cat_id': { '$in': [ ObjectId("6a5b1476238d3b4dd5000048"), ObjectId("6a5b1476238d3b4dd5000051"), ObjectId("6a5b1476238d3b4dd5000057") ] } })

$in is frequently used with lists of IDs.

$nin (not in)) returns a document only when none of the given elements matches. You might use $nin to find all products that are neither black nor blue:

db.products.find({'details.color': {'$nin': ["black", "blue"]}})

$all matches if every given element matches the search key. If you wanted to find all products tagged as gift and garden, $all would be a good choice:

db.products.find({'tags': {'$all': ["gift", "garden"]}})

Boolean Operators

db.products.find({'details.manufacturer': 'Acme', tags: {$ne: "gardening"} })

db.users.find({'age': {'$not': {'$lte': 30}}})

db.products.find({'details.color': {$in: ['blue', 'Green']}})

db.products.find({ '$or': [ {'details.color': 'blue'}, {'details.manufacturer': 'Acme'} ] })

db.products.find({ $and: [ { tags: {$in: ['gift', 'holiday']} }, { tags: {$in: ['gardening', 'landscaping']} } ] })

QUERY FOR A DOCUMENT WITH A SPECIFIC KEY

db.products.find({'details.color': {$exists: false}})

db.products.find({'details.color': {$exists: true}})

Arrays

QUERYING FOR AN ARRAY BY SIZE

db.users.find({'addresses': {$size: 3}})

JAVASCRIPT QUERY OPERATORS

$where Execute some arbitrary JavaScript to select a document

db.reviews.find({ '$where': "function() { return this.helpful_votes > 3; }" })

db.reviews.find({'$where': "this.helpful_votes > 3"})

Regular Expressions

$regex Match the element against the supplied regex term

db.reviews.find({ 'user_id': ObjectId("4c4b1476238d3b4dd5000001"), 'text': { '$regex': "best|worst", '$options': "i"} })

MISCELLANEOUS QUERY OPERATORS

DB架构

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
About Constructing queries in MongoDB

Querying an e-commerce data model The MongoDB query language in detail Query selectors and optionsProducts,categories, and reviews> use productsswitched to db products> show tables;ordersproducts> product = db.products.findOne({"slug" :
复制链接

扫一扫