I have sql Query below
SELECT PC.CampaignID
,COUNT(PC.ProspectID)
FROM ProspectCampaign as PC WITH(NOLOCK)
INNER JOIN Prospect P WITH(NOLOCK) ON P.ProspectID = PC.ProspectID AND P.ClientID = PC.ClientID
INNER JOIN Campaign C WITH(NOLOCK) ON C.CampaignID = PC.CampaignID AND C.ClientID = PC.ClientID
WHERE
C.IsDeleted=0
AND P.IsDeleted = 0
AND ISNULL(P.IsContactOptOut,0) = IIF(ISNULL(C.IsIncludeBlacklistedContacts,0) =0 ,0,ISNULL(P.IsContactOptOut,0))
AND ISNULL(P.IsDomainBlackListed,0) = CASE WHEN ISNULL(P.IsContatcWhiteListed,0) = 0 AND ISNUll(C.IsIncludeBlacklistedContacts,0) = 0
THEN 0 ELSE ISNULL(P.IsDomainBlackListed,0) END
AND ISNULL(P.IsContatcBlackListed,0) = CASE WHEN ISNULL(P.IsContatcWhiteListed,0) = 0 AND ISNUll(C.IsIncludeBlacklistedContacts,0) = 0
THEN 0 ELSE ISNULL(P.IsContatcBlackListed,0) END
AND ISNULL(P.IsGlobalCountryBlacklist,0) = CASE WHEN ISNULL(P.IsContatcWhiteListed,0) = 0 AND ISNUll(C.IsIncludeBlacklistedContacts,0) = 0
THEN 0 ELSE ISNULL(P.IsGlobalCountryBlacklist,0) END
AND ISNULL(P.IsGlobalDomainBlacklist,0) = CASE WHEN ISNULL(P.IsContatcWhiteListed,0) = 0 AND ISNUll(C.IsIncludeBlacklistedContacts,0) = 0
THEN 0 ELSE isnull(P.IsGlobalDomainBlacklist,0) END
AND ISNULL(P.IsDefaultCountryBlacklist,0) = CASE WHEN ISNULL(P.IsContatcWhiteListed,0) = 0 AND ISNUll(C.IsIncludeBlacklistedContacts,0) = 0
THEN 0 ELSE ISNULL(P.IsDefaultCountryBlacklist,0) END
AND ISNULL(P.IsGlobalContactBlacklist,0) = CASE WHEN ISNULL(P.IsContatcWhiteListed,0) = 0 AND ISNUll(C.IsIncludeBlacklistedContacts,0) = 0
THEN 0 ELSE ISNULL(P.IsGlobalContactBlacklist,0) END
AND P.IsGlobalBounce = 0
AND PC.ClientID = 411
GROUP BY PC.CampaignID
But i cant find to build this AND ISNULL(P.IsContactOptOut,0) = IIF(ISNULL(C.IsIncludeBlacklistedContacts,0) =0 ,0,ISNULL(P.IsContactOptOut,0)) in mongo db.
Please help me to build this query in mongodb.
const d = await ProspectCampaign.aggregate(
[
{
"$lookup":{
from: 'Prospect',
let: { id: "$ProspectID",cid:"$ClientID", flag: false },
pipeline: [
{ $match:
{ $expr:
{ $and:
[
{ $eq: [ "$ProspectID", "$$id" ] },
{ $eq: [ "$ClientID", "$$cid" ] },
{ $eq: [ "$IsDeleted", "$$flag" ] },
{ $eq: [ "$IsGlobalBounce", "$$flag"]}
]
}
}
}
],
as: "p1"
}
},
{
"$unwind":{
path: "$p1",
preserveNullAndEmptyArrays: false
}
},
{
"$addFields":{
"p1IsContactOptOut": "$p1.IsContactOptOut",
"p1IsDomainBlackListed":"$p1.IsDomainBlackListed",
"p1IsContatcBlackListed":"$p1.IsContatcBlackListed",
"p1IsGlobalCountryBlacklist":"$p1.IsGlobalCountryBlacklist",
"p1IsGlobalDomainBlacklist":"$p1.IsGlobalDomainBlacklist",
"p1IsDefaultCountryBlacklist":"$p1.IsDefaultCountryBlacklist",
"p1IsGlobalContactBlacklist":"$p1.IsGlobalContactBlacklist"
}
},
{
"$lookup":{
from: 'Campaign',
let: { id: "$CampaignID",cid:"$ClientID", flag: false },
pipeline: [
{ $match:
{ $expr:
{ $and:
[
{ $eq: [ "$CampaignID", "$$id" ] },
{ $eq: [ "$ClientID", "$$cid" ] },
{ $eq: [ "$IsDeleted", "$$flag" ] },
]
}
}
}
],
as: "p2"
}
},
{
"$unwind":{
path: "$p2",
preserveNullAndEmptyArrays: false
}
},
{
"$match":{
IsDeleted:false,
ClientID:411,
UserID:17,
}
},
{
"$match":{
$expr:{
$and:[
{"p1IsContactOptOut":{$cond:{ if: { $eq: [ "$p2.IsIncludeBlacklistedContacts", false ] }, then: false, else: "$p1IsContactOptOut" }}},
{"p1IsDomainBlackListed":{$cond:{ if: { $and:[{$eq:["$p1.IsContatcWhiteListed",false]},{$eq:["$p2.IsIncludeBlacklistedContacts",false]}] }, then: false, else: "$p1IsDomainBlackListed" }}},
{"p1IsContatcBlackListed":{$cond:{ if: { $and:[{$eq:["$p1.IsContatcWhiteListed",false]},{$eq:["$p2.IsIncludeBlacklistedContacts",false]}] }, then: false, else: "$p1IsContatcBlackListed" }}},
{"p1IsGlobalCountryBlacklist":{$cond:{ if: { $and:[{$eq:["$p1.IsContatcWhiteListed",false]},{$eq:["$p2.IsIncludeBlacklistedContacts",false]}] }, then: false, else: "$p1IsGlobalCountryBlacklist" }}},
{"p1IsGlobalDomainBlacklist":{$cond:{ if: { $and:[{$eq:["$p1.IsContatcWhiteListed",false]},{$eq:["$p2.IsIncludeBlacklistedContacts",false]}] }, then: false, else: "$p1IsGlobalDomainBlacklist" }}},
{"p1IsDefaultCountryBlacklist":{$cond:{ if: { $and:[{$eq:["$p1.IsContatcWhiteListed",false]},{$eq:["$p2.IsIncludeBlacklistedContacts",false]}] }, then: false, else: "$p1IsDefaultCountryBlacklist" }}},
{"p1IsGlobalContactBlacklist":{$cond:{ if: { $and:[{$eq:["$p1.IsContatcWhiteListed",false]},{$eq:["$p2.IsIncludeBlacklistedContacts",false]}] }, then: false, else: "$p1IsGlobalContactBlacklist" }}}
]
}
}
},
{
"$group":{
_id:"$CampaignID",
Count: {
$sum:1
}
}
}
])
this is what i try.
Thank you in advance.