CODEWITHSUNDEEP
mongodbpostgresqlselectwhere-clause
bandungeuy
bandungeuy

Reputation: 512

SQL Query after migrate from PostgreSQL to MongoDB

Recently, I migrated my postgreSQL database to MongoDB. Let say I have 2 tables in PostgreSQL and migrated become 2 collections in MongoDB. In PostgreSQL:

  1. Table accountCode

    ac_id desc group_id p_code c_code
    999.01 PettyCash 9 42000 99000
    888.09 Tax 34 70000 66000
    999.02 PC2 9 43000 99000

  2. Table transaction

    reffNum ac_id db cr desc dateTime id audited
    GL/0003845/V/11/21/XXX 999.01 950000 0 Transfer from Client 2021-03-17 09:58:41.304302 2 0
    GL/0005584/V/11/21/XXX 999.02 10000 0 Transfer from Client 2021-03-18 09:58:41.304302 2 0
    SL/00077898/V/11/21/XXX 888.09 997500 0 Tax 2021-03-18 09:58:41.304302 2 0
    GL/0005485/V/11/21/XXX 999.01 0 150000 Transfer to Client 2021-03-25 09:58:41.304302 3 0

And I have SQL Query like this:

SELECT CASE WHEN (SUM (A .db) - SUM (A .cr)) is NULL  THEN '0' ELSE (SUM (A .db) - SUM (A .cr)) END from transaction a, accountCode b
where a.ac_id = b.ac_id and a.id<>0 AND (a.audited='0') AND to_char(a.dateTime,'YYYY-MM') = '2021-03' AND b.groupid="9"

And it should give result: 810000

I was migrate the DB to 2 collections in MongoDB, similar with 2 tables in PostgreSQL. But now, I'm confuse how to query so I have same result in MongoDB? Or should I change the structure from 2 tables to just 1 single collection in MongoDB? Any thought? TIA.

Upvotes: 0

Views: 88

Answers (1)

dgnk
dgnk

Reputation: 98

You can join two collections by using $lookup(Aggregation)

{$lookup:
 {
   from: <collection to join>,
   localField: <field from the input documents>,
   foreignField: <field from the documents of the "from" collection>,
   as: <output array field>
 }}

But it is recommended to create both tables as a single collection Mongodb is meant to use like that.

Upvotes: 1

Related Questions