How to transform a SQL self join to a MongoDB query

Working with Data
1

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

{pers_id: “1”}
{pers_id: “3”}

How can i do??

2

Hello, @Annsa_Lisa, welcome to the community!
You can achieve the desired result with .find() command

db.your_collection.find({ type: 'A' }, { _id: false, pers_id: true });

Or using .aggregate() command:

db.your_collection.aggregate([
  {
     $match: {
        type: 'A',         
     },    
  },
  {
     $project {
        _id: false,   
        pers_id: true,
     },    
  },
]);
3

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

4

I would $group by pers_id using $addToSet the type in a types array and then $match out documents that have type B in the types array.

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