Features and SQL Support for the JDBC Driver for MongoDB®

The JDBC driver for MongoDB is free to download and supports SQL queries. The JDBC driver works with on-premise and cloud hosted MongoDB including MongoDB Atlas, and with common applications such as Tableau, Splunk, SAP Lumira, and any other query or reporting software that supports JDBC. The following table shows which SQL queries can be handled directly by the JDBC driver for MongoDB. The trial version of the JDBC driver for MongoDB has all features and no row limits. After the trial period expires, if your query produces more than 100 results, upgrade your UnityJDBC/MongoDB JDBC license here.

Collection prototype example for nation collection.
{
    "_id" : ObjectId("51929a0ee4b03d5485de04a9"),
    "n_nationkey" : 24,
    "n_name" : "UNITED STATES",
    "n_regionkey" : 1,
    "n_comment" : "Largest country by population in North America."
}
Collection prototype example for region collection.
{
    "_id" : ObjectId("519299f6e4b03d5485de048d"),
    "r_regionkey" : 1,
    "r_name" : "AMERICA",
    "r_comment" : "Includes Canada, United States, and Mexico."
}
Collection prototype example for georef collection (includes arrays and nested documents).
{
    "_id" : ObjectId("519299f6e4b03d5485de05af"),
    "g_city" : "Vancouver",
    "g_loc" :
        {
            "lat" : 49.2505,
            "lon" : -123.1119,
            "info" : "a coastal seaport city on the mainland of British Columbia, Canada"
        },
    "g_weather" : ["light rain", "rain", "heavy rain", "snow", "sun"]
}

Important Notes:

  • Case-sensitive identifiers: SQL identifiers (table and field names) are interpreted as case-sensitive as they are case-sensitive in MongoDB. Use delimited-identifiers (with double-quotes) to force case-sensitivity in SQL tools and always use double-quotes for SQL reserved words (e.g. "select"). Example: SELECT a, A, "a", "A" FROM "Table" This will query a Mongo collection named Table (capital T is important) which has fields a and A. Refer to a using either a or "a". Refer to A using either A or "A".

  • SQL functions: Common SQL functions including string functions (concat, substr, instr), date functions (e.g. dateadd, datediff, day), trigonometry functions, mathematical functions (e.g. abs), bit functions, and others are supported. Complete list of SQL functions supported.
    Example:
    SELECT concat('Clerk: ', UPPER(o_clerk)), o_totalprice5+3, dateadd('yy', 3, o_orderdate) AS orderDatePlus3Years,
    CASE WHEN o_orderkey > 100 THEN 0 ELSE 1 END AS caseExample  FROM orders

  • JDBC DatabaseMetaData and ResultSetMetaData Support: The JDBC Driver for MongoDB supports the JDBC API allowing interoperability with database software using SQL and JDBC. This includes support for DatabaseMetaData and ResultSetMetaData. Since MongoDB does not have a schema, a schema is built on connection and can be cached (see Connection Parameters for more details). For DatabaseMetaData, common API methods used include getTables, getColumns, getPrimaryKeys, getIndexInfo, getDatabaseMajorVersion, among others.

  • Transactions, Connection Pooling, and DataSource Support: The JDBC Driver for MongoDB supports the JDBC API interfaces DataSource, ConnectionPoolDataSource, and XADataSource. It will also accept transaction requests such as commit and rollback. However, since MongoDB does not support transactions the driver silently ignores transaction operations and any update is automatically committed as done by MongoDB.
Feature SQL Example MongoDB Statement JDBC Driver for MongoDB UnityJDBC Virtualization Support
SELECT Clause
Select all fields from one table SELECT *
FROM nation 		db.nation.find()
Select a list of fields from one table SELECT n_nationkey, n_name
FROM nation 		db.nation.find( { },
 {"n_nationkey" : 1, "n_name" : 1,
  "_id" : 0})
Select with field aliases SELECT n_nationkey AS nkey,
       n_name name
FROM nation 		db.nation.find( { },
 {"n_nationkey" : 1, "n_name" : 1,
  "_id" : 0})
note: driver will rename before returning results.
Select expressions SELECT n_nationkey + 1,
    'name:'+ n_name AS name
FROM nation
Select distinct SELECT DISTINCT n_regionkey
FROM nation 		db.nation.distinct("n_regionkey", { })
Details
Details
FROM Clause
One table query SELECT *
FROM nation 		db.nation.find()
Multiple table query SELECT *
FROM nation n, region r
WHERE n.n_regionkey =
    r.r_regionkey
Inner join clause SELECT *
FROM nation n
INNER JOIN region r
ON n.n_regionkey =
    r.r_regionkey
Left outer join clause SELECT *
FROM nation n
LEFT OUTER JOIN region r
ON n.n_regionkey =
    r.r_regionkey
Right outer join clause SELECT *
FROM nation n
RIGHT OUTER JOIN region r
ON n.n_regionkey =
    r.r_regionkey
Full outer join clause SELECT *
FROM nation n
FULL OUTER JOIN region r
ON n.n_regionkey =
    r.r_regionkey
WHERE Clause
Equality (=) comparison SELECT *
FROM nation
WHERE n_name = 'UNITED STATES' 		db.nation.find(
 {"n_name" : "UNITED STATES"},
 { })
Greater than equal (>=) comparison SELECT *
FROM nation
WHERE n_name >= 'UNITED STATES' 		db.nation.find(
 {"n_name" : {$gte: "UNITED STATES"}},
 { })
Greater than (>) comparison SELECT *
FROM nation
WHERE n_name > 'UNITED STATES' 		db.nation.find(
 {"n_name" : {$gt: "UNITED STATES"},
 { })
Less than equal (<=) comparison SELECT *
FROM nation
WHERE n_name <= 'UNITED STATES' 		db.nation.find(
 {"n_name" : {$lte: "UNITED STATES"}}
 { })
Less than (<) comparison SELECT *
FROM nation
WHERE n_nationkey < 15 		db.nation.find(
 {"n_nationkey" : {$lt: 15}}
 { })
Not equal (!= or <>) comparison SELECT *
FROM nation
WHERE n_nationkey != 5 		db.nation.find(
 {"n_nationkey" : {$ne: 5}}
 { })
Boolean (True/False) Comparison SELECT *
FROM nation
WHERE flag = FALSE 		db.nation.find(
 { "flag" : false},
 { })
Like comparison SELECT *
FROM nation
WHERE n_name LIKE '%A%' 		db.nation.find(
 { "n_name" : { "$regex" : "^.*A.*$"}},
 { })
Two attribute comparison SELECT n_name
FROM nation
WHERE n_name = n_comment 		db.nation.find(
 { "$where" :
    "this.n_name == this.n_comment"},
 { "n_name" : 1 , "_id" : 0})
Negation SELECT *
FROM nation
WHERE NOT (n_nationkey = 3) 		db.region.find(
 { "n_regionkey" : { "$ne" : 3}},
 { })
AND and OR SELECT *
FROM nation
WHERE n_nationkey > 3 AND
 (n_name < 'F' OR
  n_name > 'J') 		db.region.find(
 {"n_nationkey" : { "$gt" : 3},
  "$or" : [{"n_name" : { "$lt" : "F"}},
           {"n_name" : { "$gt" : "J"}}]},
 { })
NOT LIKE comparison SELECT *
FROM region
WHERE r_name NOT LIKE 'MIDD%' 		db.region.find(
 { "r_name" :
  { "$not" : { "$regex" : "^MIDD.*$"}}},
 { })
Date and Timestamp Support
Date comparison with constant date value SELECT *
FROM tbl
WHERE datefld = '2014-07-04' 		db.tbl.find(
 { "datefld" :
  { "$date" : "2014-07-04T00:00:00.000Z"}},
 { })
Datetime comparison with constant datetime value SELECT *
FROM tbl
WHERE datefld
 = '2014-07-04 13:35:42' 		db.tbl.find(
 { "datefld" :
  { "$date" : "2014-07-04T13:35:42.000Z"}},
 { })
Force string comparison for date constant SELECT *
FROM tbl
WHERE datestr
 = str('2014-07-04 13:35:42') 		db.tbl.find(
 { "datefld" : "2014-07-04 13:35:42"}},
 { }) 		Details Details
DateTime comparison as keyword SELECT *
FROM region
WHERE r_comment = CURRENT_TIMESTAMP
DateTime comparison as function SELECT *
FROM region
WHERE r_comment = CURRENT_TIMESTAMP()
Select DateTime as keyword SELECT CURRENT_TIMESTAMP
Select DateTime as function SELECT CURRENT_TIMESTAMP()
group by Clause
Group by one attribute SELECT n_regionkey, COUNT(*) cnt
FROM nation
GROUP BY n_regionkey 		db.nation.aggregate([{ "$group" :
 {"_id" : { "n_regionkey" : "$n_regionkey"},
  "cnt" : { "$sum" : 1}}}])
Group by multiple attributes SELECT n_regionkey, n_name, COUNT(*)
FROM nation
GROUP BY n_regionkey, n_name 		db.nation.aggregate([{ "$group" :
 {"_id" : { "n_regionkey" : "$n_regionkey",
            "n_name" : "$n_name"},
  "cnt" : { "$sum" : 1}}}])
Aggregate Functions
Count(*) SELECT COUNT(*)
FROM nation 		db.nation.find({ }, { }).count()
Count(attribute) SELECT COUNT(n_regionkey)
FROM nation 		db.nation.find(
 {"n_nationkey" : {"$exists" : true}},
 { }).count()
Maximum SELECT MAX(n_regionkey) MAX
FROM nation 		db.nation.aggregate([{ "$group" :
 {"_id" :  null,
  "max" : { "$max" : "$n_regionkey"}}}])
Minimum SELECT MIN(n_regionkey) MIN
FROM nation 		db.nation.aggregate([{ "$group" :
 {"_id" :  null,
  "min" : { "$min" : "$n_regionkey"}}}])
Summation SELECT SUM(n_regionkey) SUM
FROM nation 		db.nation.aggregate([{ "$group" :
 {"_id" :  null,
  "sum" : { "$sum" : "$n_regionkey"}}}])
Average SELECT avg(n_regionkey) avg
FROM nation 		db.nation.aggregate([{ "$group" :
 {"_id" :  null,
  "avg" : { "$avg" : "$n_regionkey"}}}])
Having Clause
Having clause SELECT n_regionkey, COUNT(*)
FROM nation
GROUP BY n_regionkey
HAVING COUNT(*) > 4
Order by Clause
Order by one attribute SELECT *
FROM nation
ORDER BY n_name ASC 		db.nation.find().sort(
 {"n_name" : 1})
Order by multiple attributes SELECT *
FROM nation
ORDER BY n_name ASC,
    n_nationkey DESC 		db.nation.find().sort(
 {"n_name": 1, "n_nationkey" : -1}
)
Order by expression SELECT *
FROM nation
ORDER BY n_nationkey+5 ASC
Limit/Offset Clause
Limit clause
(single collection)		 SELECT *
FROM nation
LIMIT 5 		db.nation.find().limit(5)
Offset clause
(single collection)		 SELECT *
FROM nation
LIMIT 5
offset 2 		db.nation.find().limit(5).skip(2)
Limit clause
(multiple collection)		 SELECT *
FROM nation INNER JOIN region
  ON n_regionkey = r_regionkey
LIMIT 5
Offset clause
(multiple collection)		 SELECT *
FROM nation INNER JOIN region
  ON n_regionkey = r_regionkey
LIMIT 5
offset 2
Union Clause
Two query union SELECT r_regionkey
FROM region
UNION SELECT n_regionkey
FROM nation
Delete Statement
Delete with where predicate (int) DELETE FROM region
WHERE r_regionkey > 3 		db.region.remove(
 {"r_regionkey" : {$gt : 3}})
Delete with where predicate (string) DELETE FROM region
WHERE r_name = 'ASIA' 		db.region.remove(
 {"r_name" : "ASIA"})
Insert Statement
Insert with specified field list INSERT INTO region
 (r_regionkey, r_name)
VALUES (6, 'ANTARTICA') 		db.region.insert(
 {"r_regionkey" : 6,
  "r_name" : "ANTARTICA"})
Details
Update Statement
Update an integer field to a constant value UPDATE region
SET r_regionkey = 6
WHERE r_regionkey = 3 		db.region.update(
 {"r_regionkey" : 3},
 {$set: {"r_regionkey": 6}},
 {"multi" : true})
Update a string field to a constant value UPDATE region
SET r_name = 'ANTARTICA'
WHERE r_regionkey = 3 		db.region.update(
 {"r_regionkey" : 3},
 {$set: {"r_name" : "ANTARTICA"}},
 {"multi" : true})
Insert Statement with JSON
Insert with JSON Object INSERT INTO region
VALUES (
'{ "r_regionkey" : 6,
"r_name" : "ANTARTICA"}') 		db.region.insert(
 {"r_regionkey" : 6,
  "r_name": "ANTARTICA"})
Details
Details
Insert with JSON Object into a field INSERT INTO region
 (r_regionkey, r_name)
VALUES (6,
'{ "loc" : "ANTARTICA"}') 		db.region.insert(
 {"r_regionkey" : 6,
  "r_name" : {"loc": "ANTARTICA"}})
Details
Details
UPSERT (uses MongoDB save())
UPSERT
(no _id given)		 UPSERT INTO region
  (r_regionkey, r_name)
  VALUES (6, 'ANTARTICA') 		db.region.save(
    {"r_regionkey" : 6,
    "r_name": "ANTARTICA"})
UPSERT
(_id given)		 UPSERT INTO region
  (_id, r_regionkey, r_name)
  VALUES (6, 6, 'ANTARTICA') 		db.region.save(
    {"_id" : 6, "r_regionkey" : 6,
    "r_name" : "ANTARTICA"})
Nested Collections
Select with nested collections SELECT g_city, g_loc.lat
FROM georef 		db.georeg.find({ },
 {"g_city" : 1, "g_loc.lat" : 1,
  "_id" : 0})
Select with nested collection (using SQL delimited identifier to specify nested object) SELECT g_city, "g_loc.lat"
FROM georef 		db.georeg.find({ },
 {"g_city" : 1, "g_loc.lat" : 1,
  "_id" : 0})
Select with nested attribute in predicate SELECT g_city, g_loc.lat
FROM georef
WHERE g_loc.lat > 48 		db.georeg.find(
{"g_loc.lat" : {$gt : 48}},
{"g_city" : 1, "g_loc.lat" : 1,
 "_id" : 0})
Collections with Arrays
Select with Array SELECT *
FROM georef
WHERE g_weather = 'rain' 		db.georeg.find(
 {"g_weather" : "rain"},
 { })
Select with Array (IN syntax) SELECT *
FROM georef
WHERE 'rain' IN g_weather 		db.georeg.find(
 {"g_weather" : { "$regex" : "^rain$"},
 { })
Details
Select with Multiple Array Elements SELECT *
FROM georef
WHERE g_weather = 'rain'
    AND g_weather = 'sun' 		db.georeg.find(
 { "$and" :
    [{ "g_weather" : "rain"},
     { "g_weather" : "sun"}
    ]
 }, { })
Select on Object Inside an Array SELECT _id, "value", "value.num"
FROM arraymixedobject
WHERE "value.num" = 5 		db.arraymixedobject.find(
     { "value.num" : 5},
     { "_id" : 1 , "value" : 1})
Select on Object Inside an Array using Array Subscripts SELECT _id, "value", "value.num"
FROM arraymixedobject
WHERE "value.2.num" = 5 		db.arraymixedobject.find(
     { "value.2.num" : 5},
     { "_id" : 1 , "value" : 1}) 		Details Details
Retrieve individual array elements by subscript SELECT _id, "value", "value.0", "value.1", "value.5"
FROM arraymixedobject
WHERE "value.2.num" = 5 		db.arraymixedobject.find(
 { "value.2.num" : 5},
 { "_id" : 1 , "value" : 1}) 		Details Details
Query objects that contain arrays (nested arrays and objects) SELECT num, "value.0", "value.1.num2", "value.value2", "value.1.value2.value3", "value.1.value2.1.value3"
FROM nestedarray
WHERE
  "value.1.value2.value3" < 2 		db.nestedarray.find(
 { "value.1.value2.value3" : { "$lt" : 2}},
 { "num" : 1 , "value" : 1 , "_id" : 0}) 		Details Details
DROP Table/Collection
DROP Table/Collection DROP TABLE region db.region.drop()
Creating and Dropping Indexes
CREATE INDEX CREATE INDEX testIdx
ON region (r_regionkey) 		db.region.ensureIndex(
 {"r_regionkey" : 1}, {"name" : "testIdx"})
CREATE UNIQUE INDEX
(multiple fields)		 CREATE UNIQUE INDEX testIdx ON region (r_regionkey DESC, r_name ASC) db.region.ensureIndex(
 {"r_regionkey" : 1, "r_name" : 1},
 {"unique" : true, "name" : "testIdx"})
CREATE HASH INDEX CREATE UNIQUE INDEX idx
ON region (r_name)
USING HASH 		db.region.ensureIndex(
 {"r_name" : "hashed"}, { "name" : "idx"})
DROP INDEX DROP INDEX idx ON region db.region.dropIndex("idx")
User Management
Add a User INSERT INTO system.users
("user", pwd, roles) VALUES
('newuser', 'newpass',
 '["readWrite", "dbAdmin"]') 		db.system.users.insert(
{"user" : "newuser" \,
 "pwd":"6e6c5af031665ca6299532edb04657c0",
 "roles" : ["readWrite" , "dbAdmin"]})
Remove (delete) a User DELETE FROM system.users
WHERE "user" = 'newuser' 		db.system.users.remove(
{ "user" : "newuser"})
Change a User's Password UPDATE system.users
SET pwd = 'abc'
WHERE "user" = 'newuser' 		db.system.users.update(
{"user" : "newuser"},
{"$set" :
{"pwd":"60f037dbe185971b4ec039d442f4662b"}})
Change a User's Password and Role (permissions) UPDATE system.users
SET pwd = 'abc',
roles = '["readWrite"]'
WHERE "user" = 'newuser' 		db.system.users.update(
{"user" : "newuser"},
{"$set" :
{"pwd":"60f037dbe185971b4ec039d442f4662b",
  "roles" : [ "readWrite"]}})
Note that the field user is a reserved SQL word which is why it is in double-quotes. This is optional. For more information on MongoDB permissions see MongoDB Security, Access Control, and User Permissions.

Handling Nested Collections and Arrays

The MongoDB JDBC driver supports collections that contain subobjects and arrays. Automatic flattening is performed to simplify data access. If collection data has an attribute array, that is an array, two tables are available in the relational schema:

  • data that contains all data unflattened
  • data_array that contains the base attributes as well as a distinct row for each value in the array
Subobjects are also flattened so that each attribute of the object has its own column. Multiple levels of arrays are flattened as well.

Example: A Collection arrayint Containing an Array of Numbers

Sample JSON Document

{
    "_id"    : ObjectId("51eac61d1bce84818dc789b9"),
    "num"    : 1,
    "name"   : "1",
    "values" : [1, 2, 3, 4, 5, 6]
}

arrayint Table

_idnumnamevalues (array)
51eac61d1bce84818dc789b911[1, 2, 3, 4, 5, 6]

arrayint_values Table

_idnumnamevalues (int)
51eac61d1bce84818dc789b9111
51eac61d1bce84818dc789b9112
51eac61d1bce84818dc789b9113
51eac61d1bce84818dc789b9114
51eac61d1bce84818dc789b9115
51eac61d1bce84818dc789b9116


Example: A Collection arrayobj Containing an Array of Objects

Sample JSON Document

{
    "_id"    : ObjectId("51eaca001bceddc428c7761b"),
    "values" : [{ "num" : 1 , "name" : 67}, { "num" : 2 , "name" : 68}, { "num" : 3 , "name" : 69},
                { "num" : 4 , "name" : 70}, { "num" : 5 , "name" : 71}, { "num" : 6 , "name" : 72}]
}

arrayobj Table

_idnumnamevalues (array)
51eaca001bceddc428c7761b11[{ "num" : 1 , "name" : 67}, { "num" : 2 , "name" : 68}, { "num" : 3 , "name" : 69},
{ "num" : 4 , "name" : 70}, { "num" : 5 , "name" : 71}, { "num" : 6 , "name" : 72}]

arrayobj_values Table

_idnumname
51eaca001bceddc428c7761b167
51eaca001bceddc428c7761b268
51eaca001bceddc428c7761b369
51eaca001bceddc428c7761b470
51eaca001bceddc428c7761b571
51eaca001bceddc428c7761b672

Example: A Collection nest4 containing Multiple Levels of Nested Arrays and Object

Sample JSON Document

{
    "_id"     : ObjectId("542ec0c9486e886dd6a2238d"),
    "anum"    : 1,
    "aname"   : "a1",
    "avalues" : [{ "bnum" : 11 , "bname" : "b11" , "bvalues" :
                    [ { "cnum" : 111 , "cname" : "c111" , "cvalues" :
                            [ { "dnum" : 1111 , "dname" : "d1111"} , { "dnum" : 2111 , "dname" : "d2111"} ,
                              { "dnum" : 3111 , "dname" : "d3111"}]} ,
                      { "cnum" : 211 , "cname" : "c211" , "cvalues" :
                            [ { "dnum" : 1211 , "dname" : "d1211"} , { "dnum" : 2211 , "dname" : "d2211"} ,
                              { "dnum" : 3211 , "dname" : "d3211"}]}]},
                 { "bnum" : 21 , "bname" : "b21" , "bvalues" :
                    [ { "cnum" : 121 , "cname" : "c121" , "cvalues" :
                            [ { "dnum" : 1121 , "dname" : "d1121"} , { "dnum" : 2121 , "dname" : "d2121"} ,
                              { "dnum" : 3121 , "dname" : "d3121"}]} ,
                      { "cnum" : 221 , "cname" : "c221" , " cvalues" :
                            [ { "dnum" : 1221 , "dname" : "d1221"} , { "dnum" : 2221 , "dname" : "d2221"} ,
                              { "dnum" : 3221 , "dname" : "d3221"}]}]},
                 { "bnum" : 31 , "bname" : "b31" , "bvalues" :
                    [ { "cnum" : 131 , "cname" : "c131" , "cvalues" :
                            [ { "dnum" : 1131 , "dname" : "d1131"} , { "dnum" : 2131 , "dname" : "d2131"} ,
                              { "dnum" : 3131 , "dname" : "d3131"}]} ,
                      { "cnum" : 231 , "cname" : "c231" , "cvalues" :
                            [ { "dnum" : 1231 , "dname" : "d1231"} , { "dnum" : 2231 , "dname" : "d2231"} ,
                              { "dnum" : 3231 , "dname" : "d3231"}]}]}]
}

nest4 Table

_idanumanameavalues (array)
542ec0c9486e886dd6a2238d1a1(full JSON of avalues)

nest4_avalues Table

_idanumanamebnumbname
542ec0c9486e886dd6a2238d1a111b11
542ec0c9486e886dd6a2238d1a121b21
542ec0c9486e886dd6a2238d1a131b31

nest4_avalues_bvalues Table

_idanumanamebnumbnamecnumcname
542ec0c9486e886dd6a2238d1a111b11111c111
542ec0c9486e886dd6a2238d1a111b11211c211
542ec0c9486e886dd6a2238d1a121b21121c121
542ec0c9486e886dd6a2238d1a121b21221c221
542ec0c9486e886dd6a2238d1a131b31131c131
542ec0c9486e886dd6a2238d1a131b31231c231

nest4_avalues_bvalues_cvalues Table

_idanumanamebnumbnamecnumcnamednumdname
542ec0c9486e886dd6a2238d1a111b11111c1111111d1111
542ec0c9486e886dd6a2238d1a111b11111c1112111d2111
542ec0c9486e886dd6a2238d1a111b11111c1113111d3111
542ec0c9486e886dd6a2238d1a111b11211c2111211d1211
542ec0c9486e886dd6a2238d1a111b11211c2112211d2211
542ec0c9486e886dd6a2238d1a111b11211c2113211d3211
...

Mongo and MongoDB are trademarks of 10gen, Inc.