Query Code查询代码

Posted on: 16/06/2018 (last updated: 09/03/2022) by Kathryn Vargas

tl;dr: 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, Ruby, and the mongo shell language in one click. 查询代码是Studio 3T中的代码生成功能，它可以将MongoDB、聚合和SQL查询一次性转换为JavaScript（Node.js）、Java(2.x和3.x驱动程序API）、Python、C#、PHP、Ruby和mongo shell语言。Try it today.
~~Shortcuts~~快捷键: ~~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:“查询代码”会自动将通过图形化查询生成器和聚合编辑器生成的MongoDB查询以及通过SQL查询生成的SQL查询转换为以下语言：

JavaScript (Node.js)
Python
Java (2.x and 3.x driver API)
C#
PHP
Ruby
~~The complete mongo shell language~~完整的mongo shell语言

~~Simply click on the Query Code tab to view the generated code.~~只需单击“查询代码”选项卡即可查看生成的代码。

►https://player.vimeo.com/video/681887647

~~So, again, where exactly is Query Code available?~~那么，“查询代码”到底在哪里？

~~Visual Query Builder~~图形化查询生成器 – ~~Studio 3T’s drag-and-drop MongoDB query builder.~~ Studio 3T的拖放MongoDB查询生成器。~~Execute MongoDB queries with no JSON required.~~执行MongoDB查询时不需要JSON。
~~SQL Query~~SQL查询 – ~~Query MongoDB with SQL statements and joins, and see how they translate to the mongo shell language.~~使用SQL语句和联接查询MongoDB，并查看它们如何转换为mongo shell语言。
~~Aggregation Editor~~聚合编辑器 – ~~Studio 3T’s aggregation pipeline builder. Write MongoDB aggregation queries in stages, apply operators, and view inputs/outputs at each step.~~Studio 3T的聚合管道生成器。分阶段编写MongoDB聚合查询，应用运算符，并在每个步骤查看输入/输出。

~~Want to know more about the four ways to query in Studio 3T? Here’s the guide.~~想知道更多关于Studio 3T中的四种查询方式吗？这是指南。

Open mongo shell code directly in IntelliShell直接在IntelliShell中打开mongo shell代码

~~You can open mongo shell code generated by Query Code directly in IntelliShell, Studio 3T’s built-in mongo shell.~~您可以直接在Studio 3T的内置mongo shell IntelliShell中打开由查询代码生成的mongo shell代码。

~~Simply click on the shell icon wherever Query Code is available, which is activated when mongo shell is chosen as the target language.~~只要在查询代码可用的地方单击shell图标，当选择mongo shell作为目标语言时，就会激活该图标。

Code examples代码示例

SQL to MongoDBSQL到MongoDB

~~Original SQL query:~~原始SQL查询：

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):~~MongoDB查询（将在下面的示例中使用）：

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:~~Node.js翻译：

// 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:~~Python翻译：

# 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:~~Java(2.x驱动程序API）翻译：

// 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:~~Java(3.x驱动程序API）翻译：

// 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";
}

MongoDB to Ruby

# Requires: Ruby v2.0+ and Mongo driver (gem install mongo)
require "mongo"
client = Mongo::Client.new(["host:port"], :database => "chicago")
collection = client[:housing]
# Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/
pipeline = [
    {
        "$group" => {
            :_id => {
                :zip_code => "$zip_code"
            },
            "COUNT(*)" => {
                "$sum" => 1
            },
            "MAX(units)" => {
                "$max" => "$units"
            }
        }
    },
    {
        "$project" => {
            "COUNT(*)" => "$COUNT(*)",
            "MAX(units)" => "$MAX(units)",
            :zip_code => "$_id.zip_code",
            :_id => 0
        }
    },
    {
        "$sort" => {
            "MAX(units)" => -1
        }
    }
]
options = {
    :allow_disk_use => true
}
cursor = collection.aggregate(pipeline, options)
cursor.each do |document|
    puts document[:_id]
end

~~Instant code? Check. What about these other time-saving features:~~即时代码？勾选。那么其他节省时间的功能呢：

IntelliShell – ~~Write MongoDB queries faster with Studio 3T’s mongo shell.~~ 使用Studio 3T的mongo shell更快地编写MongoDB查询。~~Autocomplete field names, collection names, shell methods, JavaScript standard library functions, and more.~~自动完成字段名、集合名、shell方法、JavaScript标准库函数等等。
~~Data Compare and Sync~~数据比较和同步 – ~~Compare any two MongoDB collections across databases and sync differences between the two.~~跨数据库比较任意两个MongoDB集合，并同步两者之间的差异。
~~Import and Export Wizard~~导入和导出向导 – ~~Import to and export from MongoDB in the following formats: JSON, CSV, SQL, BSON/mongodump, and another collection.~~以以下格式导入到和导出自MongoDB：JSON、CSV、SQL、BSON/mongodump和其他集合。