RaqForum 28 No.
1,270 View  • 
     ...

MongoDB Joins MySQL

 
SQL(179)

【Question】

We have to generate a query including data from both MySQL and MongoDB.

The result of query executed in MongoDB contain “city id”. But we need to display city name instead of ‘city id’.

This city id has a reference to master table which is in MySQL. The master table has mapping of ‘city id’ & ‘city name’.

Can anyone help me with resolving this kind of scenarios?

 

【Answer】

You can use a virtual data source to implement MongoDB Join MySQL. But the feature is only provided in a business edition or an advanced edition and supports only two data sources.

Or you can try using SPL (Structured Process Language) to help doing this. For example, to get records from MongoDB’s emp1 collection by time periods and replace its CityID field values with cities table’s CityName field values in MySQL, we use the following SPL script:

 

A

1

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

2

=mongo_shell(A1,"emp1.find({'Birthday':{'$gte':'"+string(begin)+"'}},   {'Birthday':{'$lte':'"+string(end)+"'}} )").fetch()

3

=mongo_close(A1)

4

=myDB1.query("select *   from cities")

5

=A2.switch(CityID,A4)

6

=A5.new(EID,Dept,CityID.CityName:CityName,Name,Gender)

 

JasperReport can access esProc via JDBC and call an SPLS script in the same way as calling a stored procedure. More details can be found in How to Call an SPL Script in JasperReport.


SQL(179)
 
 •  1,270 View  •