1MongoDB::Examples(3) User Contributed Perl Documentation MongoDB::Examples(3)
2
6 MongoDB::Examples - Some examples of MongoDB syntax
7
9 version v2.0.3
10
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 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 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 "CREATE TABLE USERS (a Number, b Number)"
26 Implicit, can be done explicitly.
27 "INSERT INTO USERS VALUES(1,1)"
29 $db->get_collection( 'users' )->insert_one( { a => 1, b => 1 } );
30 "SELECT a,b FROM users"
32 $db->get_collection( 'users')->find( { } )->fields( { a => 1, b => 1 });
33 "SELECT * FROM users"
35 $db->get_collection( 'users' )->find;
36 "SELECT * FROM users WHERE age=33"
38 $db->get_collection( 'users' )->find( { age => 33 } )
39 "SELECT a,b FROM users WHERE age=33"
41 $db->get_collection( 'users' )->find( { age => 33 } )->fields( { a => 1, b => 1 });
42 "SELECT * FROM users WHERE age=33 ORDER BY name"
44 $db->get_collection( 'users' )->find( { age => 33 } )->sort( { name => 1 } );
45 "SELECT * FROM users WHERE age>33"
47 $db->get_collection( 'users' )->find( { age => { '$gt' => 33 } } );
48 "SELECT * FROM users WHERE age<33"
50 $db->get_collection( 'users' )->find( { age => { '$lt' => 33 } } );
51 "SELECT * FROM users WHERE name LIKE "%Joe%""
53 $db->get_collection( 'users' )->find( { name => qr/Joe/ } );
54 "SELECT * FROM users WHERE name LIKE "Joe%""
56 $db->get_collection( 'users' )->find( {name => qr/^Joe/ } );
57 "SELECT * FROM users WHERE age>33 AND age<=40"
59 $db->get_collection( 'users' )->find( { age => { '$gt' => 33, '$lte' => 40 } } );
60 "SELECT * FROM users ORDER BY name DESC"
62 $db->get_collection( 'users' )->find->sort( { name => -1 } );
63 "CREATE INDEX myindexname ON users(name)"
65 my $indexes = $db->get_collection( 'users' )->indexes;
66 $indexes->create_one( [ name => 1 ] );
67 "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 "SELECT * FROM users WHERE a=1 and b='q'"
73 $db->get_collection( 'users' )->find( {a => 1, b => "q" } );
74 "SELECT * FROM users LIMIT 10 SKIP 20"
76 $db->get_collection( 'users' )->find->limit(10)->skip(20);
77 "SELECT * FROM users WHERE a=1 or b=2"
79 $db->get_collection( 'users' )->find( { '$or' => [ {a => 1 }, { b => 2 } ] } );
80 "SELECT * FROM users LIMIT 1"
82 $db->get_collection( 'users' )->find->limit(1);
83 "EXPLAIN SELECT * FROM users WHERE z=3"
85 $db->get_collection( 'users' )->find( { z => 3 } )->explain;
86 "SELECT DISTINCT last_name FROM users"
88 $db->get_collection( 'users' )->distinct( 'last_name' );
89 "SELECT COUNT(*y) FROM users"
91 $db->get_collection( 'users' )->count_documents;
92 "SELECT COUNT(*y) FROM users where age > 30"
94 $db->get_collection( 'users' )->count_documents( { "age" => { '$gt' => 30 } } );
95 "SELECT COUNT(age) from users"
97 $db->get_collection( 'users' )->count_documents( { age => { '$exists' => 1 } } );
98 "UPDATE users SET a=1 WHERE b='q'"
100 $db->get_collection( 'users' )->update_many( { b => "q" }, { '$set' => { a => 1 } } );
101 "UPDATE users SET a=a+2 WHERE b='q'"
103 $db->get_collection( 'users' )->update_many( { b => "q" }, { '$inc' => { a => 2 } } );
104 "DELETE FROM users WHERE z="abc""
106 $db->get_database( 'users' )->delete_many( { z => "abc" } );
107
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 For example, suppose we want to use "db.foo.validate" in Perl. We
115 could run:
116 > 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 Next, we can translate the important parts into Perl:
143 $db->run_command( [ validate => "foo" ] );
145 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 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 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 This will atomically find and pop the next-highest-priority task.
164 See <http://www.mongodb.org/display/DOCS/findAndModify+Command> for
166 more details on find-and-modify.
167
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 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 See the MongoDB aggregation framework documentation for more
184 information (<http://docs.mongodb.org/manual/aggregation/>).
185 $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 my $out = $db->get_collection('business')->aggregate(
193 [
194 {'$match' => {'type' => 'coffeeshop'}},
195 {'$group' => {'_id' => '$neighborhood', 'num_coffeshops' => {'$sum' => 1}}}
196 ]
197 );
198 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 (
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 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 $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 {
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 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 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 The output documents will each have a student ID number and an array of
273 the courses in which that student is enrolled:
274 (
276 {
277 '_id' => 91736114,
278 'courses' => ['CSCI0170', 'CSCI0220', 'APMA1650', 'HIST1230']
279 },
280 {
281 '_id' => 89100891,
282 'courses' => ['CSCI0170', 'CSCI1670', 'CSCI1690']
283 }
284 )
285 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 $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 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
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 {
328 "userId" => 12345,
329 "address" => {
330 "street" => "123 Main St",
331 "city" => "Springfield",
332 "state" => "MN",
333 "zip" => "43213"
334 }
335 }
336 If we want to query for all users from Springfield, we can do:
338 my $cursor = $users->find({"address.city" => "Springfield"});
340 This will search documents for an "address" field that is a subdocument
342 and a "city" field within the subdocument.
343
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 A sample document in JavaScript:
351 {
353 "users" : [
354 {
355 "name" : "bill",
356 "age" : 60
357 },
358 {
359 "name" : "fred",
360 "age" : 29
361 },
362 ]
363 }
364 The update:
366 $coll->update_one({"users.name" => "fred"}, {'users.$.name' => "george"});
368 This will update the array so that the element containing "name" =>
370 "fred" now has "name" => "george".
371
373 · David Golden <david@mongodb.com>
374 · Rassi <rassi@mongodb.com>
376 · Mike Friedman <friedo@friedo.com>
378 · Kristina Chodorow <k.chodorow@gmail.com>
380 · Florian Ragwitz <rafl@debian.org>
382
384 This software is Copyright (c) 2019 by MongoDB, Inc.
385 This is free software, licensed under:
387 The Apache License, Version 2.0, January 2004
389perl v5.28.1 2019-02-07 MongoDB::Examples(3)