Jaspersoft MongoDB Query Language

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:

1. }# field contains all values specified in the listfield : { '$exists' : 'true' }# return object if field is presentfield : { '$exists' : 'false' }# return if field is missingfield1 : { '$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:

• Aggregation Pipeline
• Pipeline Stages

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 += valuesindex.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:

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 totalFROM ordersGROUP BY cust_id, ord_dateHAVING total > 250ORDER BY total DESC

Aggregation using MongoDB:

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

See Also

• Mongo DB 4.0 Documentation
• Jaspersoft Tech Talks # 13:
  • Video: 
  • Slides: Jaspersoft with MongoDB Slides
• Analytics for Mongo DB
• MongoDB Resources