Convert Sql query based on more than one table to mongodb query

[kanthu]

Convert sql query which involves 3 tables to mongo query [mongo 3.0 version] using aggregation. In mongodb Acc is the actual collection which has subscr and dv as embedded collections

In other words, the result set from sql query should match the result set from mongo query. 

SELECT T1.ACC_ID, T1.CTN, T1.NAME, T1.GTWY_GUID, T1.DV_GUID, T1.CAT_ID, T1.FW_VER, 

T1.DATE_CREATED, T1.STAT_ID,

('=' || T2.LOCID) LOC_CODE, T1.BAN, T1.Mkt_Area, T1.STATE, T1.IMEI IMEI, T1.HW_MODEL FROM (

      SELECT D.ACC_ID, ('=' || S.SUB_ID) CTN, (S.FNAME || ' ' || S.LNAME) NAME, D.GTWY_GUID, 

 D.DV_GUID, ('=' || D

.CAT_ID) CAT_ID, D.FW_VER, D.DATE_CREATED, D.STAT_ID, ('=' || A.EXT_ACC_ID) BAN,

 S.BMKT Mkt_Area, S.STATE, (

'=' || S.HWID) IMEI, D.HW_MODEL

      FROM DV D, SUBSCR S, ACC A

      WHERE D.GTWY_GUID=S.GTWY_GUID

      AND D.ACC_ID=A.ACC_ID

      AND S.ACC_ID=A.ACC_ID

      AND D.DOM_ID='DL'

     ) T1

LEFT OUTER JOIN

    (SELECT gtwy_guid, dv_guid, mobject_value.value LOCID  FROM mobject_value WHERE mobject_id='80') T2

ON T1.gtwy_guid=T2.gtwy_guid AND T1.dv_guid=T2.dv_guid

ORDER BY T1.NAME, T1.GTWY_GUID, T1.DV_GUID ASC

[kanthu]

Correction: Acc is the actual collection which has subscr and dv as embedded documents*

[Stephen Steneker]

On Thursday, 11 February 2016 15:26:12 UTC+11, kanthu wrote:

Convert sql query which involves 3 tables to mongo query [mongo 3.0 version] using aggregation. In mongodb Acc is the actual collection which has subscr and dv as embedded collections

Hi,

An effective data model in MongoDB will support your use case, and likely look significantly different from the relational model implied by your SQL query.

Rather than trying to directly translate SQL queries that presume normalized data, I would encourage you to consider Data Models in MongoDB as a starting point.

If you do have a question about MongoDB usage, you’ll need to provide more information:

• a sample document showing how your data is structured in MongoDB

• a sample of your desired result

• details on what you’ve tried so far

Regards,
Stephen

​

[kanthu]

I am attaching the collection with sample documents [queried for few documents] and the sql result into a csv file. The query that i'm trying is in the js file. Coming to the data model, i cannot change the collection structure now because it is accessed by some other report services. 

It is daily service which generate the report with those results every day. I need to write a mongo query and the results of sql and mongo query should be consistent. On an average the record count of sql query every day would be 6.5 million.