Group and lookup not showing the projected key of lookup collection

Pawan_Sonole · 2024-09-09T06:10:03.696Z

Hi Community,

I am stuck at one oracle sql to mongodb query conversion please find below sql query:

SELECT distinct
delta.c1,
block.c1,
block.c3
FROM
(
SELECT
c1,
c2,
c3,
SUM(cstate_a) cstate_a,
SUM(cstate_b) cstate_b
FROM t1
WHERE c2 = ‘1234’
GROUP BY c1, c2, c3
) delta,
t2 block
WHERE delta.c3 = block.c3;

converted above oracle sql to mongo query

db.t1.aggregate([

{
$match: { “c2”: “1234” }
},
{
$group:
{
_id: { c1: “$c1”,
c2: “$c2”,
c3: “$c3”
},
total_Active: { $sum: “$cstate_a” },
total_Administrative: { $sum: “$cstate_b” }
}
},
{
$lookup:
{
from: “t2”,
localField: “c2” ,
foreignField: “c1”,
as: “blck”
}
},
{
$project:
{
_id: 0,
ocn: “$blck.c1”,
total_Active: 1,
total_Administrative:1,
lata : “$blck.lata”
}
},
]);

o/p
{ total_Active: 23, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: },
{ total_Active: 0, total_Administrative: 0, lata: }

when I execute i am not able to get the data key i.e for collection blck - lata its showing blank, can anyone please guide whats the issue here ?

John_Sewell · 2024-09-09T07:05:02.873Z

Easier if you actually provided data so someone can test it ideally in mongo playground. However your group stage is putting the grouped fields under the _id and then you’re trying to do a $lookup on the fields as they were.
Comment out stages as you build the query to identify thing like this, it’s a critical way to debug issues.

Pawan_Sonole · 2024-09-09T15:07:32.362Z

Hi Sir,

Thank you for you time, I got the solution and getting the data as expected.

Thanks,
Pawan

John_Sewell · 2024-09-09T15:09:16.795Z

Excellent, glad you got it working.