Language Reference
The Jaspersoft MongoDB Query Language is a JSON-style declarative language for specifying what data to retrieve from MongoDB. The connector converts this query into the appropriate API calls and uses the MongoDB Java connector to query the MongoDB instance.
As of release 7.5, JasperReports Server supports MongoDB 4.0 by using its Java driver version 3.10.2 (see MongoDB Compatibility). The Jaspersoft MongoDB query language has been updated, in particular the syntax for aggregation, and you may need to modify your MongoDB queries after you upgrade.
There are two types of queries supported:
- API driven queries: These queries rely on the mongo-java-driver to query MongoDB.
{
# The following parameter is mandatory.
collectionName : myCollection Details,
# The following parameters are optional
findQuery : { Details },
findFields : { Details },
sort : { Details },
aggregate : [ As of v7.5 - Details ],
mapReduce : { Details },
limit : int Details,
rowsToProcess : int Details
batchSize : int As of v7.5 - Details
maxTime : int As of v7.5 - Details
collation : { As of v7.5 - Details }
}
- Command driven queries: In releases up to JasperReports Server 7.2, command-based queries supported aggregation operations. As of release 7.5, aggregation commands are deprecated.
{
runCommand : { aggregate [ Up to 7.2 - Details ],
# The following parameter is optional
rowsToProcess : int Details
} }
collectionName
Specifies the collection name. Exactly one collection must be specified, this can be hard-coded into the query or you can use a report parameter to feed the collection name.
- Hard-coded collection name:
{ collectionName : 'myCollection' }
- Collection name specified as a String Parameter:
{ collectionName : $P{myCollectionParam} }
findQuery
The findQuery specifies which documents from the collection will be returned. It supports the following options:
- Conditional operators
The value used for these operators may be explicitly specified in the query or may be specified in the report as a parameter of the appropriate data type.
Partial list of conditional operators:
field : { '$gt' : value } | # greater than value |
field : { '$lt' : value } | # less than value |
field : { '$gte' : value } | # greater than or equal to value |
field : { '$lte' : value } | # less than or equal to value |
field : { '$all' : [list of values] } | # field contains all values specified in the list |
field : { '$exists' : 'true' } | # return object if field is present |
field : { '$exists' : 'false' } | # return if field is missing |
field1 : { '$gt' : value1 } , field2 : { '$lt' : value2 } | # logical AND: both conditions must be satisfied |
$or : [ { field1 : 'extremely loud' } , { field2 : 'incredibly close' } ] | # logical OR: at least one of the field conditions is true |
For more information and a comprehensive list of MongoDB conditional operators, see the MongoDB Query and Projection Operators.
- Regular expressions
You can also select fields and documents based on matching a regular expression:
findQuery : { field : { '$regex' : 'expression', '$options' : '' } }
For more information, see the MongoDB regex documentation.
- $where
Allows the query to specify a javascript expression.
findQuery : { $where : "this.billing_address_country == 'Mexico'" }
For more information, see the MongoDB $where documentation.
findFields
This allows the query to select which fields will be part of the result set. If no value is specified for 'findFields' then all fields will be returned. To return only a subset of the fields available in the returned documents, specify a key/value pair of 'fieldname' : 1 for each field you want to return.
To return all fields except for a specified subset, exclude fields with a key/value pair of 'fieldname' : 0.
In the following example, only the name and home_office field are returned:
findFields : { name : 1, phone_office : 1 }
The following example returns all fields except for big_image:
findFields : { big_image : 0 }
sort
Specifies the fields and order of each one that will be used to sort. This sorting does not affect aggregation, instead use the sort stage of the pipeline as described in Aggregation Pipeline.
Accepted values:
1 (natural order)
-1 (descending)
sort : { country_name : 1, city_population : -1 }
limit
The limit cuts off the result after the given number of documents is returned. When this optional value is set, it must be a positive integer (not enclosed in quotes). Setting the limit to 0 or not specifying a limit has the effect of returning all documents. This limit does not affect aggregation, instead use the limit stage of the pipeline as described in Aggregation Pipeline.
'limit' : 5
Aggregation (as of v7.5)
As of JasperReports Server 7.5, command-based aggregation is deprecated and replaced by API-based aggregation:
{ collectionName: COLLECTION_NAME, aggregate : [ # List of aggregation operations - pipeline ] }
Aggregation (up to v7.2)
The aggregation command must be expressed as follows:
{ runCommand : { aggregate : COLLECTION_NAME, pipeline : [ # List of aggregation operations - pipeline ] } }
Aggregation Pipeline
The aggregation pipeline is a set of operations, with each operation called a stage that transforms the documents in some way. For more information, see the MongoDB documentation:
The following operators are supported in the pipeline:
- $group: Groups documents by key and calculates aggregate values for the group (MongoDB reference).
$group : { _id : "$tags", docsByTag : { $sum : 1 }, viewsByTag : { $sum : "$pageViews" }, avgByTag : { $avg : "$pageViews" }, }
- $project: Selects columns or sub-columns, and creates computed values or sub-objects (MongoDB reference).
$project : { author : 1, // Includes the field tag : "$tags", // Renames a field and includes it with the new name pageViews : 1, daveWroteIt : { $eq: ["$author", "dave"] }, weLikeIt : { // Applies different aggregation expressions $or: [ { $eq:["$author", "dave"] }, { $eq:["$tags", "good"] } ] } }
- $match: Filters documents so that only matching documents continue in the stream (MongoDB reference).
$match : { author : "dave" }
- $limit: Limits the number of documents that pass through the document stream (MongoDB reference).
$limit : 20
- $skip: Skips over a number of documents and passes the remaining through the document stream (MongoDB reference).
$skip : 10
- $unwind: Unwinds an array, subsituting each value in the array for the array within the same document (MongoDB reference).
$unwind : "$tags"
- $sort: Sort documents by key (MongoDB reference).
$sort : { author : -1 }
mapReduce
Map-reduce is another form of aggregation. In general the aggregation pipeline is simpler to define and faster in performance, but map-reduce can provide flexibility and customization that the pipeline cannot. For more information, see the MongoDB map-reduce documentation.
The mapReduce key specifies a map-reduce operation whose output will be used for the current query. In this case, the collectionName specifies the target collection. This object expects three mandatory keys and has one optional key:
- Mandatory keys
- map: Specifies the map function as a string (MongoDB reference)
- reduce: Specifies the reduce function as a string (MongoDB reference)
- out: Specifies the name of the output collection or output options (MongoDB reference)
- Optional key
- finalize: Defines a JavaScript function as a string to be applied after the reduce step (MongoDB reference)
Other MapReduce fields are not supported. MongoDB MapReduce Docs
Sample mapReduce function:
'map' : 'function () { emit (this.state, {population : this.pop}); }', 'reduce' : 'function (key, values) { var total = 0; for( var index = 0; index < values.length; index ++ ) { total += values<a href="/wiki/index">index</a>.population; } return {population : total} }', 'out' : 'totalPopulation'
Output options:
- replace - The previously existing collection (if any) is replaced:
'out' : { 'replace' : 'joinedCollection' }
- out - Identical to 'replace' operation, if no other operation is needed:
'out' : 'joinedCollection'
- merge - New keys replace existing keys (other keys remain unchanged):
'out' : { 'merge' : 'joinedCollection' }
- reduce - New keys and existing keys are reduced together:
'out' : { 'reduce' : 'joinedCollection' }
- db - Specify a different database for the output collection:
'out' : { 'replace' : 'sample', 'db' : 'sampleDatabase' }
- inline - This option is not supported in the connector:
'out' : { { 'inline' : 1 } # NOT supported. Don't do this. }
rowsToProcess
The rowsToProcess sets the number of rows that will be processed to determine the list of fields in Jaspersoft Studio. This value applies only at edit at time in Jaspersoft Studio. It has no effect on reports when they are executed.
When this optional value is set, it must be a positive integer (not enclosed in quotes).The connector uses a default value of 5 documents if this option is not specified.
'rowsToProcess' : 20
batchSize
This value determines the number of documents that MongoDB will return to the client per network message. It does not affect query results but may affect performance for exceptional document sizes. When this optional value is set, it must be a positive integer (not enclosed in quotes). For more information, see the MongoDB documentation for batchSize.
'batchSize' : 10
maxTime
This value specifies a time limit in milliseconds for processing operations a query. If you do not specify a value for maxTime, operations will not time out. A value of 0 explicitly specifies the default unbounded behavior. When this optional value is set, it must be a positive integer (not enclosed in quotes). For more information, see the MongoDB documentation for maxTime.
The following setting stops the query after 10 minutes.
'maxTime' : 600000
collation
This optional parameter specifies language-specific rules for string comparison, such as rules for letter case and accent marks. It supports all options described in the MongoDB documentation for collation.
'collation' : { locale: 'fr'}
Sample queries
findQuery
The minimal query giving just the collection name returns all records with all of their fields:
{ 'collectionName' : 'accounts' }
The following example uses the findFields key to select certain fields and the sort key to order the results:
{ 'collectionName' : 'accounts', 'findFields' : { 'name':1, 'phone_office':1, 'billing_address_city':1, 'billing_address_street':1, 'billing_address_country':1 }, 'sort' : { 'billing_address_country':-1, 'billing_address_city':1 } }
The findQuery key can define filters as shown in the following examples:
{ 'collectionName' : 'accounts', 'findQuery' : { 'status_date' : { '$gte' : $P{StartDate}, $lt: $P{EndDate} }, 'name' : { '$regex' : '^N', '$options' : '' } } }
In this example, the value of the "in" filter is hard-coded:
{ 'collectionName' : 'customers', 'findQuery' : { 'industry' : { '$in' : ['Communications','Engineering'] } } }
In this example, the value of the "in" filter is parameterized:
{ 'collectionName' : 'customers', 'findQuery' : { 'industry' : { '$in' : $P{MyCollectionParameter} } } }
The aggregation pipeline allows advanced grouping and filtering:
API-based as of v7.5 | Command-based up to v7.2 |
{ collectionName: 'zips', aggregate : [ { $group : { _id : '$state', population: { $sum : '$pop' } } }, { $sort : { population : -1 } } ] } |
{ runCommand: { aggregate : 'zips', pipeline : [ { $group : { _id : '$state', population: { $sum : '$pop' } } }, { $sort : { population : -1 } } ] } } |
{ collectionName : 'article', aggregate : [ { $project : { author : 1, tags : 1, pageViews : 1 } }, { $unwind : '$tags'}, { $project : { author : 1, tag : "$tags", pageViews : 1, daveWroteIt : { $eq:['$author', 'dave'] }, weLikeIt : { $or: [ { $eq:[ '$author', 'dave'] }, { $eq:[ '$tags', 'good' ] } ] } } } ] } |
{ runCommand: { aggregate : 'article', pipeline : [ { $project : { author : 1, tags : 1, pageViews : 1 } }, { $unwind : '$tags'}, { $project : { author : 1, tag : "$tags", pageViews : 1, daveWroteIt : { $eq:['$author', 'dave'] }, weLikeIt : { $or: [ { $eq:[ '$author', 'dave'] }, { $eq:[ '$tags', 'good' ] } ] } } } ] } } |
The mapReduce key lets you define custom aggregation functions:
{ collectionName : 'zips', sort : { value.population : -1, }, mapReduce : { map : 'function () { emit (this.state, {population : this.pop}); }', reduce : 'function (key, values) { var total = 0; for( var index = 0; index < values.length; index ++) { total += values[index].population; } return {population : total} }', out : 'totalPopulation' } }
Appendix
SQL to MongoDB Query Mappings
Simple query in SQL:
SELECT * FROM users WHERE age > 25 AND age <= 50 ORDER BY age
When using MongoDB:
{ collectionName : 'users', findQuery : { age: { $gt: 25, $lte: 50 } }, sort : { 'age': 1 } }
For more details, see the SQL to MongoDB Mapping Chart.
Aggregation (group by) in SQL:
SELECT cust_id, ord_date, SUM(price) AS total FROM orders GROUP BY cust_id, ord_date HAVING total > 250 ORDER BY total DESC
Aggregation using MongoDB:
API-based as of v7.5 | Command-based up to v7.2 |
{ collectionName: 'orders', aggregate : [ { $group : { _id : { cust_id: '$cust_id', ord_date: '$ord_date' } total: { $sum : '$price' } } }, { $match: { total: { $gt: 250 } } }, { $sort : { total : -1 } } ] } |
{ runCommand: { aggregate : 'orders', pipeline : [ { $group : { _id : { cust_id: '$cust_id', ord_date: '$ord_date' } total: { $sum : '$price' } } }, { $match: { total: { $gt: 250 } } }, { $sort : { total : -1 } } ] } } |
For more details, see the SQL to Aggregation Mapping Chart.
Quotes in Queries
Quotes are optional around keys, but in the past they were required. Some examples on this page use quotes, but some do not.
MongoDB Java driver JIRA: improve JSON parser to not require quotes around keys.
Version of MongoDB Java Driver
Jaspersoft MongoDB connector version | mongo-java-driver version |
js-mongodb-datasource-3.10.2.jar | mongo-java-driver-3.10.2 |
0.5.0 - current | mongo-java-driver 2.7.3 |
0.13 | mongo-java-driver 2.7.3 |
0.11 - 0.12 | mongo-java-driver 2.7.2 |
0.8 - 0.10 | mongo-java-driver 2.6.3 |
0.1 - 0.7 | mongo-java-driver 2.3 |
See Also
- Mongo DB 4.0 Documentation
- Jaspersoft Tech Talks # 13:
- Video: Using Jaspersoft with MongoDB
- Slides: Jaspersoft with MongoDB Slides
- Analytics for Mongo DB
- MongoDB Resources
Log in or register to post comments