-1

I have two collections.

  LogData
 [{
  "SId": 10,
  "NoOfDaya" : 9,
  "Status" : 4
 }
{
"SId": 11,
"NoOfDaya" : 8,
 "Status" : 2
 }]



 OptData 
 [ {
 "SId": 10,
"CId": 12,
"CreatedDate": ISO(24-10-2014)
}

 {
  "SId": 10,
  "CId": 13,
  "CreatedDate": ISO(24-10-2014)
}]

Now using mongoDB I need to find the data in form 

select  a.SPID,a.CreatedDate,CID=(MAX(a.CID)) from OptData a     
Join LogData c  on a.SID=c.SID where Status>2
group by a.SPID,a.CreatedDate

LogData have 600 records whereas OPTData have 90 millions records in production. I need to update LogData frequently, that's why its in separate collection.

  1. Please don't suggest to keep data in one collection.
  2. This is same query, I asked with different approach Creating file in GridFs (MongoDb)
  3. Please don't suggest Joins can't be applied in mongoDB.
Community
  • 1
  • 1
sangita
  • 191
  • 5
  • 14

1 Answers1

0

Because MongoDB does not support JOINs, you will have to perform two separate queries and do the JOIN on the application layer. With just 600 documents the collection LogData is very small, so it should be no problem to completely load it into your applications memory and use it to enrich the results returned from OptData.

Another option would be to denormalize the data from LogData by mirroring the fields you need from LogData in the respective documents in OptData. So your OptData documents would look something like this:

{
   "SId": 10,
   "CId": 12,
   "CreatedDate": ISO(24-10-2014),
   "LogStatus": 2
}
Philipp
  • 67,764
  • 9
  • 118
  • 153
  • Correct. So I am trying to do the second approach but how to do that? Map-Reduce doesn't support any thing like this. – sangita Feb 25 '15 at 09:41
  • @sangita You will have to update all of your documents. This will require to write a program which does what is described in the first approach. – Philipp Feb 25 '15 at 09:44
  • 1st approach doesn't suits my problem. It should be solvable at mongoDB level. You are suggesting me same thing again and again. – sangita Feb 25 '15 at 10:02
  • @sangita I am sorry that MongoDB does not work the way you want it to work. – Philipp Feb 25 '15 at 10:05