Docs Menu
Docs Home
/ / /
Laravel MongoDB

Query Builder

On this page

  • Overview
  • Before You Get Started
  • Retrieve Matching Documents
  • Where Method Example
  • Logical Conditional Operations
  • Ranges and Type Checks
  • Text Pattern Match Example
  • Retrieve Distinct Values
  • Aggregations
  • Results Grouped by Common Field Values Example
  • Number of Results Example
  • Maximum Value of a Field Example
  • Minimum Value of a Field Example
  • Average Value of a Field Example
  • Summed Value of a Field Example
  • Aggregate Matched Results Example
  • Modify Query Results
  • Order Results Example
  • Omit a Specified Number of Results Example
  • Show a Subset of Fields and Array Values in the Results Example
  • Paginate the Results Example
  • Retrieve Data by Using MongoDB Operations
  • Contains a Field Example
  • Contains All Fields Example
  • Match Array Size Example
  • Match Data Type Example
  • Match a Value Computed with Modulo Example
  • Match a Regular Expression
  • Run MongoDB Query API Operations Example
  • Match Array Elements Example
  • Specify Cursor Timeout Example
  • Match Locations by Using Geospatial Operations
  • Near a Position Example
  • Within an Area Example
  • Intersecting a Geometry Example
  • Proximity Data for Nearby Matches Example
  • Write Data by Using MongoDB Write Operations
  • Upsert a Document Example
  • Increment a Numerical Value Example
  • Decrement a Numerical Value Example
  • Add an Array Element Example
  • Remove an Array Element Example
  • Remove a Field Example

In this guide, you can learn how to use the Laravel Integration extension of the Laravel query builder to work with a MongoDB database. The query builder lets you use a single syntax and fluent interface to write queries for any supported database.

Note

The Laravel Integration extends Laravel's query builder and Eloquent ORM, which can run similar database operations. To learn more about retrieving documents by using Eloquent models, see Read Operations.

Laravel provides a facade to access the query builder class DB, which lets you perform database operations. Facades, which are static interfaces to classes, make the syntax more concise, avoid runtime errors, and improve testability.

The Laravel Integration provides the DB method table() to access a collection. Chain methods to specify commands and any constraints. Then, chain the get() method at the end to run the methods and retrieve the results. To retrieve only the first matching result, chain the first() method instead of the get() method. Starting in Laravel MongoDB v5.0, the query builder returns results as stdClass objects.

The following example shows the syntax of a query builder call:

DB::table('<collection name>')
// chain methods by using the "->" object operator
->get();

Tip

Set Database Connection

Before using the DB::table() method, ensure that you specify MongoDB as your application's default database connection. For instructions on setting the database connection, see the Configure Your MongoDB Connection step in the Quick Start.

If MongoDB is not your application's default database, you can use the DB::connection() method to specify a MongoDB connection. Pass the name of the connection to the connection() method, as shown in the following code:

$connection = DB::connection('mongodb');

This guide provides examples of the following types of query builder operations:

  • Retrieve Matching Documents

  • Modify Query Results

  • Retrieve Data by Using MongoDB Operations

  • Write Data by Using MongoDB Write Operations

To run the code examples in this guide, complete the Quick Start tutorial to configure a web application, load sample datasets into your MongoDB deployment, and run the example code from a controller method. To see the expected code output as JSON documents, use the toJson() method shown in the optional View your results as JSON documents step of the Quick Start.

To perform read and write operations by using the query builder, import the Illuminate\Support\Facades\DB facade and compose your query.

This section includes query builder examples for read operations in the following operator categories:

The following example shows how to use the where() query builder method to retrieve documents from the movies collection that contain an imdb.rating field value of exactly 9.3. Click the VIEW OUTPUT button to see the results returned by the query:

$result = DB::connection('mongodb')
->table('movies')
->where('imdb.rating', 9.3)
->get();
[
{ "title": "Cosmos",
"year": 1980,
"runtime": 60,
"imdb": {
"rating": 9.3,
"votes": 17174,
"id": 81846
},
"plot": "Astronomer Carl Sagan leads us on an engaging guided tour of the various elements and cosmological theories of the universe.",
...
},
{ "title": "The Shawshank Redemption",
"year": 1994,
"runtime": 142,
"imdb": {
"rating": 9.3,
"votes": 1521105,
"id": 111161
},
"plot": "Two imprisoned men bond over a number of years, finding solace and eventual redemption through acts of common decency.",
...
},
{ "title": "The Real Miyagi",
"year": 2015,
"runtime": 90,
"imdb": {
"rating": 9.3,
"votes": 41,
"id": 2313306
},
"plot": "The life of the greatest karate master of a generation.",
...
}
]

The examples in this section show the query builder syntax you can use to perform the following logical conditional operations:

The following example shows how to chain the orWhere() query builder method to retrieve documents from the movies collection in which the value of the _id field is ObjectId('573a1398f29313caabce9682') or the value of the title field is "Back to the Future":

$result = DB::connection('mongodb')
->table('movies')
->where('id', new ObjectId('573a1398f29313caabce9682'))
->orWhere('title', 'Back to the Future')
->get();

Note

You can use the id alias in your queries to represent the _id field in MongoDB documents, as shown in the preceding code. When you use the query builder to run a find operation, the Laravel Integration automatically converts between _id and id field names. In query results, the _id field is presented as id. This provides better consistency with Laravel, as the framework assumes that each record has a primary key named id by default.

Because of this behavior, you cannot have two separate id and _id fields in your documents.

The following example shows how to chain the where() query builder method to retrieve documents from the movies collection that match both an imdb.rating value greater than 8.5 and a year value of less than 1940:

$result = DB::connection('mongodb')
->table('movies')
->where('imdb.rating', '>', 8.5)
->where('year', '<', 1940)
->get();

Tip

For compatibility with Laravel, Laravel MongoDB v5.1 supports both arrow (->) and dot (.) notation to access nested fields in a query filter. The preceding example uses dot notation to query the imdb.rating nested field, which is the recommended syntax.

The following example shows how to call the whereNot() query builder method to retrieve documents from the movies collection that match documents that do not have an imdb.rating value greater than 2. This is equivalent to matching all documents that have an imdb.rating of less than or equal to 2:

$result = DB::connection('mongodb')
->table('movies')
->whereNot('imdb.rating', '>', 2)
->get();

The following example shows how to chain the where() query builder method to retrieve documents from the movies collection that match both of the following conditions. This example passes a closure as the first parameter of the where() query builder method to group the logical OR group:

  • imdb.rating value is greater than 8.5

  • year value is either 1986 or 1996

$result = DB::connection('mongodb')
->table('movies')
->where('imdb.rating', '>', 8.5)
->where(function (Builder $query) {
return $query
->where('year', 1986)
->orWhere('year', 1996);
})->get();

The examples in this section show the query builder syntax you can use to match values by using the following range queries and type check operations:

The following example shows how to use the whereBetween() query builder method to retrieve documents from the movies collection that contain an imdb.rating value between 9 and 9.5:

$result = DB::connection('mongodb')
->table('movies')
->whereBetween('imdb.rating', [9, 9.5])
->get();
[
{ "title" "The Godfather", "imdb": { "rating": 9.2, "votes": 1038358, "id": 68646 }, ... },
{ "title": "Hollywood", "imdb": { "rating": 9.1, "votes": 511,"id": 80230 }, ... },
{ "title": "Cosmos", "imdb": { "rating": 9.3, "votes": 17174, "id": 81846 }, ... },
...
]

The following example shows how to use the whereNull() query builder method to retrieve documents from the movies collection that omit a runtime value or field:

$result = DB::connection('mongodb')
->table('movies')
->whereNull('runtime')
->get();

The following example shows how to use the whereIn() query builder method to retrieve documents from the movies collection that match at least one of the title values in the specified set:

$result = DB::table('movies')
->whereIn('title', ['Toy Story', 'Shrek 2', 'Johnny English'])
->get();
[
{ "title": "Toy Story", "year": 1995, "runtime": 81, ... },
{ "title": "Johnny English", "year": 2003, "runtime": 87, ... },
{ "title": "Shrek 2", "year" 2004, "runtime": 93, ... },
...
]

The following example shows how to use the where() query builder method to retrieve documents from the movies collection in which the released value is January 15, 2010, specified in a Carbon object:

$result = DB::connection('mongodb')
->table('movies')
->where('released', Carbon::create(2010, 1, 15))
->get();

Note

Date Query Filter and Result Type

Starting in Laravel MongoDB v5.0, Carbon objects passed as query filters, as shown in the preceding code, are converted to UTCDateTime BSON values.

In query results, UTCDateTime BSON values in MongoDB are returned as Carbon objects. The Laravel Integration applies the default timezone when performing this conversion.

If you want to represent a date as a string in your query filter rather than as a Carbon object, use the whereDate() query builder method. The following example retrieves documents from the movies collection in which the released value is January 15, 2010 and specifies the date as a string:

$result = DB::connection('mongodb')
->table('movies')
->whereDate('released', '2010-1-15')
->get();

The following example shows how to use the like query operator with the where() query builder method to retrieve documents from the movies collection by using a specified text pattern.

Text patterns can contain text mixed with the following wildcard characters:

  • % which matches zero or more characters

  • _ which matches a single character

$result = DB::table('movies')
->where('title', 'like', '%spider_man%')
->get();
[
{ "title": "Kiss of the Spider Woman", ... },
{ "title": "Spider-Man", ... },
{ "title": "Spider-Man 2", ...},
...
]

The following methods provide the same functionality as using the like query operator to match patterns:

  • whereLike(): Matches a specified pattern. By default, this method performs a case-insensitive match. You can enable case-sensitivity by passing true as the last parameter to the method.

  • whereNotLike(): Matches documents in which the field value does not contain the specified string pattern.

The following example shows how to use the whereLike() method to match documents in which the title field has a value that matches the pattern 'Start%' with case-sensitivity enabled:

$result = DB::connection('mongodb')
->table('movies')
->whereLike('title', 'Start%', true)
->get();
[
{ "title": "Start-Up", ... },
{ "title": "Start the Revolution Without Me", ... },
...
]

The following example shows how to use the distinct() query builder method to retrieve all the different values of the year field for documents in the movies collections.

$result = DB::table('movies')
->distinct('year')->get();

The examples in this section show the query builder syntax you can use to perform aggregations. Aggregations are operations that compute values from a set of query result data. You can use aggregations to compute and return the following information:

The following example shows how to use the groupBy() query builder method to retrieve document data grouped by shared values of the runtime field. This example chains the following operations to match documents from the movies collection that contain a rated value of G and include the title field of one movie for each distinct runtime value:

  • Match only documents that contain a rated field value of "G" by using the where() method

  • Group data by the distinct values of the runtime field, which is assigned the _id field, by using the groupBy() method

  • Sort the groups by the runtime field by using the orderBy() method

  • Return title data from the last document in the grouped result by specifying it in the get() method

Tip

The groupBy() method calls the MongoDB $group aggregation operator and $last accumulator operator. To learn more about these operators, see $group (aggregation) in the Server manual.

$result = DB::table('movies')
->where('rated', 'G')
->groupBy('runtime')
->orderBy('runtime', 'asc')
->get(['title']);
[
...
{
"_id": { "runtime": 64 },
"runtime": 64,
"title": "Stitch! The Movie"
},
{
"_id": { "runtime": 67 },
"runtime": 67,
"title": "Bartok the Magnificent"
},
{
"_id": { "runtime":68 },
"runtime": 68,
"title": "Mickey's Twice Upon a Christmas"
},
...
]

The following example shows how to use the count() query builder method to return the number of documents contained in the movies collection:

$result = DB::table('movies')
->count();

The following example shows how to use the max() query builder method to return the highest numerical value of the runtime field from the entire movies collection:

$result = DB::table('movies')
->max('runtime');

The following example shows how to use the min() query builder method to return the lowest numerical value of the year field from the entire movies collection:

$result = DB::table('movies')
->min('year');

The following example shows how to use the avg() query builder method to return the numerical average, or arithmetic mean, of the imdb.rating values from the entire movies collection.

$result = DB::table('movies')
->avg('imdb.rating');

The following example shows how to use the sum() query builder method to return the numerical total of the imdb.votes values from the entire movies collection:

$result = DB::table('movies')
->sum('imdb.votes');

The following example shows how to aggregate data from results that match a query. The query matches all movies after the year 2000 and computes the average value of imdb.rating of those matches by using the avg() method:

$result = DB::table('movies')
->where('year', '>', 2000)
->avg('imdb.rating');

This section includes query builder examples for the following functions that modify the order and format of query results:

The following example shows how to use the orderBy() query builder method to arrange the results that match the filter specified in the title field by the imdb.rating value in descending order:

$result = DB::table('movies')
->where('title', 'like', 'back to the future%')
->orderBy('imdb.rating', 'desc')
->get();
[
{ "title": "Back to the Future", "imdb": { "rating":8.5,"votes":636511,"id":88763 }, ... },
{ "title": "Back to the Future Part II", "imdb": { "rating":7.8,"votes":292539,"id":96874 }, ... },
{ "title": "Back to the Future Part III", "imdb": {"rating":7.4,"votes":242390,"id":99088 }, ... },
...
]

The following example shows how to use the skip() query builder method to omit the first four results that match the filter specified in the title field, sorted by the year value in ascending order:

$result = DB::table('movies')
->where('title', 'like', 'star trek%')
->orderBy('year', 'asc')
->skip(4)
->get();

The following example shows how to use the project() query builder method to match documents that contain an imdb.rating value higher than 8.5 and return only the following field values:

  • Title of the movie in the title

  • Second through fourth values of the cast array field, if they exist

  • Document _id field, which is automatically included

$result = DB::table('movies')
->where('imdb.rating', '>', 8.5)
->project([
'title' => 1,
'cast' => ['$slice' => [1, 3]],
])
->get();
[
{
"_id": { ... },
"title": "City Lights"
"cast": [
"Florence Lee",
"Harry Myers",
"Al Ernest Garcia"
],
},
{
"_id": { ... },
"title": "Modern Times",
"cast": [
"Paulette Goddard",
"Henry Bergman",
"Tiny Sandford"
]
},
{
"_id": { ... },
"title": "Casablanca"
"cast": [
"Ingrid Bergman",
"Paul Henreid",
"Claude Rains"
],
},
...
]

The following example shows how to use the paginate() query builder method to divide the entire movie collection into discrete result sets of 15 documents. The example also includes a sort order to arrange the results by the imdb.votes field in descending order and a projection that includes only specific fields in the results.

$resultsPerPage = 15;
$projectionFields = ['title', 'runtime', 'imdb.rating'];
$result = DB::table('movies')
->orderBy('imdb.votes', 'desc')
->paginate($resultsPerPage, $projectionFields);

To learn more about pagination, see Paginating Query Builder Results in the Laravel documentation.

This section includes query builder examples that show how to use the following MongoDB-specific query operations:

The following example shows how to use the exists() query builder method to match documents that contain the field random_review:

$result = DB::table('movies')
->exists('random_review', true);

To learn more about this query operator, see $exists in the Server manual.

The following example shows how to use the all query operator with the where() query builder method to match documents that contain all the specified fields:

$result = DB::table('movies')
->where('movies', 'all', ['title', 'rated', 'imdb.rating'])
->get();

To learn more about this query operator, see $all in the Server manual.

The following example shows how to pass the size query operator with the where() query builder method to match documents that contain a directors field that contains an array of exactly five elements:

$result = DB::table('movies')
->where('directors', 'size', 5)
->get();

To learn more about this query operator, see $size in the Server manual.

The following example shows how to pass the type query operator with the where() query builder method to match documents that contain a type 4 value, which corresponds to an array data type, in the released field.

$result = DB::table('movies')
->where('released', 'type', 4)
->get();

To learn more about the type codes and query operator, see $type in the Server manual.

The following example shows how to pass the mod query operator with the where() query builder method to match documents by using the expression year % 2 == 0, which matches even values for the year field:

$result = DB::table('movies')
->where('year', 'mod', [2, 0])
->get();

To learn more about this query operator, see $mod in the Server manual.

The following example shows how to pass the REGEX query operator with the where() query builder method to match documents that contain a title field that matches the specified regular expression:

$result = DB::connection('mongodb')
->table('movies')
->where('title', 'REGEX', new Regex('^the lord of .*', 'i'))
->get();

To learn more about regular expression queries in MongoDB, see $regex in the Server manual.

The following example shows how to use the whereRaw() query builder method to run a query operation written by using the MongoDB Query API syntax:

$result = DB::table('movies')
->whereRaw([
'imdb.votes' => ['$gte' => 1000 ],
'$or' => [
['imdb.rating' => ['$gt' => 7]],
['directors' => ['$in' => [ 'Yasujiro Ozu', 'Sofia Coppola', 'Federico Fellini' ]]],
],
])->get();

The following code shows the equivalent MongoDB Query API syntax:

db.movies.find({
"imdb.votes": { $gte: 1000 },
$or: [{
imdb.rating: { $gt: 7 },
directors: { $in: [ "Yasujiro Ozu", "Sofia Coppola", "Federico Fellini" ] }
}]});

To learn more about the MongoDB Query API, see MongoDB Query API in the Server manual.

The following example shows how to pass the elemMatch query operator with the where() query builder method to match documents that contain an array element that matches at least one of the conditions in the specified query:

$result = DB::table('movies')
->where('writers', 'elemMatch', ['$in' => ['Maya Forbes', 'Eric Roth']])
->get();

To learn more about regular expression queries in MongoDB, see the $elemMatch operator in the Server manual.

The following example shows how to use the timeout() method to specify a maximum duration to wait for cursor operations to complete.

$result = DB::table('movies')
->timeout(2) // value in seconds
->where('year', 2001)
->get();

Note

This setting specifies a maxTimeMS value in seconds instead of milliseconds. To learn more about the maxTimeMS value, see MongoDBCollection::find() in the PHP Library documentation.

The examples in this section show the query builder syntax you can use to perform geospatial queries on GeoJSON or coordinate pair data to retrieve the following types of locations:

Important

To perform GeoJSON queries in MongoDB, you must create either 2d or 2dsphere index on the collection. To learn how to create geospatial indexes, see the Create a Geospatial Index section in the Schema Builder guide.

To learn more about GeoJSON objects that MongoDB supports, see GeoJSON Objects in the Server manual.

The following example shows how to use the near query operator with the where() query builder method to match documents that contain a location that is up to 50 meters from a GeoJSON Point object:

$results = DB::table('theaters')
->where('location.geo', 'near', [
'$geometry' => [
'type' => 'Point',
'coordinates' => [
-86.6423,
33.6054,
],
],
'$maxDistance' => 50,
])->get();

To learn more about this operator, see $near operator in the Server manual.

The following example shows how to use the geoWithin query operator with the where() query builder method to match documents that contain a location within the bounds of the specified Polygon GeoJSON object:

$results = DB::table('theaters')
->where('location.geo', 'geoWithin', [
'$geometry' => [
'type' => 'Polygon',
'coordinates' => [
[
[-72, 40],
[-74, 41],
[-72, 39],
[-72, 40],
],
],
],
])->get();

The following example shows how to use the geoInstersects query operator with the where() query builder method to match documents that contain a location that intersects with the specified LineString GeoJSON object:

$results = DB::table('theaters')
->where('location.geo', 'geoIntersects', [
'$geometry' => [
'type' => 'LineString',
'coordinates' => [
[-73.600525, 40.74416],
[-72.600525, 40.74416],
],
],
])->get();

The following example shows how to use the geoNear aggregation operator with the raw() query builder method to perform an aggregation that returns metadata, such as proximity information for each match:

$results = DB::table('theaters')->raw(
function (Collection $collection) {
return $collection->aggregate([
[
'$geoNear' => [
'near' => [
'type' => 'Point',
'coordinates' => [-118.34, 34.10],
],
'distanceField' => 'dist.calculated',
'maxDistance' => 500,
'includeLocs' => 'dist.location',
'spherical' => true,
],
],
]);
},
)->toArray();

To learn more about this aggregation operator, see $geoNear operator in the Server manual.

This section includes query builder examples that show how to use the following MongoDB-specific write operations:

Starting in v4.7, you can perform an upsert operation by using either of the following query builder methods:

  • upsert(): When you use this method, you can perform a batch upsert to change or insert multiple documents in one operation.

  • update(): When you use this method, you must specify the upsert option to update all documents that match the query filter or insert one document if no documents are matched. Only this upsert method is supported in versions v4.6 and earlier.

The upsert(array $values, array|string $uniqueBy, array|null $update) query builder method accepts the following parameters:

  • $values: Array of fields and values that specify documents to update or insert.

  • $uniqueBy: List of fields that uniquely identify documents in your first array parameter.

  • $update: Optional list of fields to update if a matching document exists. If you omit this parameter, the Laravel Integration updates all fields.

The following example shows how to use the upsert() query builder method to update or insert documents based on the following instructions:

  • Specify a document in which the value of the title field is 'Inspector Maigret', the value of the recommended field is false, and the value of the runtime field is 128.

  • Specify a document in which the value of the title field is 'Petit Maman', the value of the recommended field is true, and the value of the runtime field is 72.

  • Indicate that the title field uniquely identifies documents in the scope of your operation.

  • Update only the recommended field in matched documents.

$result = DB::table('movies')
->upsert(
[
['title' => 'Inspector Maigret', 'recommended' => false, 'runtime' => 128],
['title' => 'Petit Maman', 'recommended' => true, 'runtime' => 72],
],
'title',
'recommended',
);

The upsert() query builder method returns the number of documents that the operation updated, inserted, and modified.

Note

The upsert() method does not trigger events. To trigger events from an upsert operation, you can use the createOrFirst() method instead.

The following example shows how to use the update() query builder method and upsert option to update the matching document or insert one with the specified data if it does not exist. When you set the upsert option to true and the document does not exist, the command inserts both the data and the title field and value specified in the where() query operation:

$result = DB::table('movies')
->where('title', 'Will Hunting')
->update(
[
'plot' => 'An autobiographical movie',
'year' => 1998,
'writers' => [ 'Will Hunting' ],
],
['upsert' => true],
);

The update() query builder method returns the number of documents that the operation updated or inserted.

The following example shows how to use the increment() query builder method to add 3000 to the value of the imdb.votes field in the matched document:

$result = DB::table('movies')
->where('title', 'Field of Dreams')
->increment('imdb.votes', 3000);

The increment() query builder method returns the number of documents that the operation updated.

Starting in Laravel Integration v4.8, you can also use the incrementEach() query builder method to increment multiple values in a single operation. The following example uses the incrementEach() method to increase the values of the awards.wins and imdb.votes fields in the matched document:

$result = DB::table('movies')
->where('title', 'Lost in Translation')
->incrementEach([
'awards.wins' => 2,
'imdb.votes' => 1050,
]);

Note

If you pass a field to the increment() or incrementEach() method that has no value or doesn't exist in the matched documents, these methods initialize the specified field to the increment value.

The following example shows how to use the decrement() query builder method to subtract 0.2 from the value of the imdb.rating field in the matched document:

$result = DB::table('movies')
->where('title', 'Sharknado')
->decrement('imdb.rating', 0.2);

The decrement() query builder method returns the number of documents that the operation updated.

Starting in Laravel Integration v4.8, you can also use the decrementEach() query builder method to decrement multiple values in a single operation. The following example uses the decrementEach() method to decrease the values of the metacritic and imdb.rating fields in the matched document:

$result = DB::table('movies')
->where('title', 'Dunkirk')
->decrementEach([
'metacritic' => 1,
'imdb.rating' => 0.4,
]);

Note

If you pass a field to the decrement() or decrementEach() method that has no value or doesn't exist in the matched documents, these methods initialize the specified field to the decrement value.

The following example shows how to use the push() query builder method to add "Gary Cole" to the cast array field in the matched document:

$result = DB::table('movies')
->where('title', 'Office Space')
->push('cast', 'Gary Cole');

The push() query builder method returns the number of documents that the operation updated.

The following example shows how to use the pull() query builder method to remove the "Adventure" value from the genres field from the document matched by the query:

$result = DB::table('movies')
->where('title', 'Iron Man')
->pull('genres', 'Adventure');

The pull() query builder method returns the number of documents that the operation updated.

The following example shows how to use the unset() query builder method to remove the tomatoes.viewer field and value from the document matched by the query:

$result = DB::table('movies')
->where('title', 'Final Accord')
->unset('tomatoes.viewer');

The unset() query builder method returns the number of documents that the operation updated.

Back

Schema Builder