1MongoDB::Examples(3)  User Contributed Perl Documentation MongoDB::Examples(3)
2
3
4

NAME

6       MongoDB::Examples - Some examples of MongoDB syntax
7

VERSION

9       version v2.2.2
10

MAPPING SQL TO MONGODB

12       For developers familiar with SQL, the following chart should help you
13       see how many common SQL queries could be expressed in MongoDB.
14
15       These are Perl-specific examples of translating SQL queries to
16       MongoDB's query language.  To see the mappings for JavaScript (or
17       another language), see
18       <http://docs.mongodb.org/manual/reference/sql-comparison/>.
19
20       In the following examples, $db is a MongoDB::Database object which was
21       retrieved by using "get_database". See MongoDB::MongoClient,
22       MongoDB::Database and MongoDB::Collection for more on the methods you
23       see below.
24
25       "CREATE TABLE USERS (a Number, b Number)"
26               Implicit, can be done explicitly.
27
28       "INSERT INTO USERS VALUES(1,1)"
29               $db->get_collection( 'users' )->insert_one( { a => 1, b => 1 } );
30
31       "SELECT a,b FROM users"
32               $db->get_collection( 'users')->find( { } )->fields( { a => 1, b => 1 });
33
34       "SELECT * FROM users"
35               $db->get_collection( 'users' )->find;
36
37       "SELECT * FROM users WHERE age=33"
38               $db->get_collection( 'users' )->find( { age => 33 } )
39
40       "SELECT a,b FROM users WHERE age=33"
41               $db->get_collection( 'users' )->find( { age => 33 } )->fields( { a => 1, b => 1 });
42
43       "SELECT * FROM users WHERE age=33 ORDER BY name"
44               $db->get_collection( 'users' )->find( { age => 33 } )->sort( { name => 1 } );
45
46       "SELECT * FROM users WHERE age>33"
47               $db->get_collection( 'users' )->find( { age => { '$gt' => 33 } } );
48
49       "SELECT * FROM users WHERE age<33"
50               $db->get_collection( 'users' )->find( { age => { '$lt' => 33 } } );
51
52       "SELECT * FROM users WHERE name LIKE "%Joe%""
53               $db->get_collection( 'users' )->find( { name => qr/Joe/ } );
54
55       "SELECT * FROM users WHERE name LIKE "Joe%""
56               $db->get_collection( 'users' )->find( {name => qr/^Joe/ } );
57
58       "SELECT * FROM users WHERE age>33 AND age<=40"
59               $db->get_collection( 'users' )->find( { age => { '$gt' => 33, '$lte' => 40 } } );
60
61       "SELECT * FROM users ORDER BY name DESC"
62               $db->get_collection( 'users' )->find->sort( { name => -1 } );
63
64       "CREATE INDEX myindexname ON users(name)"
65               my $indexes = $db->get_collection( 'users' )->indexes;
66               $indexes->create_one( [ name => 1 ] );
67
68       "CREATE INDEX myindexname ON users(name,ts DESC)"
69               my $indexes = $db->get_collection( 'users' )->indexes;
70               $indexes->create_one( [ name => 1, ts => -1 ] );
71
72       "SELECT * FROM users WHERE a=1 and b='q'"
73               $db->get_collection( 'users' )->find( {a => 1, b => "q" } );
74
75       "SELECT * FROM users LIMIT 10 SKIP 20"
76               $db->get_collection( 'users' )->find->limit(10)->skip(20);
77
78       "SELECT * FROM users WHERE a=1 or b=2"
79               $db->get_collection( 'users' )->find( { '$or' => [ {a => 1 }, { b => 2 } ] } );
80
81       "SELECT * FROM users LIMIT 1"
82               $db->get_collection( 'users' )->find->limit(1);
83
84       "EXPLAIN SELECT * FROM users WHERE z=3"
85               $db->get_collection( 'users' )->find( { z => 3 } )->explain;
86
87       "SELECT DISTINCT last_name FROM users"
88               $db->get_collection( 'users' )->distinct( 'last_name' );
89
90       "SELECT COUNT(*y) FROM users"
91               $db->get_collection( 'users' )->count_documents;
92
93       "SELECT COUNT(*y) FROM users where age > 30"
94               $db->get_collection( 'users' )->count_documents( { "age" => { '$gt' => 30 } } );
95
96       "SELECT COUNT(age) from users"
97               $db->get_collection( 'users' )->count_documents( { age => { '$exists' => 1 } } );
98
99       "UPDATE users SET a=1 WHERE b='q'"
100               $db->get_collection( 'users' )->update_many( { b => "q" }, { '$set' => { a => 1 } } );
101
102       "UPDATE users SET a=a+2 WHERE b='q'"
103               $db->get_collection( 'users' )->update_many( { b => "q" }, { '$inc' => { a => 2 } } );
104
105       "DELETE FROM users WHERE z="abc""
106               $db->get_database( 'users' )->delete_many( { z => "abc" } );
107

DATABASE COMMANDS

109       If you do something in the MongoDB shell and you would like to
110       translate it to Perl, the best way is to run the function in the shell
111       without parentheses, which will print the source.  You can then
112       generally translate the source into Perl fairly easily.
113
114       For example, suppose we want to use "db.foo.validate" in Perl.  We
115       could run:
116
117           > db.foo.validate
118           function (full) {
119               var cmd = {validate:this.getName()};
120               if (typeof full == "object") {
121                   Object.extend(cmd, full);
122               } else {
123                   cmd.full = full;
124               }
125               var res = this._db.runCommand(cmd);
126               if (typeof res.valid == "undefined") {
127                   res.valid = false;
128                   var raw = res.result || res.raw;
129                   if (raw) {
130                       var str = "-" + tojson(raw);
131                       res.valid = !(str.match(/exception/) || str.match(/corrupt/));
132                       var p = /lastExtentSize:(\d+)/;
133                       var r = p.exec(str);
134                       if (r) {
135                           res.lastExtentSize = Number(r[1]);
136                       }
137                   }
138               }
139               return res;
140           }
141
142       Next, we can translate the important parts into Perl:
143
144           $db->run_command( [ validate => "foo" ] );
145
146   Find-one-and-modify
147       The find-one-and-modify commands in MongoDB::Collection are similar to
148       update (or remove), but will return the modified document.  They can be
149       useful for implementing queues or locks.
150
151       For example, suppose we had a list of things to do, and we wanted to
152       remove the highest-priority item for processing.  We could do a find
153       and then a delete_one, but that wouldn't be atomic (a write could occur
154       between the query and the remove).  Instead, we could use
155       find_one_and_delete:
156
157           my $coll = $db->get_collection('todo');
158           my $next_task = $todo->find_one_and_delete(
159               {}, # empty filter means any document
160               { sort => {priority => -1} },
161           );
162
163       This will atomically find and pop the next-highest-priority task.
164
165       See <http://www.mongodb.org/display/DOCS/findAndModify+Command> for
166       more details on find-and-modify.
167

AGGREGATION

169       The aggregation framework is MongoDB's analogy for SQL GROUP BY
170       queries, but more generic and more powerful. An invocation of the
171       aggregation framework specifies a series of stages in a pipeline to be
172       executed in order by the server. Each stage of the pipeline is drawn
173       from one of the following so-called "pipeline operators": $project,
174       $match, $limit, $skip, $unwind, $group, $sort, and $geoNear.
175
176       The aggregation framework is the preferred way of performing most
177       aggregation tasks. New in version 2.2, it has largely obviated
178       mapReduce
179       <http://docs.mongodb.org/manual/reference/command/mapReduce/#dbcmd.mapReduce>,
180       and group
181       <http://docs.mongodb.org/manual/reference/command/group/#dbcmd.group>.
182
183       See the MongoDB aggregation framework documentation for more
184       information (<http://docs.mongodb.org/manual/aggregation/>).
185
186   $match and $group
187       The $group pipeline operator is used like GROUP BY in SQL. For example,
188       suppose we have a number of local businesses stored in a "business"
189       collection.  If we wanted to find the number of coffeeshops in each
190       neighborhood, we could do:
191
192           my $out = $db->get_collection('business')->aggregate(
193               [
194                   {'$match' => {'type' => 'coffeeshop'}},
195                   {'$group' => {'_id' => '$neighborhood', 'num_coffeshops' => {'$sum' => 1}}}
196               ]
197           );
198
199       The SQL equivalent is "SELECT neighborhood, COUNT(*) FROM business
200       GROUP BY neighborhood WHERE type = 'coffeeshop'".  After executing the
201       above aggregation query, $out will contain a MongoDB::QueryResult,
202       allowing us to iterate through result documents such as the following:
203
204           (
205                {
206                    '_id' => 'Soho',
207                    'num_coffeshops' => 23
208                },
209                {
210                    '_id' => 'Chinatown',
211                    'num_coffeshops' => 14
212                },
213                {
214                    '_id' => 'Upper East Side',
215                    'num_coffeshops' => 10
216                },
217                {
218                    '_id' => 'East Village',
219                    'num_coffeshops' => 87
220                }
221           )
222
223       Note that aggregate takes an array reference as an argument. Each
224       element of the array is document which specifies a stage in the
225       aggregation pipeline. Here our aggregation query consists of a $match
226       phase followed by a $group phase. Use $match to filter the documents in
227       the collection prior to aggregation. The "_id" field in the $group
228       stage specifies the key to group by; the "$" in '$neighborhood'
229       indicates that we are referencing the name of a key. Finally, we use
230       the $sum operator to add one for every document in a particular
231       neighborhood.  There are other operators, such as $avg, $max, $min,
232       $push, and $addToSet, which can be used in the $group phase and work
233       much like $sum.
234
235   $project and $unwind
236       Now let's look at a more complex example of the aggregation framework
237       that makes use of the $project and $unwind pipeline operators. Suppose
238       we have a collection called 'courses' which contains information on
239       college courses. An example document in the collection looks like this:
240
241           {
242               '_id' => 'CSCI0170',
243               'name' => 'Computer Science 17',
244               'description' => 'An Integrated Introduction to Computer Science',
245               'instructor_id' => 29823498,
246               'instructor_name' => 'A. Greenwald',
247               'students' => [
248                   { 'student_id' => 91736114, 'student_name' => 'D. Storch' },
249                   { 'student_id' => 89100891, 'student_name' => 'J. Rassi' }
250               ]
251           }
252
253       We wish to generate a report containing one document per student that
254       indicates the courses in which each student is enrolled. The following
255       call to "aggregate" will do the trick:
256
257           my $out = $db->get_collection('courses')->aggregate([
258               {'$unwind' => '$students'},
259               {'$project' => {
260                       '_id' => 0,
261                       'course' => '$_id',
262                       'student_id' => '$students.student_id',
263                   }
264               },
265               {'$group' => {
266                       '_id' => '$student_id',
267                       'courses' => {'$addToSet' => '$course'}
268                   }
269               }
270           ]);
271
272       The output documents will each have a student ID number and an array of
273       the courses in which that student is enrolled:
274
275           (
276               {
277                   '_id' => 91736114,
278                   'courses' => ['CSCI0170', 'CSCI0220', 'APMA1650', 'HIST1230']
279               },
280               {
281                   '_id' => 89100891,
282                   'courses' => ['CSCI0170', 'CSCI1670', 'CSCI1690']
283               }
284           )
285
286       The $unwind stage of the aggregation query "peels off" elements of the
287       courses array one-by-one and places them in their own documents. After
288       this phase completes, there is a separate document for each (course,
289       student) pair. The $project stage then throws out unnecessary fields
290       and keeps the ones we are interested in. It also pulls the student ID
291       field out of its subdocument and creates a top-level field with the key
292       "student_id". Last, we group by student ID, using $addToSet in order to
293       add the unique courses for each student to the "courses" array.
294
295   $sort, $skip, and $limit
296       The $sort, $skip, and $limit pipeline operators work much like their
297       companion methods in MongoDB::Cursor. Returning to the previous
298       students and courses example, suppose that we were particularly
299       interested in the student with the ID that is numerically third-to-
300       highest. We could retrieve the course list for that student by adding
301       $sort, $skip, and $limit phases to the pipeline:
302
303           my $out = $db->get_collection('courses')->aggregate([
304               {'$unwind' => '$students'},
305               {'$project' => {
306                       '_id' => 0,
307                       'course' => '$_id',
308                       'student_id' => '$students.student_id',
309                   }
310               },
311               {'$group' => {
312                       '_id' => '$student_id',
313                       'courses' => {'$addToSet' => '$course'}
314                   }
315               },
316               {'$sort' => {'_id' => -1}},
317               {'$skip' => 2},
318               {'$limit' => 1}
319           ]);
320

QUERYING

322   Nested Fields
323       MongoDB allows you to store deeply nested structures and then query for
324       fields within them using dot-notation.  For example, suppose we have a
325       users collection with documents that look like:
326
327           {
328               "userId" => 12345,
329               "address" => {
330                   "street" => "123 Main St",
331                   "city" => "Springfield",
332                   "state" => "MN",
333                   "zip" => "43213"
334               }
335           }
336
337       If we want to query for all users from Springfield, we can do:
338
339           my $cursor = $users->find({"address.city" => "Springfield"});
340
341       This will search documents for an "address" field that is a subdocument
342       and a "city" field within the subdocument.
343

UPDATING

345   Positional Operator
346       In MongoDB 1.3.4 and later, you can use positional operator, "$", to
347       update elements of an array.  For instance, suppose you have an array
348       of user information and you want to update a user's name.
349
350       A sample document in JavaScript:
351
352           {
353               "users" : [
354                   {
355                       "name" : "bill",
356                       "age" : 60
357                   },
358                   {
359                       "name" : "fred",
360                       "age" : 29
361                   },
362               ]
363           }
364
365       The update:
366
367           $coll->update_one({"users.name" => "fred"}, {'users.$.name' => "george"});
368
369       This will update the array so that the element containing "name" =>
370       "fred" now has "name" => "george".
371

AUTHORS

373       •   David Golden <david@mongodb.com>
374
375       •   Rassi <rassi@mongodb.com>
376
377       •   Mike Friedman <friedo@friedo.com>
378
379       •   Kristina Chodorow <k.chodorow@gmail.com>
380
381       •   Florian Ragwitz <rafl@debian.org>
382
384       This software is Copyright (c) 2020 by MongoDB, Inc.
385
386       This is free software, licensed under:
387
388         The Apache License, Version 2.0, January 2004
389
390
391
392perl v5.32.1                      2021-01-27              MongoDB::Examples(3)
Impressum