Join Postgresql strings with Mongodb documents based on specific columns
I am using MongoDB and PostgreSQL in my application. The necessity of using MongoDB is that we can have any number of new fields to be inserted for which we will store data in MongoDB.
We store our fixed field values in PostgreSQL and custom field values in MongoDB.
E.g.
**Employee Table (RDBMS):**
id Name Salary
1 Krish 40000
**Employee Collection (MongoDB):**
{
<some autogenerated id of mongodb>
instanceId: 1 (The id of SQL: MANUALLY ASSIGNED),
employeeCode: A001
}
We get records from SQL and from their IDs we get related records from MongoDB. Then match the result to get the new field values and send to the UI.
Now I'm looking for some optimized solution to get MongoDB results in POJO / PostgreSQL model so I don't have to manually fetch data from MongoDB by passing SQL IDs and then matching them again.
Is there a way that I can connect MongoDB with PostgreSQL via columns (here's the RDBMS ID and instanceId from MongoDB) so that with one fetch I can get the associated Mongo result. Any return type is valid, but I need it all in one call.
I am using Hibernate and Spring in my application.
source to share
Using Spring Data might be the best solution for your use case as it supports both:
- JPA
- MongoDB
You can still get all the data in one request, but that doesn't mean you have to use one DB call. You can have one service call that propagates to the twp database calls. Since the PostgreSQL string is probably the main object, I advise you to share the PostgreSQL primary key with MongoDB.
There is no need to have separate IDs. This way you can just get SQL and Mongo documents with the same ID. Sharing the same identifier can give you the advantage of processing these requests concurrently and combining the result before returning from the service call. Thus, the duration of the service method will not take the sum of the two repository calls, being the maximum number of those calls.
source to share
Surprisingly, yes, you can potentially. There's an external data wrapper named mongo_fdw
that allows PostgreSQL to query MongoDB. I have not used it and have no opinion on its performance, usefulness or quality.
I would be very surprised if you could use this effectively through Hibernate, unless you can convince Hibernate that FDW mapped "tables" are just views. You might be in luck with EclipseLink and their "NoSQL" support if you want to do this at the Java level.
Separately, this sounds like a monstrous design. There are many sane ways to do what you want in a decent DBMS without going to a hybrid database platform. There is a time and place for hybrids, but I really doubt your situation justifies the complexity.
Just use PostgreSQL support json
/jsonb
to support dynamic mappings. Or use traditional options like saving json
as text fields, saving XML, or even displaying EAV. Don't create rube goldberg .
source to share