- tl;dr
- Hotkeys
Query Code is the code generation feature in Studio 3T that converts MongoDB, aggregation, and SQL queries to JavaScript (Node.js), Java (2.x and 3.x driver API), Python, C#, PHP, and the mongo shell language in one click.
Generate query code - Ctrl + G (⌘+ G)
Basics
Query Code automatically translates MongoDB queries built through the Visual Query Builder and Aggregation Editor – as well as SQL queries built through SQL Query – to the following languages:
- JavaScript (Node.js)
- Python
- Java (2.x and 3.x driver API)
- C#
- PHP
- The complete mongo shell language
Simply click on the Query Code tab to view the generated code.
So, again, where exactly is Query Code available?
- Visual Query Builder – Studio 3T’s drag-and-drop MongoDB query builder. Execute MongoDB queries with no JSON required.
- SQL Query – Query MongoDB with SQL statements and joins, and see how they translate to the mongo shell language.
- Aggregation Editor – Studio 3T’s aggregation pipeline builder. Write MongoDB aggregation queries in stages, apply operators, and view inputs/outputs at each step.
Code examples
SQL to MongoDB
Original SQL query:
select count(*), max(units), zip_code from housing group by zip_code order by max(units) desc;
MongoDB query (to be used in the examples below):
db.getCollection("housing").aggregate(
[
{
"$group" : {
"_id" : {
"zip_code" : "$zip_code"
},
"COUNT(*)" : {
"$sum" : NumberInt(1)
},
"MAX(units)" : {
"$max" : "$units"
}
}
},
{
"$project" : {
"_id" : NumberInt(0),
"COUNT(*)" : "$COUNT(*)",
"MAX(units)" : "$MAX(units)",
"zip_code" : "$_id.zip_code"
}
},
{
"$sort" : {
"MAX(units)" : NumberInt(-1)
}
},
{
"$project" : {
"_id" : NumberInt(0),
"COUNT(*)" : "$COUNT(*)",
"MAX(units)" : "$MAX(units)",
"zip_code" : "$zip_code"
}
}
]
);
MongoDB to Node.js
Node.js translation:
// Requires official Node.js MongoDB Driver 3.0.0+
var mongodb = require("mongodb");
var client = mongodb.MongoClient;
var url = "mongodb://host:port/";
client.connect(url, function (err, client) {
var db = client.db("demo-sql");
var collection = db.collection("housing");
var options = {
allowDiskUse: false
};
var pipeline = [
{
"$group": {
"_id": {
"zip_code": "$zip_code"
},
"COUNT(*)": {
"$sum": 1
},
"MAX(units)": {
"$max": "$units"
}
}
},
{
"$project": {
"_id": 0,
"COUNT(*)": "$COUNT(*)",
"MAX(units)": "$MAX(units)",
"zip_code": "$_id.zip_code"
}
},
{
"$sort": {
"MAX(units)": -1
}
},
{
"$project": {
"_id": 0,
"COUNT(*)": "$COUNT(*)",
"MAX(units)": "$MAX(units)",
"zip_code": "$zip_code"
}
}
];
var cursor = collection.aggregate(pipeline, options);
cursor.forEach(
function(doc) {
console.log(doc);
},
function(err) {
client.close();
}
);
// Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/
});
MongoDB to Python
Python translation:
# Requires pymongo 3.6.0+
from bson.son import SON
from pymongo import MongoClient
client = MongoClient("mongodb://host:port/")
database = client["demo-sql"]
collection = database["housing"]
# Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/
pipeline = [
{
u"$group": {
u"_id": {
u"zip_code": u"$zip_code"
},
u"COUNT(*)": {
u"$sum": 1
},
u"MAX(units)": {
u"$max": u"$units"
}
}
},
{
u"$project": {
u"_id": 0,
u"COUNT(*)": u"$COUNT(*)",
u"MAX(units)": u"$MAX(units)",
u"zip_code": u"$_id.zip_code"
}
},
{
u"$sort": SON([ (u"MAX(units)", -1) ])
},
{
u"$project": {
u"_id": 0,
u"COUNT(*)": u"$COUNT(*)",
u"MAX(units)": u"$MAX(units)",
u"zip_code": u"$zip_code"
}
}
]
cursor = collection.aggregate(
pipeline,
allowDiskUse = False
)
try:
for doc in cursor:
print(doc)
finally:
client.close()
MongoDB to Java (2.x driver API)
Java (2.x driver API) translation:
// Requires official Java MongoDB Driver 2.14+
import com.mongodb.AggregationOptions;
import com.mongodb.BasicDBObject;
import com.mongodb.Cursor;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBObject;
import com.mongodb.MongoClient;
import java.util.Arrays;
import java.util.List;
public class Program {
public static void main(String[] args) {
try {
MongoClient client = new MongoClient("localhost", 27017);
try {
DB database = client.getDB("demo-sql");
DBCollection collection = database.getCollection("housing");
// Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/
List pipeline = Arrays.asList(
new BasicDBObject()
.append("$group", new BasicDBObject()
.append("_id", new BasicDBObject()
.append("zip_code", "$zip_code")
)
.append("COUNT(*)", new BasicDBObject()
.append("$sum", 1)
)
.append("MAX(units)", new BasicDBObject()
.append("$max", "$units")
)
),
new BasicDBObject()
.append("$project", new BasicDBObject()
.append("_id", 0)
.append("COUNT(*)", "$COUNT(*)")
.append("MAX(units)", "$MAX(units)")
.append("zip_code", "$_id.zip_code")
),
new BasicDBObject()
.append("$sort", new BasicDBObject()
.append("MAX(units)", -1)
),
new BasicDBObject()
.append("$project", new BasicDBObject()
.append("_id", 0)
.append("COUNT(*)", "$COUNT(*)")
.append("MAX(units)", "$MAX(units)")
.append("zip_code", "$zip_code")
)
);
AggregationOptions options = AggregationOptions.builder()
// always use cursor mode
.outputMode(AggregationOptions.OutputMode.CURSOR)
.allowDiskUse(false)
.build();
Cursor cursor = collection.aggregate(pipeline, options);
while (cursor.hasNext()) {
BasicDBObject document = (BasicDBObject) cursor.next();
System.out.println(document.toString());
}
} finally {
client.close();
}
} catch (Exception e) {
// handle exception
}
}
}
MongoDB to Java (3.x driver API)
Java (3.x driver API) translation:
// Requires official Java MongoDB Driver 3.6+
import com.mongodb.Block;
import com.mongodb.MongoClient;
import com.mongodb.MongoException;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import java.util.Arrays;
import java.util.List;
import org.bson.Document;
import org.bson.conversions.Bson;
public class Program {
public static void main(String[] args) {
try (MongoClient client = new MongoClient("localhost", 27017)) {
MongoDatabase database = client.getDatabase("demo-sql");
MongoCollection collection = database.getCollection("housing");
// Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/
Block processBlock = new Block() {
@Override
public void apply(final Document document) {
System.out.println(document);
}
};
List<? extends Bson> pipeline = Arrays.asList(
new Document()
.append("$group", new Document()
.append("_id", new Document()
.append("zip_code", "$zip_code")
)
.append("COUNT(*)", new Document()
.append("$sum", 1)
)
.append("MAX(units)", new Document()
.append("$max", "$units")
)
),
new Document()
.append("$project", new Document()
.append("_id", 0)
.append("COUNT(*)", "$COUNT(*)")
.append("MAX(units)", "$MAX(units)")
.append("zip_code", "$_id.zip_code")
),
new Document()
.append("$sort", new Document()
.append("MAX(units)", -1)
),
new Document()
.append("$project", new Document()
.append("_id", 0)
.append("COUNT(*)", "$COUNT(*)")
.append("MAX(units)", "$MAX(units)")
.append("zip_code", "$zip_code")
)
);
collection.aggregate(pipeline)
.allowDiskUse(false)
.forEach(processBlock);
} catch (MongoException e) {
// handle MongoDB exception
}
}
}
MongoDB to C#
C# translation:
// Requires official C# and .NET MongoDB Driver 2.5+
using MongoDB.Bson;
using MongoDB.Driver;
using System;
using System.Threading.Tasks;
namespace MongoDBQuery
{
class Program
{
static async Task _Main()
{
IMongoClient client = new MongoClient("mongodb://host:port/");
IMongoDatabase database = client.GetDatabase("demo-sql");
IMongoCollection collection = database.GetCollection("housing");
// Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/
var options = new AggregateOptions() {
AllowDiskUse = false
};
PipelineDefinition<BsonDocument, BsonDocument> pipeline = new BsonDocument[]
{
new BsonDocument("$group", new BsonDocument()
.Add("_id", new BsonDocument()
.Add("zip_code", "$zip_code")
)
.Add("COUNT(*)", new BsonDocument()
.Add("$sum", 1)
)
.Add("MAX(units)", new BsonDocument()
.Add("$max", "$units")
)),
new BsonDocument("$project", new BsonDocument()
.Add("_id", 0)
.Add("COUNT(*)", "$COUNT(*)")
.Add("MAX(units)", "$MAX(units)")
.Add("zip_code", "$_id.zip_code")),
new BsonDocument("$sort", new BsonDocument()
.Add("MAX(units)", -1)),
new BsonDocument("$project", new BsonDocument()
.Add("_id", 0)
.Add("COUNT(*)", "$COUNT(*)")
.Add("MAX(units)", "$MAX(units)")
.Add("zip_code", "$zip_code"))
};
using (var cursor = await collection.AggregateAsync(pipeline, options))
{
while (await cursor.MoveNextAsync())
{
var batch = cursor.Current;
foreach (BsonDocument document in batch)
{
Console.WriteLine(document.ToJson());
}
}
}
}
static void Main(string[] args)
{
_Main().Wait();
}
}
}
MongoDB to PHP
PHP translation:
<?php /** * Requires: * - PHP MongoDB Extension (https://php.net/manual/en/set.mongodb.php) * - MongoDB Library (`composer require mongodb/mongodb`) */ require 'vendor/autoload.php'; use MongoDB\Client; // Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/ $client = new Client('mongodb://localhost:27017'); $collection = $client->{'demo-sql'}->housing;
$options = [];
$pipeline = [
[
'$group' => [
'_id' => [
'zip_code' => '$zip_code'
],
'COUNT(*)' => [
'$sum' => 1
],
'MAX(units)' => [
'$max' => '$units'
]
]
], [
'$project' => [
'_id' => 0,
'COUNT(*)' => '$COUNT(*)',
'MAX(units)' => '$MAX(units)',
'zip_code' => '$_id.zip_code'
]
], [
'$sort' => [
'MAX(units)' => -1
]
], [
'$project' => [
'_id' => 0,
'COUNT(*)' => '$COUNT(*)',
'MAX(units)' => '$MAX(units)',
'zip_code' => '$zip_code'
]
]
];
$cursor = $collection->aggregate($pipeline, $options);
foreach ($cursor as $document) {
echo $document['_id'] . "\n";
}
Instant code? Check. What about these other time-saving features:
- IntelliShell – Write MongoDB queries faster with Studio 3T’s mongo shell. Autocomplete field names, collection names, shell methods, JavaScript standard library functions, and more.
- Data Compare and Sync – Compare any two MongoDB collections across databases and sync differences between the two.
- Import and Export Wizard – Import from and export to MongoDB in the following formats: JSON, CSV, SQL, BSON/mongodump, and another collection.
