MongoDBMongoDB

若要设置 MongoDB 连接,请提供连接字符串和数据库名称 。To set up a MongoDB connection, provide a Connection String and a DB Name.

连接字符串Connection String

  • 最简单的情况:mongodb://username:password@hostname:port/dbnameThe simplest: mongodb://username:password@hostname:port/dbname
  • 启用了 SSL 的情况:mongodb://username:password@hostname:port/dbname?ssl=trueWith SSL enabled: mongodb://username:password@hostname:port/dbname?ssl=true
  • 启用了 SSL 且具有自签名证书(禁用证书验证)的情况:mongodb://username:password@hostname:port/dbname?ssl=true&ssl_cert_reqs=CERT_NONEWith SSL enabled and self-signed certificates (disables certificate verification): mongodb://username:password@hostname:port/dbname?ssl=true&ssl_cert_reqs=CERT_NONE

如果需要,可在查询字符串中传入其他连接选项。If needed, you can pass additional connection options in the query string. 请在[连接选项](https://docs.mongodb.com/manual/reference/connection-string /#connection-options)中查看完整详细信息。See full details in [Connection Options](https://docs.mongodb.com/manual/reference/connection-string /#connection-options).

你可能注意到了数据源配置中有一个单独的数据库名称字段,我们也会将它包含在连接字符串中。You might notice that there is a separate field for the DB Name in the data source configuration and we also include it in the connection string. 通常,需要在 MLab 等共享主机上这样做。This is usually required on shared hosts like MLab.

MongoDB AtlasMongoDB Atlas

免费层帐户位于连接到 MongoDB Atlas 的共享环境,因此可能会出现问题。Because they are on a shared environment connecting to MongoDB Atlas free tier accounts can be problematic. 为了获得最佳结果,请使用以下格式的连接字符串:For best results, use a connection string of the format:

mongodb+srv://<user>:<password>@<subdomain>.mongodb.net/<database>?retryWrites=true

疑难解答Troubleshooting

错误:“SSL 握手失败: [SSL:CERTIFICATE_VERIFY_FAILED] 证书验证失败”Error: “SSL handshake failed: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed”

MongoDB 服务器使用自签名证书时,通常会发生这种情况。This usually happens when your MongoDB server is using self-signed certificates. 你可切换为正确签名的证书,也可直接将 ssl_cert_reqs=CERT_NONE 选项添加到连接字符串。You can either switch to a properly signed certificate or just add the ssl_cert_reqs=CERT_NONE option to your Connection String.

将 MongoDB 查询编写为 JSON 对象。Write your MongoDB query as a JSON object. 在执行期间,SQL Analytics 会将其转换为 db.collection.find() 调用或 db.collection.aggregate() 调用。During execution, SQL Analytics will convert it into either a db.collection.find() call or a db.collection.aggregate() call. 映射 JSON 对象并将其发送到 MongoDB 的方法如下:Here’s how your JSON object is mapped and sent to MongoDB:

MongoDB 令牌MongoDB Token SQL Analytics 中的写入位置Where to write in SQL Analytics
db 在数据源设置屏幕上On the data source setup screen
collection 在查询对象中添加 collectionAdd a collection key in your query object
query 在查询对象中添加 queryAdd a query key in your query object
projection 在查询对象中添加 fieldsAdd a fields key in your query object
.sort() method 在查询对象中添加 sortAdd a sort key in your query object
.skip() method 在查询对象中添加 skipAdd a skip key in your query object
.limit() method 在查询对象中添加 limitAdd a limit key in your query object
db.collection.count() 方法db.collection.count() method 在查询对象中使用具有任意值的 countUse a count key with any value in your query object

将用于每个键的值作为参数按原样传递到 MongoDB。The values you use for each key are passed unmodified as as parameters to MongoDB.

查询示例Query Examples

简单查询示例Simple Query Example

{
  "collection": "my_collection",
  "query": {
    "type": 1
  },
  "fields": {
    "_id": 1,
    "name": 2
  },
  "sort": [{
    "name": "date",
    "direction": -1
  }]
}

Javascript 中的等效查询将编写为:db.my_collection.find({"type": 1}, {"_id": 1, "name": 2}).sort([{"name": "date","direction": -1}])An equivalent query in Javascript would be written: db.my_collection.find({"type": 1}, {"_id": 1, "name": 2}).sort([{"name": "date","direction": -1}])

计数查询示例Count Query Example

{
  "collection": "my_collection",
  "count": true
}

聚合Aggregation

聚合使用的语法与 PyMongo 中使用的类似。Aggregation uses a syntax similar to the one used in PyMongo. 但为了支持正确排序,它在“$sort”操作时使用常规列表,该列表会在执行前转换为 SON(已排序的字典)对象。However, to support the correct order of sorting, it uses a regular list for the “$sort” operation that converts into a SON (sorted dictionary) object before execution.

聚合查询示例:Aggregation query example:

{
  "collection": "things",
  "aggregate": [{
    "$unwind": "$tags"
  }, {
    "$group": {
      "_id": "$tags",
      "count": {
        "$sum": 1
      }
    }
  }, {
    "$sort": [{
      "name": "count",
      "direction": -1
    }, {
      "name": "_id",
      "direction": -1
    }]
  }]
}

MongoDB 扩展 JSON 支持MongoDB Extended JSON Support

除了我们自己的扩展 $humanTime,我们还支持 MongoDB 扩展 JSONWe support MongoDB Extended JSON along with our own extension - $humanTime:

{
  "collection": "date_test",
  "query": {
    "lastModified": {
      "$gt": {
        "$humanTime": "3 years ago"
      }
    }
  },
  "limit": 100
}

它接受与上述类似的可读字符串(“3 years ago”、“yesterday”等)或时间戳。It accepts a human-readable string like the above (“3 years ago”, “yesterday”, etc) or timestamps.

备注

由于 SQL Analytics 使用的格式与 MongoDB 所需的格式之间存在差异,因此在 MongoDB 中使用日期或日期/时间类型的查询参数时,还需要 $humanTime 函数。The $humanTime function is also needed when using Query Parameters of type Date or Date/Time with MongoDB, due to the difference between the format SQL Analytics uses and the one MongoDB expects.

使用日期(或日期范围)参数时,请使用 $humanTime 对象对其进行包装:{{param}} 会变为 {"$humanTime": "{{param}} 00:00"}(使用日期参数时才需要后缀 00:00;如果使用日期时间参数,则应忽略该后缀)。When using a Date (or Date Range) parameter, wrap it with a $humanTime object: {{param}} becomes {"$humanTime": "{{param}} 00:00"} (the 00:00 suffix is needed only with Date parameters, for Date Time parameters you should skip it).

MongoDB 筛选MongoDB Filtering

可通过将添加了“::filter”关键字的列投影到末尾,向 Mongo 查询添加筛选器。You can add filters to Mongo queries by projecting a column with the ‘::filter’ keyword added on to the end.

{
  "collection": "zipcodes",
  "aggregate": [{
    "$project": {
      "_id": "$_id",
      "city": "$city",
      "loc": "$loc",
      "pop": "$pop",
      "state::filter": "$state"
    }
  }]
}

上例将显示一个“State”列,并允许对此列进行筛选。The above example will show a ‘State’ column, and allow you to filter on this column.

疑难解答Troubleshooting

排序超出了 104857600 字节的内存限制Sort exceeded memory limit of 104857600 bytes

排序超出了 104857600 字节的内存限制,但未选择进行外部排序。Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. 中止操作。Aborting operation. 传递 allowDiskUse:true 以选择加入。Pass allowDiskUse:true to opt in.

在 MongoDB 中,内存中排序限制为 100 M;若要执行大型排序,需启用 allowDiskUse 选项来将数据写入临时文件进行排序。In MongoDB, the in-memory sorting have a limit of 100M, to perform a large sort, you need enable allowDiskUse option to write data to a temporary files for sorting.

只需将选项添加到查询中即可启用 allowDiskUse 选项:To enable the allowDiskUse option, just add the option to your query:

{
  "allowDiskUse": true
}