Join two collections in mongodb in Jasper

[vivek.agarwal]

Hi,

We need to show data from two mongodb collections. I need to know how can we join two collections.

 

Regards

Vivek

[calculate.machine]

     

[calculate.machine]

To connect to multiple datasources, Jasper offers virtual data source. The function, however, is provided only in the commercial or higher version and probably won’t appear in a free version. In addition, it supports connecting to only two datasources.

 

But you can try using esProc to assist Jasper to join the two collections. Suppose to select orders during a certain time period from collection sales and create a left join with collection emp. You can do it in esProc as follows:

A1=MongoDB("mongo://localhost:27017/test?user=root&password=sa")

A2=A1.find("sales","{'$and':[{'OrderDate':{'$gte':'"+string(begin)+"'}},{'OrderDate':{'$lte':'"+string(end)+"'}}]}","{_id:0}").fetch()

A3=A1.find("emp",,"{_id:0}").fetch()

A4=A1.close()

A5=join@1(A2:sales,SellerId;A3:emp,EId)

A6=A5.new(sales.Client:Client,sales.Amount:Amount,sales.OrderDate:OrderDate,emp.Name:Name)

Jasper can connect to esProc through JDBC. The way it calls an esProc script is the same as that it calls the stored procedure. Please refer to http://blog.raqsoft.com/?p=2460 for details.