<p><span style="font-weight: 400;">We're joined by Developer Advocate Lauren Schaefer and guest host Rebecca Mosner, Social Media Manager, to talk about migration from SQL to MongoDB. Learn more about Lauren's background, some of the things she’s done prior to joining MongoDB, and how to approach MongoDB from the perspective of someone familiar with SQL. Check out Lauren's series on SQL to NoSQL migration on developer.mongodb.com</span></p>

MongoDB

Ep. 11 From SQL to MongoDB

Joe Karlsson



<div class="introduction">

Let's be honest: Many devs coming to MongoDB are joining the community
with a strong background in SQL. I would personally include myself in
this subset of MongoDB devs. I think it's useful to map terms and
concepts you might be familiar with in SQL to help
"[translate](https://docs.mongodb.com/manual/reference/sql-comparison/)"
your work into MongoDB Query Language (MQL). More specifically, in this
post, I will be walking through translating the MongoDB Aggregation
Pipeline from SQL.

</div>

<div class="content">

## What is the Aggregation Framework?

The aggregation framework allows you to analyze your data in real time.
Using the framework, you can create an aggregation pipeline that
consists of one or more
[stages](https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/#aggregation-pipeline-operator-reference).
Each stage transforms the documents and passes the output to the next
stage.

If you're familiar with the Unix pipe \|, you can think of the
aggregation pipeline as a very similar concept. Just as output from one
command is passed as input to the next command when you use piping,
output from one stage is passed as input to the next stage when you use
the aggregation pipeline.

SQL is a declarative language. You have to declare what you want to
see—that's why SELECT comes first. You have to think in sets, which can
be difficult, especially for functional programmers. With MongoDB's
aggregation pipeline, you can have stages that reflect how you think—for
example, "First, let's group by X. Then, we'll get the top 5 from every
group. Then, we'll arrange by price." This is a difficult query to do in
SQL, but much easier using the aggregation pipeline framework.

The aggregation framework has a variety of
[stages](https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/)
available for you to use. Today, we'll discuss the basics of how to use
[$match](https://docs.mongodb.com/manual/reference/operator/aggregation/match/),
[$group](https://docs.mongodb.com/manual/reference/operator/aggregation/group/),
[$sort](https://docs.mongodb.com/manual/reference/operator/aggregation/sort/),
and
[$limit](https://docs.mongodb.com/manual/reference/operator/aggregation/limit/).
Note that the aggregation framework has many other powerful stages,
including
[$count](https://docs.mongodb.com/manual/reference/operator/aggregation/count/),
[$geoNear](https://docs.mongodb.com/manual/reference/operator/aggregation/geoNear/),
[$graphLookup](https://docs.mongodb.com/manual/reference/operator/aggregation/graphLookup/),
[$project](https://docs.mongodb.com/manual/reference/operator/aggregation/project/),
[$unwind](https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/),
and others.

>
>
>If you want to check out another great introduction to the MongoDB
>Aggregation Pipeline, be sure to check out [Introduction to the MongoDB
>Aggregation
>Framework](https://developer.mongodb.com/quickstart/introduction-aggregation-framework).
>
>

## Terminology and Concepts

The following table provides an overview of common SQL aggregation
terms, functions, and concepts and the corresponding MongoDB
[aggregation
operators](https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/#aggregation-pipeline-operator-reference):

| **SQL Terms, Functions, and Concepts** | **MongoDB Aggregation Operators**                                                                                                                                                                                              |
|----------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| WHERE                                  | [$match](https://docs.mongodb.com/manual/reference/operator/aggregation/match/#pipe._S_match)                                                                                                                                  |
| GROUP BY                               | [$group](https://docs.mongodb.com/manual/reference/operator/aggregation/group/#pipe._S_group)                                                                                                                                  |
| HAVING                                 | [$match](https://docs.mongodb.com/manual/reference/operator/aggregation/match/#pipe._S_match)                                                                                                                                  |
| SELECT                                 | [$project](https://docs.mongodb.com/manual/reference/operator/aggregation/project/#pipe._S_project)                                                                                                                            |
| LIMIT                                  | [$limit](https://docs.mongodb.com/manual/reference/operator/aggregation/limit/#pipe._S_limit)                                                                                                                                  |
| OFFSET                                 | [$skip](https://docs.mongodb.com/manual/reference/operator/aggregation/skip/index.html)                                                                                                                                        |
| ORDER BY                               | [$sort](https://docs.mongodb.com/manual/reference/operator/aggregation/sort/index.html)                                                                                                                                        |
| SUM()                                  | [$sum](https://docs.mongodb.com/manual/reference/operator/aggregation/sum/#grp._S_sum)                                                                                                                                         |
| COUNT()                                | [$sum](https://docs.mongodb.com/manual/reference/operator/aggregation/sum/#grp._S_sum) and [$sortByCount](https://docs.mongodb.com/manual/reference/operator/aggregation/sortByCount/#pipe._S_sortByCount)                     |
| JOIN                                   | [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#pipe._S_lookup)                                                                                                                               |
| SELECT INTO NEW_TABLE                  | [$out](https://docs.mongodb.com/manual/reference/operator/aggregation/out/#pipe._S_out)                                                                                                                                        |
| MERGE INTO TABLE                       | [$merge](https://docs.mongodb.com/manual/reference/operator/aggregation/merge/#pipe._S_merge) (Available starting in MongoDB 4.2)                                                                                              |
| UNION ALL                              | [$unionWith](https://docs.mongodb.com/manual/reference/operator/aggregation/unionWith/#pipe._S_unionWith) ([Available starting in MongoDB 4.4](https://developer.mongodb.com/how-to/use-union-all-aggregation-pipeline-stage)) |

Alright, now that we've covered the basics of MongoDB Aggregations,
let's jump into some examples.

## SQL Setup

The SQL examples assume *two* tables, *album* and *songs*, that join by
the *song.album_id* and the *songs.id* columns. Here's what the tables
look like:

##### Albums

| **id** | **name**                          | **band_name**    | **price** | **status** |
|--------|-----------------------------------|------------------|-----------|------------|
| 1      | lo-fi chill hop songs to study to | Silicon Infinite | 2.99      | A          |
| 2      | Moon Rocks                        | Silicon Infinite | 1.99      | B          |
| 3      | Flavour                           | Organical        | 4.99      | A          |


##### Songs


| **id** | **title**             | **plays** | **album_id** |
|--------|-----------------------|-----------|--------------|
| 1      | Snow Beats            | 133       | 1            |
| 2      | Rolling By            | 242       | 1            |
| 3      | Clouds                | 3191      | 1            |
| 4      | But First Coffee      | 562       | 3            |
| 5      | Autumn                | 901       | 3            |
| 6      | Milk Toast            | 118       | 2            |
| 7      | Purple Mic            | 719       | 2            |
| 8      | One Note Dinner Party | 1242      | 2            |


I used a site called [SQL Fiddle](http://sqlfiddle.com/#!17/6858c/1),
and used PostgreSQL 9.6 for all of my examples. However, feel free to
run these sample SQL snippets wherever you feel most comfortable. In
fact, this is the code I used to set up and seed my tables with our
sample data:

``` SQL
-- Creating the main albums table
CREATE TABLE IF NOT EXISTS albums (
    id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    name VARCHAR(40) NOT NULL UNIQUE,
    band_name VARCHAR(40) NOT NULL,
    price float8 NOT NULL,
    status VARCHAR(10) NOT NULL
);

-- Creating the songs table
CREATE TABLE IF NOT EXISTS songs (
    id SERIAL PRIMARY KEY NOT NULL,
    title VARCHAR(40) NOT NULL,
    plays integer NOT NULL,
    album_id BIGINT NOT NULL REFERENCES albums ON DELETE RESTRICT
);

INSERT INTO albums (name, band_name, price, status)
VALUES
    ('lo-fi chill hop songs to study to', 'Silicon Infinite', 7.99, 'A'),
    ('Moon Rocks', 'Silicon Infinite', 1.99, 'B'),
    ('Flavour', 'Organical', 4.99, 'A');

INSERT INTO songs (title, plays, album_id)
VALUES
    ('Snow Beats', 133, (SELECT id from albums WHERE name='lo-fi chill hop songs to study to')),
    ('Rolling By', 242, (SELECT id from albums WHERE name='lo-fi chill hop songs to study to')),
    ('Clouds', 3191, (SELECT id from albums WHERE name='lo-fi chill hop songs to study to')),
    ('But First Coffee', 562, (SELECT id from albums WHERE name='Flavour')),
    ('Autumn', 901, (SELECT id from albums WHERE name='Flavour')),
    ('Milk Toast', 118, (SELECT id from albums WHERE name='Moon Rocks')),
    ('Purple Mic', 719, (SELECT id from albums WHERE name='Moon Rocks')),
    ('One Note Dinner Party', 1242, (SELECT id from albums WHERE name='Moon Rocks'));
```

## MongoDB Setup

The MongoDB examples assume *one* collection `albums` that contains
documents with the following schema:

``` json
{
    name : 'lo-fi chill hop songs to study to',
    band_name: 'Silicon Infinite',
    price: 7.99,
    status: 'A',
    songs: [
        { title: 'Snow beats', 'plays': 133 },
        { title: 'Rolling By', 'plays': 242 },
        { title: 'Sway', 'plays': 3191 }
    ]
}
```

For this post, I did all of my prototyping in a MongoDB Visual Studio
Code plugin playground. For more information on how to use a MongoDB
Playground in Visual Studio Code, be sure to check out this post: [How
To Use The MongoDB Visual Studio Code
Plugin](https://developer.mongodb.com/how-to/mongodb-visual-studio-code-plugin).
Once you have your playground all set up, you can use this snippet to
set up and seed your collection. You can also follow along with this
demo by using the [MongoDB Web
Shell](https://docs.mongodb.com/manual/tutorial/getting-started).

``` javascript
// Select the database to use.
use('mongodbVSCodePlaygroundDB');

// The drop() command destroys all data from a collection.
// Make sure you run it against the correct database and collection.
db.albums.drop();

// Insert a few documents into the albums collection.
db.albums.insertMany([
    {
        'name' : 'lo-fi chill hop songs to study to', band_name: 'Silicon Infinite', price: 7.99, status: 'A',
        songs: [
            { title: 'Snow beats', 'plays': 133 },
            { title: 'Rolling By', 'plays': 242 },
            { title: 'Clouds', 'plays': 3191 }
        ]
    },
    {
        'name' : 'Moon Rocks', band_name: 'Silicon Infinite', price: 1.99, status: 'B',
        songs: [
            { title: 'Milk Toast', 'plays': 118 },
            { title: 'Purple Mic', 'plays': 719 },
            { title: 'One Note Dinner Party', 'plays': 1242 }
        ]
    },
    {
        'name' : 'Flavour', band_name: 'Organical', price: 4.99, status: 'A',
        songs: [
            { title: 'But First Coffee', 'plays': 562 },
            { title: 'Autumn', 'plays': 901 }
        ]
    },
]);
```

## Quick Reference

### Count all records from albums

#### SQL

``` SQL
SELECT COUNT(*) AS count
FROM albums
```

#### MongoDB

``` javascript
db.albums.aggregate( [
    {
        $group: {
            _id: null,  //  An _id value of null on the $group operator accumulates values for all the input documents as a whole.
            count: { $sum: 1 }
        }
    }
] );
```

### Sum the price field from albums

#### SQL

``` SQL
SELECT SUM(price) AS total
FROM albums
```

#### MongoDB

``` javascript
db.albums.aggregate( [
    {
        $group: {
            _id: null,
            total: { $sum: "$price" }
        }
    }
] );
```

### For each unique band_name, sum the price field

#### SQL

``` SQL
SELECT band_name,
SUM(price) AS total
FROM albums
GROUP BY band_name
```

#### MongoDB

``` javascript
db.albums.aggregate( [
    {
        $group: {
            _id: "$band_name",
            total: { $sum: "$price" }
        }
    }
] );
```

### For each unique band_name, sum the price field, results sorted by sum

#### SQL

``` SQL
SELECT band_name,
    SUM(price) AS total
FROM albums
GROUP BY band_name
ORDER BY total
```

#### MongoDB

``` javascript
db.albums.aggregate( [
    {
        $group: {
            _id: "$band_name",
            total: { $sum: "$price" }
        }
    },
    { $sort: { total: 1 } }
] );
```

### For band_name with multiple albums, return the band_name and the corresponding album count

#### SQL

``` SQL
SELECT band_name,
    count(*)
FROM albums
GROUP BY band_name
HAVING count(*) > 1;
```

#### MongoDB

``` javascript
db.albums.aggregate( [
    {
      $group: {
         _id: "$band_name",
         count: { $sum: 1 }
      }
    },
    { $match: { count: { $gt: 1 } } }
 ] );
```

### Sum the price of all albums with status A and group by unique band_name

#### SQL

``` SQL
SELECT band_name,
    SUM(price) as total
FROM albums
WHERE status = 'A'
GROUP BY band_name
```

#### MongoDB

``` javascript
db.albums.aggregate( [
    { $match: { status: 'A' } },
    {
        $group: {
            _id: "$band_name",
            total: { $sum: "$price" }
        }
    }
] );
```

### For each unique band_name with status A, sum the price field and return only where the sum is greater than $5.00

#### SQL

``` SQL
SELECT band_name,
    SUM(price) as total
FROM albums
WHERE status = 'A'
GROUP BY band_name
HAVING SUM(price) > 5.00;
```

#### MongoDB

``` javascript
db.albums.aggregate( [
    { $match: { status: 'A' } },
    {
        $group: {
            _id: "$band_name",
            total: { $sum: "$price" }
        }
    },
    { $match: { total: { $gt: 5.00 } } }
] );
```

### For each unique band_name, sum the corresponding song plays field associated with the albums

#### SQL

``` SQL
SELECT band_name,
    SUM(songs.plays) as total_plays
FROM albums,
    songs
WHERE songs.album_id = albums.id
GROUP BY band_name;
```

#### MongoDB

``` javascript
db.albums.aggregate( [
    { $unwind: "$songs" },
    {
        $group: {
            _id: "$band_name",
            qty: { $sum: "$songs.plays" }
        }
    }
] );
```

### For each unique album, get the song from album with the most plays

#### SQL

``` SQL
SELECT name, title, plays
    FROM songs s1 INNER JOIN albums ON (album_id = albums.id)
WHERE plays=(SELECT MAX(s2.plays)
    FROM songs s2
WHERE s1.album_id = s2.album_id)
ORDER BY name;
```

#### MongoDB

``` javascript
db.albums.aggregate( [
    { $project:
        {
            name: 1,
            plays: {
                $filter: {
                    input: "$songs",
                    as: "item",
                    cond: { $eq: ["$item.plays", { $max: "$songs.plays" }] }
                }
            }
        }
    }
] );
```

</div>

<div class="summary">

## Wrapping Up

This post is in no way a complete overview of all the ways that MongoDB
can be used like a SQL-based database. This was only meant to help devs
in SQL land start to make the transition over to MongoDB with some basic
queries using the aggregation pipeline. The aggregation framework has
many other powerful stages, including
[$count](https://docs.mongodb.com/manual/reference/operator/aggregation/count/),
[$geoNear](https://docs.mongodb.com/manual/reference/operator/aggregation/geoNear/),
[$graphLookup](https://docs.mongodb.com/manual/reference/operator/aggregation/graphLookup/),
[$project](https://docs.mongodb.com/manual/reference/operator/aggregation/project/),
[$unwind](https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/),
and others.

If you want to get better at using the MongoDB Aggregation Framework, be
sure to check out [MongoDB University: M121 - The MongoDB Aggregation
Framework](https://university.mongodb.com/courses/M121/about). Or,
better yet, try to use some advanced MongoDB aggregation pipeline
queries in your next project! If you have any questions, be sure to head
over to the [MongoDB Community
Forums](https://university.mongodb.com/courses/M121/about). It's the
best place to get your MongoDB questions answered.

## Resources:

-   MongoDB University: M121 - The MongoDB Aggregation Framework:
    <https://university.mongodb.com/courses/M121/about>
-   How to Use Custom Aggregation Expressions in MongoDB 4.4:
    <https://developer.mongodb.com/how-to/use-function-accumulator-operators>
-   Introduction to the MongoDB Aggregation Framework:
    <https://developer.mongodb.com/quickstart/introduction-aggregation-framework>
-   How to Use the Union All Aggregation Pipeline Stage in MongoDB 4.4:
    <https://developer.mongodb.com/how-to/use-union-all-aggregation-pipeline-stage>
-   Aggregation Framework with Node.js Tutorial:
    <https://developer.mongodb.com/quickstart/node-aggregation-framework>
-   Aggregation Pipeline Quick Reference:
    [https://docs.mongodb.com/manual/meta/aggregation-quick-reference](https://docs.mongodb.com/manual/meta/aggregation-quick-reference/)
-   SQL to Aggregation Mapping Chart:
    [https://docs.mongodb.com/manual/reference/sql-aggregation-comparison](https://docs.mongodb.com/manual/reference/sql-aggregation-comparison/)
-   SQL to MongoDB Mapping Chart:
    [https://docs.mongodb.com/manual/reference/sql-comparison](https://docs.mongodb.com/manual/reference/sql-comparison/)
-   Questions? Comments? We'd love to connect with you. Join the
    conversation on the MongoDB Community Forums:
    [https://developer.mongodb.com/community/forums](https://developer.mongodb.com/community/forums/)

</div>


This is an overview of common SQL aggregation terms, functions, and concepts and the corresponding MongoDB aggregation operators.

MongoDB Aggregation Pipeline Queries vs SQL Queries

Lauren Schaefer

Welcome to the final post in my series on moving from SQL to MongoDB. In the first post, I [mapped terms and concepts from SQL to MongoDB](https://www.mongodb.com/developer/article/map-terms-concepts-sql-mongodb/). In the second post, I discussed the [top four reasons why you should use MongoDB](https://www.mongodb.com/developer/article/top-4-reasons-to-use-mongodb/).

Now that we have an understanding of the terminology as well as why MongoDB is worth the effort of changing your mindset, let's talk about three key ways you need to change your mindset.

Your first instinct might be to convert your existing columns and rows to fields and documents and stick with your old ways of modeling data. We've found that people who try to use MongoDB in the same way that they use a relational database struggle and sometimes fail.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://www.mongodb.com/developer/images/article/sql-to-mdb/trashcomputer.gif"
        alt="Ron throws computer in the trash"
    />
</figure>

We don't want that to happen to you.

Let's discuss three key ways to change your mindset as you move from SQL to MongoDB.

-   [Embrace Document Diversity](#sql-embrace-document-diversity)
-   [Data That is Accessed Together Should Be Stored Together](#sql-data-accessed-together)
-   [Tread Carefully with Transactions](#sql-tread-carefully-transactions)

>
>
>This article is based on a presentation I gave at MongoDB World and MongoDB.local Houston entitled "From SQL to NoSQL: Changing Your Mindset."
>
>If you prefer videos over articles, check out the [recording](https://youtu.be/FFj04Apz_BE). Slides are available [here](https://www.slideshare.net/LaurenHaywardSchaefe/jumpstart-from-sql-to-nosql-changing-your-mindset).
>
>

## <a name="sql-embrace-document-diversity">Embrace Document Diversity

As we saw in the first post in this series when we modeled documents for Leslie, Ron, and Lauren, [not all documents in a collection need to have the same fields](https://www.mongodb.com/developer/article/map-terms-concepts-sql-mongodb/#std-label-sql-mdb-1-store-laurens-info).

Users

``` json
{
   "_id": 1,
   "first_name": "Leslie",
   "last_name": "Yepp",
   "cell": "8125552344",
   "city": "Pawnee",
   "location": [ -86.536632, 39.170344 ],
   "hobbies": ["scrapbooking", "eating waffles", "working"],
   "jobHistory": [
      {
         "title": "Deputy Director",
         "yearStarted": 2004
      },
      {
         "title": "City Councillor",
         "yearStarted": 2012
      },
      {
         "title": "Director, National Parks Service, Midwest Branch",
         "yearStarted": 2014
      }
   ]
},

{
   "_id": 2,
   "first_name": "Ron",
   "last_name": "Swandaughter",
   "cell": "8125559347",
   "city": "Pawnee",
   "hobbies": ["woodworking", "fishing"],
   "jobHistory": [
      {
         "title": "Director",
         "yearStarted": 2002
      },
      {
         "title": "CEO, Kinda Good Building Company",
         "yearStarted": 2014
      },
      {
         "title": "Superintendent, Pawnee National Park",
         "yearStarted": 2018
      }
   ]
},

{
   "_id": 3,
   "first_name": "Lauren",
   "last_name": "Burhug",
   "city": "Pawnee",
   "hobbies": ["soccer"],
   "school": "Pawnee Elementary"
}
```

For those of us with SQL backgrounds, this is going to feel uncomfortable and probably a little odd at first. I promise it will be ok. Embrace document diversity. It gives us so much flexibility and power to model our data.

In fact, MongoDB has a data modeling pattern specifically for when your documents do not have the same fields. It's called the [Polymorphic Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-polymorphic-pattern). We use the Polymorphic Pattern when documents in a collection are of similar but not identical structures.

Let's take a look at an example that builds on the Polymorphic Pattern. Let's say we decided to keep a list of each user's social media followers inside of each `User` document. Lauren and Leslie don't have very many followers, so we could easily list their followers in their documents. For example, Lauren's document might look something like this:

``` json
{
   "_id": 3,
   "first_name": "Lauren",
   "last_name": "Burhug",
   "city": "Pawnee",
   "hobbies": ["soccer"],
   "school": "Pawnee Elementary",
   "followers": [
         "Brandon",
         "Wesley",
         "Ciara",
         ...
   ]
}
```

This approach would likely work for most of our users. However, since Ron built a chair that appeared in the very popular Bloosh Magazine, Ron has millions of followers. If we try to list all of his followers in his `User` document, it may exceed the [16 megabyte document size limit](https://docs.mongodb.com/manual/reference/limits/#BSON-Document-Size). The question arises: do we want to optimize our document model for the typical use case where a user has a few hundred followers or the outlier use case where a user has millions of followers?

We can utilize the [Outlier Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-outlier-pattern) to solve this problem. The Outlier Pattern allows us to model our data for the typical use case but still handle outlier use cases.

We can begin modeling Ron's document just like Lauren's and include a list of followers. When we begin to approach the document size limit, we can add a new `has_extras` field to Ron's document. (The field can be named anything we'd like.)

``` json
{
   "_id": 2,
   "first_name": "Ron",
   "last_name": "Swandaughter",
   "cell": "8125559347",
   "city": "Pawnee",
   "hobbies": ["woodworking", "fishing"],
   "jobHistory": [
      {
         "title": "Director",
         "yearStarted": 2002
      },
      ...
   ], 
   "followers": [
      "Leslie",
      "Donna",
      "Tom"
      ...
   ],
   "has_extras": true
}
```

Then we can create a new document where we will store the rest of Ron's followers.

``` json
{
   "_id": 2.1,
   "followers": [
      "Jerry",
      "Ann",
      "Ben"
      ...
   ],
   "is_overflow": true
}
```

If Ron continues to gain more followers, we could create another overflow document for him.

The great thing about the Outlier Pattern is that we are optimizing for the typical use case but we have the flexibility to handle outliers.

So, embrace document diversity. Resist the urge to force all of your documents to have identical structures just because it's what you've always done.

For more on MongoDB data modeling design patterns, see [Building with Patterns: A Summary](https://www.mongodb.com/blog/post/building-with-patterns-a-summary) and the free MongoDB University Course [M320: Data Modeling](http://bit.ly/M320_DataModeling).

## <a name="sql-data-accessed-together">Data That is Accessed Together Should be Stored Together

If you have experience with SQL databases, someone probably drilled into your head that you should [normalize your data](https://en.wikipedia.org/wiki/Database_normalization). Normalization is considered good because it prevents data duplication. Let's take a step back and examine the motivation for database normalization.

When relational databases became popular, disk space was extremely expensive. Financially, it made sense to normalize data and save disk space. Take a look at the chart below that shows the cost per megabyte over time.

:charts[]{url="https://charts.mongodb.com/charts-storage-costs-sbekh" id="740dea93-d2da-44c3-8104-14ccef947662"}

The cost has drastically gone down. Our phones, tablets, laptops, and flash drives have more storage capacity today than they did even five to ten years ago for a fraction of the cost. When was the last time you deleted a photo? I can't remember when I did. I keep even the really horribly unflattering photos. And I currently backup all of my photos on two external hard drives and multiple cloud services. Storage is so cheap.

Storage has become so cheap that we've seen a shift in the cost of software development. Thirty to forty years ago storage was a huge cost in software development and developers were relatively cheap. Today, the costs have flipped: storage is a small cost of software development and developers are expensive.

Instead of optimizing for storage, we need to optimize for developers' time and productivity.

As a developer, I like this shift. I want to be able to focus on implementing business logic and iterate quickly. Those are the things that matter to the business and move developers' careers forward. I don't want to be dragged down by data storage specifics.

Think back to the [example in the previous post](https://www.mongodb.com/developer/article/top-4-reasons-to-use-mongodb/#std-label-sql-mdb-2-program-faster) where I coded retrieving and updating a user's profile information. Even in that simple example, I was able to write fewer lines of code and move quicker when I used MongoDB.

So, optimize your data model for developer productivity and query optimization. Resist the urge to normalize your data for the sake of normalizing your data.

*Data that is accessed together should be stored together*. If you end up repeating data in your database, that's ok—especially if you won't be updating the data very often.

## <a name="sql-tread-carefully-transactions">Tread Carefully with Transactions

We discussed in a previous post that [MongoDB supports transactions](https://www.mongodb.com/developer/article/map-terms-concepts-sql-mongodb/#std-label-sql-mdb-1-transactions-mapping). The MongoDB engineering team did an amazing job of implementing transactions. They work so well!

But here's the thing. Relying on transactions is a bad design smell.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://www.mongodb.com/developer/images/article/sql-to-mdb/badsmell.gif"
        alt="Ben is shocked by the horrendous smell of Tom's cologne"
    />
</figure>

Why? This builds on our first two points in this section.

First, not all documents need to have the same fields. Perhaps you're breaking up data between multiple collections because it's not all of identical structure. If that's the only reason you've broken the data up, you can probably put it back together in a single collection.

Second, data that is accessed together should be stored together. If you're following this principle, you won't need to use transactions. Some use cases call for transactions. Most do not. If you find yourself frequently using transactions, take a look at your data model and consider if you need to restructure it.

For more information on transactions and when they should be used, see the [MongoDB MongoDB Multi-Document ACID Transactions Whitepaper](https://www.mongodb.com/collateral/mongodb-multi-document-acid-transactions).

## Wrap Up

Today we discussed the three things you need to know as you move from SQL to MongoDB:

-   [Embrace Document Diversity](#sql-embrace-document-diversity)
-   [Data That is Accessed Together Should Be Stored Together](#sql-data-accessed-together)
-   [Tread Carefully with Transactions](#sql-tread-carefully-transactions)

I hope you enjoy using MongoDB! If you want to jump in and start coding, my teammates and I have written [Quick Start Tutorials](https://www.mongodb.com/blog/channel/quickstart) for a variety of programming languages. I also highly recommend the free courses on [MongoDB University](http://bit.ly/mdb-university).

In summary, don't be like Ron. (I mean, don't be like him in this particular case, because Ron is amazing.)

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://www.mongodb.com/developer/images/article/sql-to-mdb/trashcomputer.gif"
        alt="Ron throws computer in the trash"
    />
</figure>

Change your mindset and get the full value of MongoDB.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://www.mongodb.com/developer/images/article/sql-to-mdb/dancingron.gif"
        alt="Ron dances happily with a tiny black hat attached to his head"
    />
</figure>


Discover the 3 things you need to know when you switch from SQL to MongoDB.

3 Things to Know When You Switch from SQL to MongoDB

Learn how SQL terms and concepts map to MongoDB.

Mapping Terms and Concepts from SQL to MongoDB

Dominic Frei

Persisting data is an important part of most games. Unity offers only a limited set of solutions, which means we have to look around for other options as well. In this tutorial series, we will explore the options given to us by Unity and third-party libraries.

Saving Data in Unity3D Using SQLite

Pavel Duchovny

Modern platforms have a wide variety of data sources. As businesses grow, they have to constantly evolve their data management and have sophisticated, scalable, and convenient tools to analyse data from all sources to produce business insights.

MongoDB has developed a rich and powerful [query language](https://docs.mongodb.com/manual/tutorial/query-documents/), including a very robust [aggregation framework](https://docs.mongodb.com/manual/aggregation/). 

These were mainly done to optimize the way developers work with data and provide great tools to manipulate and query MongoDB documents.

Having said that, many developers, analysts, and tools still prefer the legacy SQL language to interact with the data sources. SQL has a strong foundation around joining data as this was a core concept of the legacy relational databases normalization model. 

This makes SQL have a convenient syntax when it comes to describing joins. 

Providing MongoDB users the ability to leverage SQL to analyse multi-source documents while having a flexible schema and data store is a compelling solution for businesses.

## Data Sources and the Challenge

Consider a requirement to create a single view to analyze data from operative different systems. For example:

- Customer data is managed in the user administration systems (REST API).
- Financial data is managed in a financial cluster (Atlas cluster).
- End-to-end transactions are stored in files on cold storage gathered from various external providers (S3 store).

How can we combine and best join this data? 

[MongoDB Atlas Query Federation](https://www.mongodb.com/docs/atlas/data-federation/) connects multiple data sources using the different [data store types](https://www.mongodb.com/docs/atlas/data-federation/overview/#mongodb-expression-exp.Data-Store). Once the data sources are mapped, we can create collections consuming this data. Those collections can have [SQL schema](https://docs.mongodb.com/datalake/reference/cli/sql/sqlgenerateschema/) generated, allowing us to perform sophisticated joins and do JDBC queries from various BI tools.

In this article, we will showcase the extreme power hidden in Atlas [SQL Query](https://www.mongodb.com/docs/atlas/data-federation/query/query-with-sql/).

## Setting Up My Federated Database Instace
In the following view, I have created three main data stores: 
- S3 Transaction Store (S3 sample data).
- Accounts from my Atlas clusters (Sample data sample_analytics.accounts).
- Customer data from a secure https source.

![Query Federation Sources](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Screenshot_2022_06_20_at_12_33_52_ec0802abed.png)

I mapped the stores into three collections under `FinTech` database:

- `Transactions`
- `Accounts`
- `CustomerDL`

Now, I can see them through a  Query Federation connection as MongoDB collections.

Let's grab our Query Federation instance connection string from the Atlas UI.

![Connection](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Screenshot_2022_06_20_at_12_37_55_be8464c0d1.png)

This connection string can be used with our BI tools or client applications to run SQL queries.

## Connecting and Using $sql and db.sql

Once we connect to the Query Federation instancee via a mongosh shell, we can generate a SQL schema for our collections. This is optional for the JDBC or $sql operators to recognise collections as SQL “tables”  as this step is done automatically for newly created collections, however, its always good to be familiar with the available commands.

#### Generate SQL schema for each collection:
```js
use admin;
db.runCommand({sqlGenerateSchema: 1, sampleNamespaces: ["FinTech.customersDL"], sampleSize: 1000, setSchemas: true})
{
  ok: 1,
  schemas: [ { databaseName: 'FinTech', namespaces: [Array] } ]
}
db.runCommand({sqlGenerateSchema: 1, sampleNamespaces: ["FinTech.accounts"], sampleSize: 1000, setSchemas: true})
{
  ok: 1,
  schemas: [ { databaseName: 'FinTech', namespaces: [Array] } ]
}
db.runCommand({sqlGenerateSchema: 1, sampleNamespaces: ["FinTech.transactions"], sampleSize: 1000, setSchemas: true})
{
  ok: 1,
  schemas: [ { databaseName: 'FinTech', namespaces: [Array] } ]
}
```
#### Running SQL queries and joins using $sql stage:
```js
use FinTech;
db.aggregate([{
  $sql: {
    statement: "SELECT a.* , t.transaction_count FROM accounts a, transactions t where a.account_id = t.account_id SORT BY t.transaction_count DESC limit 2",
    format: "jdbc",
    formatVersion: 2,
    dialect: "mysql",
  }
}])

// Equivalent command
db.sql("SELECT a.* , t.transaction_count FROM accounts a, transactions t where a.account_id = t.account_id SORT BY t.transaction_count DESC limit 2");
```

The above query will prompt account information and the transaction counts of each account.

## Connecting Via JDBC

Let’s connect a powerful BI tool like Tableau with the [JDBC driver](https://www.mongodb.com/docs/atlas/data-federation/query/sql/drivers/jdbc/connect/).

[Download JDBC Driver](https://search.maven.org/artifact/org.mongodb/mongodb-jdbc).

![Latest Driver](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Screenshot_2022_06_20_at_12_44_11_e2df7beb3c.png)

#### Connect to Tableau
You have 2 main options to connect, via "MongoDB Atlas" connector or via a JDBC general connector. Please follow the relevant instructions and prerequisites on this [documentation](https://www.mongodb.com/docs/atlas/data-federation/query/sql/tableau/connect/) page.

##### Connector "MongoDB Atlas by MongoDB"
Search and click the  “MongoDB Atlas by MongoDB” connector and provide the information pointing to our Query Federation URI. See the following example:

![Tablue Connector](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Screenshot_2022_06_20_at_12_50_53_ace30bbb42.png)

##### "JDBC" Connector

Setting `connection.properties` file.
```
user=root
password=*******
authSource=admin
database=FinTech
ssl=true
compressors=zlib
```

Click the “Other Databases (JDBC)” connector, copy JDBC connection format, and load the `connection.properties` file.

![Tableau Connector Selection](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/tableau_select_other_db_1c75d7023c.png)

Once the data is read successfully, the collections will appear on the right side.

#### Setting and Joining Data

![Setting Tables](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Screen_Shot_2021_10_17_at_11_19_53_8913b663c6.png)

We can drag and drop collections from different sources and link them together.

![Joining Tables with Drag & Drop](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Screen_Shot_2021_10_17_at_15_14_36_0e2fdcae97.png)

In my case, I connected `Transactions` => `Accounts` based on the `Account Id` field, and accounts and users based on the `Account Id` to `Accounts` field.

![Unified View of Accounts, Transactions and Users data](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Screen_Shot_2021_10_17_at_15_06_50_9e906438d5.png)

In this view, we will see a unified table for all accounts with usernames and their transactions start quarter. 

## Summary

MongoDB has all the tools to read, transform, and analyse your documents for almost any use-case.  

Whether your data is in an Atlas operational cluster, in a service, or on cold storage like S3, Atlas Query Federation will provide you with the ability to join the data in real time. With the option to use powerful join SQL syntax and SQL-based BI tools like Tableau, you can get value out of the data in no time.

Try Atlas Query Federation with your BI tools and SQL today.

Learn how new SQL-based queries can power your Query Federation insights in minutes. Integrate this capability with powerful BI tools like Tableau to get immediate value out of your data. 

SQL

Featured

Atlas Query Federation SQL to Form Powerful Data Interactions

Saving Data in Unity3D Using SQLite

3 Things to Know When You Switch from SQL to MongoDB

All SQL Content