Demo Notes

Script:

Many database professionals initially came from SQL background. As such, they are familiar and comfortable with SQL frameworks. For those users, working with MongoDB query language presents a challenge. Especially this manifests with aggregation pipeline, which is most powerful yet hardest to master tool.

In order to address the gap, and make new MongoDB users more comfortable with aggregation pipeline syntax, we present you an SQL2AGG tool, which would take a SQL statement and make an attempt to convert it into aggregation pipeline. This tool is accessible on github and can be used via online web page, or offline as a single-page HTML.

What problem are we trying to solve:

demonstrate a sample condition and what it translated to. Show that there is a lot of JSON and curly braces are impossible to count

SELECT:

    ((f4=977) and f5>=555) and (f6>6 or f7<=2)
    or f8 between 7 and 20

Becomes:

    "$match": {
      "$or": [
        {
          "$and": [
            {
              "$eq": {
                "f4": 977
              }
            },
            {
              "$gte": {
                "f5": 555
              }
            },
            {
              "$or": [
                {
                  "$gt": {
                    "f6": 6
                  }
                },
                {
                  "$lte": {
                    "f7": 2
                  }
                }
              ]
            }
          ]
        },
        {
          "f8": {
            "$gte": 7,
            "$lte": 20
          }
        }
      ]
    }
  },

What this tool is not:

• not a universal converter
• only supports a subset
• not all constructs are properly handled
• generated statement is not guaranteed to run

Why is this tool valuable:

• no barrier to start
• immediate translation as you type
• works with all major desktop browsers

Demo:

• open page
• explain elements
• erase pre-filled sql and demo:

select name from users

select name = first + ' ' + last from users

• demonstrate how code is generated with "$concat" because there is text element, if there was a number, it would have used $add:

select total = price + tax from orders

select count(*) from table group by field
select count(*), max(price) from orders group by store, category

• demo expressions in group by

• demo expressions in select

  • demo how + and * are combined intelligently

• demo conditions, and how they are combined (multiple AND, multiple OR)

• demo IN

• demo BETWEEN

• demo JOIN and explain limitations

• demo ORDER BY and explain limitations

• demo TOP, SKIP and LIMIT

Conclusion

• not a production tool, but useful for education purposes. Makes constructing complex conditions hard