How to transform a SQL self join to a MongoDB query

Working with Data
5

Hello @slava

your statement will be the transformation of:
SELECT PERS_ID FROM table WHERE type=‘A’

@Annsa_Lisa is looking for all pers_id with type A but not B

this can be archived as @Ramachandra_Tummala already wrote.

Here is the code for it:

db.selfjoin.aggregate([
    {   '$group': {
            '_id': '$pers_id',
            'type': { '$addToSet': '$type' }
        }
    },{ 
        '$match': { 'type': { $ne: 'B' } } 
}]);

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);

Cheers,
Michael

4 Likes