Jump to content
Changes to the Jaspersoft community edition download ×

Perform datetime based query


Jet_lag

Recommended Posts

Hello all,

I am new to jasper, please help me in solving below two queries which I am stuck since long time. I am running Jaspersoft® Studio 6.20.6 in my local windows machine.

Clarification 1:

  • When I create a Data Adapter with the local mongo with the connect URI :mongodb://127.0.0.1:27017/sampledb (sampledb is the database name) it connects.
  • However if I try to the mongo in server is not Atlas, but enterprise edition deployed in azure running as a pod, the connection string used can be connected to mongo compass successfully but fails in jasper. URI looks like this
    mongodb://xx.xx.xxx.xx:27017/?authSource=product-db&readPreference=primary&directConnection=true&tls=true&tlsAllowInvalidCertificates=true/product-db
    , error is
    Caused by: java.lang.IllegalArgumentException: databaseName can not be null
    even though the database name(product-db) is added at the end .

Please help on how to connect this.

Clarification 2:

  • Since the local mongo was connected i was trying a datetime comparison but did not find a suitable syntax.
  • Mongo record:
    • {
        "id": "12345",
        "value": {
          "version": "0",
          "items": [
            {
              "type": "pen",
              "segment": {
                "departure": {
                  "dateTime": "2019-09-11T16:15:00Z"
                }
            }}]
          }
      }

Here I would like to retun the records with datetime is greater than specific datetime. I tried below and no luck

{
collectionName:'Products',
findQuery : {
	'value.items.segment.departure.dateTime' : {
		$gt: {
            "$date": "2021-11-16T00:00:00.000Z"
        }
}
}
}

Please help and guide

 

 

 

Link to comment
Share on other sites

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.
 

  • Like 1
Link to comment
Share on other sites

Hi @Jet_lag

1. Your connection string seems to be wrong. It should be of below format. Basically have the database name first and then add any parameters after that.

mongodb://username:password@host:port/[database]?ssl=true
or
mongodb://xx.xx.xxx.xx:27017/[database]?authSource=xxx&readPreference=primary&directConnection=true&tls=true&tlsAllowInvalidCertificates=true

2. Please refer to below articles for finding the correct syntax for Jaspersoft MongoDB Query Language. 

https://community.jaspersoft.com/knowledgebase/reference/jaspersoft-mongodb-query-language/
 

Edited by jiman
Link to comment
Share on other sites

Hi @jiman,

Thanks for the response .

  1. Tried the new way of having the database name before other params and unfortunately it timeout, can you please help ? And no issues with the details as it connects successfully in MongoDBCompass.
    1. Quote

      net.sf.jasperreports.engine.JRException: Error creating MongoDBConnection; Caused by: Cannot test MongoDB connection; Caused by: Timed out after 30000 ms while waiting to connect. Client view of cluster state is {type=UNKNOWN,, caused by {javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target}

       

  2. In my query date range comparison is inside a list(items) in mongoDB query this can be queried using elemMatch before applying gt/lt, what is similar to elemMatch in jasper ?
Link to comment
Share on other sites

Hi @Jet_lag

 

Please see below.

1. MongoDB connection: For this issue, there are a few things to check. Hard to say exact reason per your environment setup.

1.1 Verify server is available at the ip:port. As you suggested, it seems you are able to connect with MongoDB Compass UI. If so, also see  firewall rules blocking such connection from Studio.

1.2 You can disable certificate validation for testing purposes.
mongodb://xx.xx.xxx.xx:27017/[database]
OR
mongodb://xx.xx.xxx.xx:27017/[database]?authSource=xxx&readPreference=primary&directConnection=true&tls=false
And see if one of these work to test the connection.

1.3 Check the MongoDB server logs for any more specific errors related to SSL connections or certificate validation.

2. There isn't direct match to elemMatch syntax but you can use similar to what is in the below example.

$elemMatch:

db.survey.find(
   { results: { $elemMatch: { product: "xyz" } } }
)

Date Example without elemMatch:
'findQuery' : {'status_date' : { '$gte' : $P{StartDate}, $lt: $P{EndDate} },          'name' : { '$regex' : '^N', '$options' : '' }      }
OR your example below.
{
collectionName:'Products',
findQuery : { 'value.items.segment.departure.dateTime' : { $gt: "2021-11-16T00:00:00.000Z" }}
}

Thanks.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...