MongoDB: Queries

    Although some wrote about my previous topic, MongoDB: Creating, updating and deleting documents , this is a retelling of offs. documentation, I do not completely agree with this. It seemed to me that the information in it turned out to be useful to someone, so I post the continuation.

    Find is an analogue of SELECT in MySQL. Used to fetch documents from MongoDB. Returns an array of documents as a collection; if there are no documents, an empty collection. Example:

    > db.users.find();

    Returns all users from the collection.

    > db.users.find( { age: 27 } );

    It will return all users whose age is 27.

    You can specify several parameters, for example, like this:

    > db.users.find( { username: “joe”, age: 27 } );

    Sometimes it is necessary to get some specific fields from documents. In this case, the request looks like this:

    > db.users.find( { }, { username: 1, email: 1 } );

    As a result, we will get all users only with the "username" and "email" fields + the "_id" field, which is always returned by default. If the "_id" field or some other field is not needed, we can specify it like this:

    > db.users.find( { }, { username: 1, email: 1, _id: 0 } );

    Requests with a condition



    Operators: $ lt - less, $ lte - less than or equal, $ gt - more, $ gte - more or equal, $ ne - not equal.
    Examples of use:
    We get all users whose age is more than 18 and less than 30

    > db.users.find( { age: { $gte: 18, $lte: 30 } } );

    We get all users whose username is not equal to “joe”

    > db.users.find( { username: { $ne: “joe” } } );

    Let's say our collection stores lottery tickets with numbers and we need to find only those that have winning numbers 390 , 754, 454. In this case, we use the operator $ in:

    > db.users.find( { numbers: { $in: [ 390, 754, 454 ] } } );

    i.e. The ticket must contain one of these numbers.
    The opposite of the $ in operator is $ nin. By analogy, he receives only those tickets where there are no numbers indicated above.
    The $ or operator is used for queries when you need to select documents by coincidence with one of the values. For example, we need to select all tickets with number 547 or where the “winner” field is true:

    > db.users.find( { $or: [ { tickect_number: 547 }, { winner: true } ] } );

    The $ mod operator is used to select keys whose values ​​are divided by the first argument and the remainder of the division is equal to the second argument. It sounds incomprehensible, but more clearly:

    > db.users.find( { user_id: { $mod: [ 5, 1 ] } } );

    Such a request will return all users whose user_id is 1, 6, 11, 16, and so on.
    To get all users except the above, you can use the $ not operator:

    > db.users.find( { user_id: { $not: { $mod: [ 5, 1 ] } } } );

    Get users with "user_id" 2, 3, 4, 5, 7, 8, 9, 10, 12 and so on.
    There is also an operator for checking whether a key exists or not - $ exist

    > db.c.find({"z" : {"$in" : [null], "$exists" : true}});

    So you can select all the documents in the collection where the key “z” exists and it is null.

    Regular expressions



    We all know that regular expressions are a very powerful thing. MongoDB uses Perl-compatible regular expressions.
    This is how you can find all users with the name joe or Joe:

    > db.users.find( { "name" : /^joe$/i } );

    In general, there is where to roam. There are a bunch of services for the same javascript, where you can write and check your regulars.

    Queries in Arrays



    Suppose we have a food collection and we insert a document with an array of fruits

    > db.food.insert( { "fruit" : [ "apple", "banana", "peach" ] } );

    there. Such a request will

    > db.food.find({"fruit" : "banana"});

    successfully find it.
    If you need to select documents for more than one element of the array, then we can use the $ all operator.

    > db.food.find( { fruits: { $all : [ "apple", "banana" ] } } );

    Such a query will return all documents in the fruit array of which there are apples and bananas.
    We can get documents by the complete coincidence of the elements in the array as follows:

    > db.food.find( { fruits: [ "apple", "banana", "peach" ] } );

    We have a blog, comments are stored in it. We want to get the first 10 comments. The $ slice operator comes to the rescue:

    > db.blog.posts.findOne( { }, { "comments" : { "$slice" : 10 } } );

    findOne - works similar to find, but returns the first matching document.

    If you need to get the last 10 comments, write like this:

    > db.blog.posts.findOne( { }, { "comments" : { "$slice" : -10 } } );

    Also $ slice can receive documents from the middle:

    > db.blog.posts.findOne( { }, { comments : { "$slice" : [ 23, 10 ] } });

    in this case, 23 initial elements will be skipped and elements 24 through 34 will be returned, if possible

    Limit, skip, and sort commands



    In order to get a limited number of documents on request, the limit command is used:

    > db.users.find().limit(3);

    Returns the first 3 users.
    In order to skip several documents and get all the rest, the skip command is used:

    > db.users.find().skip(3);

    We get all users except the first three.
    To sort, use the sort command. Sorting can be ascending (1) and descending (- 1). Sorting can be done by several keys in order of priority:

    > db.users.find().sort( { username : 1, age : -1 } );

    All these three commands can be used together:

    > db.stock.find( { "desc" : "mp3" } ).limit(50).skip(50).sort( { "price" : -1 } );

    Using the skip command with large values ​​does not work very quickly. Consider this using pagination as an example:

    The easiest way to do pagination is to return a fixed number of documents for the first time, and then shift the range each time to this value, but this will work rather slowly. Suppose we make a selection relative to the date the document was created: Sort in descending order and take the first 100. In order not to skip, we can get the date of the last document and make a request for this date: This way you can avoid using the skip command for large values. Essentially, these are the basic things about queries in MongoDB. I hope this is useful to someone.

    > var page1 = db.foo.find(criteria).limit(100)
    > var page2 = db.foo.find(criteria).skip(100).limit(100)
    > var page3 = db.foo.find(criteria).skip(200).limit(100)




    > var page1 = db.foo.find().sort( {"date" : -1} ).limit(100);



    var page2 = db.foo.find( { "date" : { "$gt" : latest.date } } );




    Also popular now: