How to format the filters and conditions of the MongoDB Nosql Query in the Query editor of Jasper studio ?

[sugreevu.teja]

I was able to use my Local MongoDB data into Jasper Studio to create the Reports of my Data by the Connection String and it is successfully connected with our MongoDB, But it is asking us to give the Query.
Even after we gave the query it is ended up with the error, please help me with the Query and the format to use.
Example : I have used one query which takes the condition of element match to go inside an array field in the Json Data by the DateTime Range 
        {
  'collectionName':'Employee',
  'findFields':{
          'details': {
          $elemMatch: {
          'department': 'IT',
          'subdept": "software",
          'leave_period': {
          $gte:"2023-08-05T09:00:00Z",
          $lte:"2023-08-10T20:00:00Z"
        }
      }
    }
  }
}

Edited November 17, 2023 by sugreevu.teja

[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 @sugreevu.teja

There are two ways to use the MongoDB connector. 
1. TIBCO JDBC Driver.
2. MongoDB native connector. 
Can you tell us which one you are using?

FYI...For JDBC driver, you need to use SQL query while for the native connector only you can MongoDB syntax. 

Please refer to below Youtube video.

 

[sugreevu.teja]

hi @jiman 
Thanks for the response, Actually I tried in both ways 
1. When I'm using the Database JDBC connection, I'm unable to find the option MongoDB which they selected in the video, so i have tried to use direct MongoDB Connection option which only worked for Local Connection string.
2. When I'm using the second option i able to connect locally and created the Data Adapter but when I'm trying to create the Jasper report for that connection it is initially only asking the Query for that I have used the above Query which I mentioned in the previous post, The main problem is I'm giving the but i need to go inside an array object I'm not able to find any Format to code it correctly.

if possible can you tell how to add that MongoDB jdbc option and correct format of the query.

[Jet_lag]

Hi @Mehak Rajkumar and @jiman

Would you please look at the similar problem I am facing :

 

[Jitendra Mandalia]

Hi @sugreevu.teja

1. Which Studio version are you using? Do you have a community edition or commercial edition? The TIBCO MongoDB JDBC driver is only available from Studio 6.2 with commercial license. You can try other MongoDB JDBC driver provided by MongoDB itself.

2. For the MongoDB syntax, you can search for appropriate scenario online and you should get enough examples to query different data structures including arrays with MongoDB syntax.

https://www.mongodb.com/docs/manual/tutorial/query-documents/

https://www.mongodb.com/docs/manual/tutorial/query-arrays/

Thanks.

Edited November 28, 2023 by jiman

[Jitendra Mandalia]

3 hours ago, jiman said:

Hi @sugreevu.teja

1. Which Studio version are you using? Do you have a community edition or commercial edition? The TIBCO MongoDB JDBC driver is only available from Studio 6.2 with commercial license. You can try other MongoDB JDBC driver provided by MongoDB itself.

2. For the MongoDB syntax, you can search for appropriate scenario online and you should get enough examples to query different data structures including arrays with MongoDB syntax.

https://www.mongodb.com/docs/manual/tutorial/query-documents/

https://www.mongodb.com/docs/manual/tutorial/query-arrays/

Thanks.

Above MongoDB query references were for a MongoDB driver, please refer to below article for the Jaspersoft specific MongoDB Query Language.

 

[sugreevu.teja]

hi @jiman 
I'm using Jasper studio 6.2 version only but not the Tibco commercial one, is their any possible way to use Jdbc-Mongodb in the normal Jasper studio if it is plz guide me with necessary steps.

And actually regarding the Query thing using DateTime range I tried with the $elematch and regex and etc.. but some how it is taking only one condition for example:
 {
  'collectionName':'Employee',
  'findFields':{
          'details': {
          $elemMatch: {
          'department': 'IT',
          'subdept": "software",
          'leave_period': {
          $gte:"2023-08-05T09:00:00Z",
          $lte:"2023-08-10T20:00:00Z"
        }
      }
    }
  }
}
In the above example i need both $gt and $lt but it is taking only the OR operation even after I'm using condition and, Is their any way to resolve this?

[Jitendra Mandalia]

Hi @sugreevu.teja

findFields is used to restrict the fields you want in the response. Are you trying to filter out the data using the date filter? Then you need to use findQuery option. 

i.e.

{
collectionName:'Employee',
findQuery : { 'field1': 'value1', 'leave_period' : { $gt: "2021-11-16T00:00:00.000Z", $lt: "2021-11-18T00:00:00.000Z"} }
}

 

'status_date' : { '$gte' : $P{StartDate}, $lt: $P{EndDate} }

Please see the post below where I recently provided an example for date filter. Also check the MongoDB Query Language article specific to Jaspersoft shared before for more examples on how to filter using date fields.