Query Code

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:

Updated on November 12, 2018

Was this article helpful?

Related Articles