2013-01-22

SQL -> MongoDB Tips

For those of you migrating from SQL to Mongo, here are a few common types of queries. This list is very incomplete. But, I'll ne'er get the proper time I need to complete it.


Text Queries

When querying string, mongo relies on the very powerful RegEx format, and you can construct complex text queries that way.
·         {"Text_FieldName":"Exact value"}
·         {"Text_FieldName":/contains value/}

If you were to write the above queries in SQL, they might look like this:
·         SELECT * FROM Table WHERE Text_FieldName = ‘ExactValue’
·         SELECT * FROM Table WHERE Text_FieldName LIKE ‘%contains value%’

Numeric

When querying numbers, you can do equalities and operand comparisons (http://docs.mongodb.org/manual/reference/operators/)
·         Equals:
·         {"Numeric_FieldName":12345}
·         Not Equals:
·         {"Numeric_FieldName": { $ne: 20 }}
·         Less Than:
·         {"Numeric_FieldName": { $lt: 20 }}
·         Less Than or Equals:
·         {"Numeric_FieldName": { $lte: 20 }}
·         Greater Than:
·         {"Numeric_FieldName": { $gt: 20 }}
·         Greater Than or Equals:
·         {"Numeric_FieldName": { $gte: 20 }}

If you were to write the above queries in SQL, they might look like this:
·         SELECT * FROM Table WHERE Numeric_FieldName = 12345
·         SELECT * FROM Table WHERE Numeric_FieldName != 12345
·         SELECT * FROM Table WHERE Numeric_FieldName < 12345
·         SELECT * FROM Table WHERE Numeric_FieldName <= 12345
·         SELECT * FROM Table WHERE Numeric_FieldName > 12345
·         SELECT * FROM Table WHERE Numeric_FieldName >= 12345

Boolean

·         {"Boolean_FieldName":true}
·         {"Boolean_FieldName":false}
If you were to write the above queries in SQL, they might look like this:
·         SELECT * FROM Table WHERE Boolean_FieldName = true
·         SELECT * FROM Table WHERE Boolean_FieldName = false

DateTime

·         {"Date_FieldName": {"$gte": new Date(2010,3,1), "$lt": new Date(2011,3,1)}}
If you were to write the above queries in SQL, they might look like this:
·         SELECT * FROM Table WHERE Date_FieldName BETWEEN ‘2010-03-01’ AND ‘2011-03-01’

No comments: