Hi everybody!
I want to show you a problem:
in RDBMS i would like to do this query
SELECT PERS_ID FROM table WHERE type=‘A’ and PERS_ID not in (SELECT PERS_ID from TABLE where type=‘B’)
how can i do this query in mongo?
I have
{pers_id: “1”, type: “A”}
{pers_id: “2”, type: “A”}
{pers_id: “2”, type: “B”}
{pers_id: “3”, type: “A”}
{pers_id: “4”, type: “B”}
and i want to see as result
I don’t think that is what I want…
I want to show collections where the pers_id with type A doesn’t have any other document of type B with the same pers_id.
Can you help me?
Thanks
In case you use VSCode as editor you can, or may already have, add the mongodb plugin which was introduced here from @Massimiliano_Marcon and run the attached code as playground:
// MongoDB Playground
// Select the database to use.
use('test');
// The drop() command destroys all data from a collection.
db.selfjoin.drop();
// Insert a few documents into the selfjoin collection.
db.selfjoin.insertMany([
{ 'pers_id' : '1', 'type' : 'A' },
{ 'pers_id' : '2', 'type' : 'A' },
{ 'pers_id' : '2', 'type' : 'B' },
{ 'pers_id' : '3', 'type' : 'A' },
{ 'pers_id' : '4', 'type' : 'B' },
]);
// Run an aggregation
const aggregation = [
{ '$group': {
'_id': '$pers_id',
'type': { '$addToSet': '$type' }
}
},{
'$match': { 'type': { $ne: 'B' } }
}];
db.selfjoin.aggregate(aggregation);