RazorSQL MongoDB Syntax Guide
RazorSQL provides support for interacting with MongoDB databases via SQL and JSON
query sytnax. See below for the exact details of what is supported and what is
not supported for each syntax type.
SQL Support
JSON Support
RazorSQL also provides tools to aid in querying and updating MongoDB databases. Listed below
are some of these tools:
DB Tools -> Query Builder
DB Tools -> Edit -> Edit Collection Tool
DB Tools -> Create -> Create Collection Tool
Right-click in Query Results -> Edit or View JSON for Selected Row
RazorSQL MongoDB SQL Support
RazorSQL provides limited SQL support for MongoDB in the form of SQL select, insert, update,
and delete statements.
Select: RazorSQL supports selecting all attributes or a subset of attributes
from a collection. It does not support where clauses, joins, or order by clauses. There are more
query options when using the JSON syntax. See below for more information on the JSON syntax or use
the DB Tools -> Query Builder option to have RazorSQL automatically build JSON statements. Listed below
are some examples:
select * from database.collection
select att1, att2, ... attn from database.collection
Example: select fname, lname from test.employee
select * from collection (assumes a database has been specified when connecting or selected in the toolbar)
select att1, att2, . . . attn from collection (assumes a database has been specified when connecting or selected in the toolbar)
Insert: RazorSQL supports the following insert syntax:
insert into database.collection (attribute name list) values (value list)
The following data types are supported in insert statements:
numeric (integers or decimals)
string / varchar
date
Any other data type such as an array or embedded document will be treated as a string / varchar. To
insert this type of data, the JSON syntax should be used. See below for more information on the JSON
syntax. RazorSQL also includes a collection editor (DB Tools -> Edit -> Edit Collection) that can
be used to insert data.
Here is an example insert statement:
insert into database.collection (att1, att2, att3) values (1, 'Att1', 'date(01/01/2011)')
There are two types of syntax that can be used to insert dates. One is a RazorSQL specific
date format - 'date(MM/DD/YYYY)'. The other is the following format: 'YYYY-MM-DDTHH:MI:SS.mmmZ'. Whenever
RazorSQL sees a value in that format, it will consider it a date.
Example 1: insert into date_collection (att1) values ('date(02/01/2011)')
Example 2: insert into date_collection (att1) values ('2011-02-01T11:33:12.123Z')
Update: RazorSQL supports the following update syntax:
update database.collection set att1 = value1, att2 = value2, . . . attn = valuen where _id = 'id value'
The _id parameter needs to be included in the where clause. No other columns are are supported in the where clause.
Example: update test.collection1 set att1 = '5', att2 = 'test5' where _id = '4e82874052f9e293122b9ef4'
RazorSQL also has an edit collection tool (DB Tools -> Edit -> Edit Collection) and tools for editing
JSON directly on the query results.
Delete: RazorSQL supports the following delete syntax:
delete from database.collection where _id = 'id_val'
The _id parameter needs to be included in the where clause. No other columns are supported
in the where clause.
Example: delete from test.collection1 where _id = '4e82874052f9e293122b9ef4'
Delete can also be performed via the edit collection tool - DB Tools -> Edit -> Edit Collection.
RazorSQL JSON Syntax Support
RazorSQL supports the use of JSON syntax for querying, inserting, and removing data.
When constructing JSON, RazorSQL requires that attribute names be wrapped in quotes.
Example: "att1" : 5
The att1 signifies the attribute name and needs to be wrapped in quotes or a parsing error will occur.
For attribute values, whether or not a value is wrapped in quotes can determine whether the value is
numeric or a string / varchar value. For example, the following two examples represent different data:
Example 1 (numeric): "att1" : 5
Example 2 (string): "att1" : "5"
To represent a date value in JSON syntax, the following syntax should be used:
"att1" : { "$date" : "2012-02-01T05:11:00.000Z"}
The format of the date value should be the following: YYYY-MM-DDTHH:MI:SS.mmmZ
JSON Syntax Examples:
Note: To automatically generate the below queries, the DB Tools -> Query Builder tool can be used
Selects:
Example: (select * from employee where salary < 40000)
test.employee.find({"salary":{"$lt":40000}})
Example: (select * from employee where fname = 'John' and salary > 40000)
test.employee.find({"fname":"John","salary":{"$gt":40000}})
Example: (select * from employee where fname = 'John' or salary <= 40000)
test.employee.find({"$or":[{"fname":"John"},{"salary":{"$lte":40000}}]})
Example: (select * from employee where fname like '%John%')
test.employee.find({"fname":{"$regex":".*John.*","$options":'i'}})
Inserts
Example: ( insert into employee (ssn, fname, minit, lname, address, gender, salary, mgrssn, dept_no) values (123456780, 'John', 'B', 'Smith', '731 Fondren, Houston TX', 'M', 30000.0, 33445555, 5) )
test.employee.insert({"ssn" : 123456780 , "fname" : "John" , "minit" : "B" , "lname" : "Smith" , "address" : "731 Fondren, Houston TX" , "gender" : "M" , "salary" : 30000.0 , "mgrssn" : 333445555 , "dept_no" : 5});
Example: (date insert)
test.date_collection.insert({ "date" : { "$date" : "2011-07-24T04:00:00.000Z"}});
Example: (complex insert with array and embedded document)
test.sample.insert({"author" : "joe" , "title" : "Yet another blog post" , "tags" : [ "example" , "joe"] , "comments" : [ { "author" : "jim" , "comment" : "I disagree"} , { "author" : "nancy" , "comment" : "Good post"}]});
Deletes
Example (remove all rows)
test.employee.remove({})
Example (delete where n = 1)
test.employee.remove({"ssn" : 1234567890})
