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. There are two types of queries supported:

  • API driven queries: These queries rely on the mongo-java-driver support provided to query MongoDB.

{
# The following parameter is mandatory.
collectionName : myCollection Details,
# The following parameters are optional
findQuery : { Details },
findFields : { Details },
sort : { Details },
mapReduce : { Details },
limit : int Details,
rowsToProcess : int Details
}

{
runCommand : { Command body },
# 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 [list of values]
field : { '$exists' : 'true' } // return object if field is present
field : { '$exists' : 'false' } // return if field is missing
field1 : { '$gt' : value1 } , field2 : { '$lt' : value2 } // both conditions must be satisfied
$or : [ { field1 : 'extremely loud' } , { field2 : 'incredibly close' } ] // at least one of the fields matches

More information and comprehensive list of MongoDB conditional operators available:

http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-ConditionalOperators

Regular expressions

findQuery : {
     field : {  '$regex'  :  'expression',  '$options'  :  ''  }
}

More information http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-RegularExpressions

$where

Allows the query to specify a javascript expression.

findQuery : {
    $where : "this.billing_address_country == 'Mexico'"
}

More information Javascript Expressions and $where


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. To return all fields except for a specified subset, exclude fields with a key/value pair of 'fieldname' : 0'''.

# Return only name and phone_office
 
findFields : {
    name : 1,
    phone_office : 1
}
# Return all fields except for big_image
 
findFields : {
    big_image : 0
}

sort

Specifies the fields and order of each one of the them that will be used to sort.

Accepted values:

1 (natural order)
-1 (descending)

sort : {
    country_name : 1,
    city_population : -1
}

mapReduce

Specifies a MapReduce operation which output will be used for the current query. In this case collectionName specifies the target collection This object expects three mandatory keys and one optional:

  • Mandatory keys
    • map: Specifies the map function as a string
    • reduce: Specifies the reduce function as a string
    • out: Specifies the name of the output collection or output options: MongoDB MapReduce Output Options

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:

# previously existing collection (if any) is replaced
 
'out' : {
    'replace' : 'joinedCollection'
}
# identical to 'replace' operation
 
'out' : 'joinedCollection'
# new keys replace existing keys (other keys remain unchanged)
 
'out' : {
    'merge' : 'joinedCollection'
}
# new keys and existing keys are reduced together
 
'out' : {
    'reduce' : 'joinedCollection'
}
# specify a different database for the output collection
 
'out' : {
    'replace' : 'sample',
    'db' : 'sampleDatabase'
}
# not supported in the connector
 
'out' : {
    { 'inline' : 1 } # NOT supported. Don't do this.
}

Other MapReduce fields are not supported. MongoDB MapReduce Docs

limit

The 'limit' must be set to a non-negative int. It should not be enclosed in any quotes. It limits the number of documents returned. Setting the limit to 0 or not specifying a limit has the effect of returning all documents. 'limit' : 5

rowsToProcess

The rowsToProcess key sets the number of rows that will be processed to determine the list of fields. This applies only at edit at time in iReport. It has no effect on reports when they are executed. The connector uses a default value of 5 documents if this option is not specified.

'rowsToProcess' : 20

aggregationFramework

The command must be expressed as follows:

{
    runCommand : {
        aggregate : COLLECTION_NAME,
        pipeline : [ # List of aggregation operations]
    }
}

The following operators are supported in the pipeline:

  • $group: Groups documents by key and calculate aggregate values for the group. Reference
$group : {
    _id : "$tags",
    docsByTag : { $sum : 1 },
    viewsByTag : { $sum : "$pageViews" },
    avgByTag : { $avg : "$pageViews" },
}
  • $project: Selects columns or sub-columns, create computed values or sub-objects. 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 out from the document stream. Reference
$match : { author : "dave" }
  • $limit: Limits the number of documents that pass through the document stream. Reference
$limit : 20
  • $skip: Skips over a number of documents that pass through the document stream. Reference
$skip : 10
  • $unwind: Unwinds an array, subsituting each value in the array for the array within the same document. Reference
$unwind : "$tags"
$sort : { author : -1 }

Sample queries

findQuery

Minimal

{
    'collectionName' : 'accounts'
}

Specify the fields returned and sort 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
    }
}

Filtered and parameterized

{
     'collectionName' : 'accounts',
     'findQuery' : {
          'status_date' : { '$gte' : $P{StartDate}, $lt: $P{EndDate} },
          'name' : { '$regex' : '^N', '$options' : '' }
 
      }
}

Hard-coded $in clause

{
    'collectionName' : 'customers',
     'findQuery' : { 'industry' : { '$in' : ['Communications','Engineering'] } }
}

Parameterized $in clause

{
     'collectionName' : 'customers',
     'findQuery' : { 'industry' : { '$in' : $P{MyCollectionParameter} } }
}

mapReduce

MapReduce job

{
  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<a href="/wiki/index">index</a>.population;
                }
                return {population : total}
              }',
    out : 'totalPopulation'
  }
}

Aggregation Framework

Aggregation Query

{
   runCommand: {
        aggregate : 'zips',
        pipeline : [
                {
                        $group : {
                                _id : '$state',
                                population: {
                                        $sum : '$pop'
                                }
                        }
                },
                {
                        $sort : {
                                population : -1
                        }
                }
        ]
}

Query Sample

{
    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' ]  }
                        ]
                    }
                }
            }
        ]
    }
}

Appendix

SQL to MongoDB Query Mappings

Standard Queries

In SQL:

SELECT *
  FROM users
  WHERE age > 25
   AND   age <= 50
  ORDER BY age

When using Mongo:

{
   collectionName : 'users',
   findQuery : { 
        age: { $gt: 25, $lte: 50 } 
   },
   sort : {
        'age': 1
   }
}

See: http://docs.mongodb.org/manual/reference/sql-comparison/

Aggregation Framework

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

When using Mongo:

{
   runCommand: {
        aggregate : 'orders',
        pipeline : [
                {
                        $group : {
                                _id : { cust_id: '$cust_id', ord_date: '$ord_date' }
                                total: { $sum : '$price' }
                        }
                },
                { 
                        $match: { 
                                total: { $gt: 250 } } 
                        }
                },
                {
                        $sort : {
                                total : -1
                        }
                }
        ]
}

See: http://docs.mongodb.org/manual/reference/sql-aggregation-comparison/

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.

https://github.com/mongodb/mongo-java-driver/wiki/Release-Notes JAVA-319: JSON parser to not require quotes around keys

Version of MongoDB Java Driver

Jaspersoft MongoDB connector version mongo-java-driver version
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

Comments

i tried to use mongodb aggregate query but i get this error : 'cursor' option is required. and when i ass the cursor option i get the error 'No results found'.

can you help me that ?

Hi,

I'm also having problem with this matter. Is there any solutions yet ?

More folks will see your question if you create it as a question.

Hi All,

I'm having problem on querying the mongo aggregate query in jaspersoft studio. the message always said "Cursor required" when I try to add cursor in the query and running it, the message will be "No Result"

Do you guys have any problem with this ? is there any solution beside changing the mongodb to 3.4 version ?

Thanks,

Regards,

Gembit Soultan

Ask this as a question.  Not as feedback to a wiki page.  More folks will see it.

Feedback
randomness