Perform datetime based query

[Jet_lag]

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

 

 

 

[Mehak Rajkumar]

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.
 

[Jitendra Mandalia]

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 November 28, 2023 by jiman

[Jet_lag]

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 ?

[Jitendra Mandalia]

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.