“NoSQL” has been a big buzzword in recent years within web application circles. The term itself is rather mysterious… how can there be a database with no SQL? How do you access the data?! This was my main initial question. This blog article will approach answering this question from the perspective of comparing MySQL to MongoDB (a relational data model to MongoDB’s document-based data model).

MongoDB calls will be shown using the PHP MongoDB Client Library.

What is NoSQL?

NoSQL refers to a subset of databases that are not relational model databases. The most popular of these is probably the document-based model, which databases such as MongoDB and CouchDB use. Only the document-based model will be referenced in this article, and comparisons between MySQL and MongoDB will be used to illustrate how specific actions compare.

Data Models

MySQL uses the relational data model, MongoDB uses the document-based data model. With relational databases, the database is built on a predefined schema. This takes the form of a database > tables > columns, with each column having a datatype and size. With the document model, schema is thrown wayside and you can store any structure you wish per document.

Terminology Map

Document Data Model Basics

MongoDB uses documents that store BSON (Binary JSON), but all you really need to know is you use JSON (or Arrays in PHP) when you interact with it. Think of a document as a JSON object:

{ "_id":1 "platform": "Atari 7800", "name": "Food Fight", "year": 1987 }

You can define any set of key=>value pairs you want. The keys can be anything and don’t have to match within the same collection. Here is an example of a perfectly valid collection:

{ "_id":1 "platform": "Atari 7800", "name": "Food Fight", "year": 1987 } { "_id":"34", "brewery": "Ballast Point", "name": "Sculpin", "abv": "7.0" } { "_id":ObjectID("5911edf6eadc4108cc0008c3"), "address": "123 Main St.", "name": "AMC Theatres" }

Note that this collection isn’t too useful, but it illustrates the flexibility of the model. Any valid JSON can be stored as a document. Internally, BSON requires an _id which will be automatically created if one is not set in your insert data.

All interactions done with the database are done through function calls. This is where the notion of “no SQL” comes in. Technically, you’re not using SQL, you’re just calling methods with JSON (or Arrays in PHP) as arguments.

Insert Data

Data is inserted into the collection by passing JSON, or an Array in PHP. The exact syntax depends on your client library, but for the examples below I’ll use the PHP MongoDB library.

MySQL

INSERT INTO zips (zip, city, state) VALUES ('93033', 'Some Town', 'CA');

MongoDB

$db->zips->insertOne(['zip'=>'93033','city'=>'Some Town','state'=>'CA']);$db->zips->insertMany() can also be used to insert multiple documents at once.

Querying Data

Data is queried by supplying attributes that match what you’re looking for. Also note that with MongoDB, match terms are case-sensitive, whereas with MySQL CHAR, VARCHAR, and TEXT fields are not case-sensitive.

Anything beyond the most basic query in MongoDB requires what are called “projections”. These are operators you use in queries for filtering, selecting specific fields, ordering, etc.

SELECT (basic)

SELECT * FROM zips;$db->zips->find([]);SELECT * FROM zips WHERE city = 'torrance';$db->zips->find(["city"=>"torrance"]);

AND

This does not require any projections, just supply all that must be matched.

SELECT * FROM zips WHERE city = 'torrance' AND state = 'CA';$db->zips->find(['city'=>'torrance','state'=>'CA']);

OR

Here we start getting into projections, using the $or operator.

SELECT * FROM zips WHERE city = 'torrance' OR city = 'lomita';$db->zips->find(['city'=>['$or'=>['torrance', 'lomita']]]);

IN

Here’s the preferred way to do multiple OR conditions, using the $in operator.

SELECT * FROM zips WHERE state IN('CA','AZ');$db->zips->find(['state'=>['$in'=>['CA','AZ']]]);

Ranges

MongoDB has a set of comparison operators for ranges. They are $lt (less than), $lte (less than or equal), $gt (greater than), and $gte.

SELECT * FROM zips WHERE zipcode < '20000';$db->zips->find(['zipcode'=>['$lt' => '20000']]);

SELECT Fields

To select or omit specific keys, you use the “projection” key (note there is no dollar sign). Also note that this is passed in as the 2nd argument, which is the ‘options’ arg:

SELECT city, state FROM zips;$db->zips->find([], ['projection'=>['city'=>1,'state'=>1]]);

To omit fields, you specify those fields and set the value to 0. One caveat is that you cannot mix 1s and 0s, with the exception of the _id field.

Select all fields except ‘city’:

$db->zips->find([], ['projection'=>['city'=>0]]);

Select city and state, but not _id:

$db->zips->find([], ['projection' => ['_id'=>0, 'city'=>1, 'state'=>1]]);

This would NOT be valid:

$db->zips->find([], ['projection' => ['city'=>1, 'state'=>0]]); // no good!

In Closing

There’s a lot more to learn regarding querying data from the database, but it’s beyond the introductory scope of this post. Keep an eye out for future posts exploring MongoDB!

Endertech is a Los Angeles Software Development Company able to custom develop database applications. Contact us if you would like a free consultation.