How to do a One-to-Many Query in MongoDB/PyMongo

Question

I am pretty new to MongoDB/NoSQL, but I have experience with RDBMS like MySql and Postgres from school/work. I am trying to do a one-to-many query between collections in the same database.

The two collections are a Users collection (i.e. legislators/committees/caucuses) and a Tweets collection (i.e. tweets from said Users).

This is what a Tweet looks like:

{
    "Twitter Screen Name": "IlhanMN",
    "Twitter Id": "783792992",
    "Time Created": {
        "$date": "2021-04-17T04:10:40.000Z"
    },
    "Text": "@BettyMcCollum04 @Ilhan ❤️✊",
    "Source": "Twitter for iPhone"
}

And this is what a User looks like:

{
    "Name": "Ilhan Omar",
    "Chamber": "house",
    "Party": "D",
    "State": "MN",
    "Twitter Screen Name": "IlhanMN",
    "Twitter Id": "783792992"
}

Basically, I am using the Twitter Screen Name and Twitter id as a Document Reference/Primary Key to link one User to their many tweets. I wanted to try and normalize the collections to make it simpler and similar to SQL so I decided not to embed tweets into a User document since then it would have gotten inefficient since the Users are tweeting multiple times a day.

I am having trouble formulating these queries because the PyMongo documentation I have been seeing on creating queries is for basic standalone collections where you are just trying to find one document based on a field and not a one-to-many type collection using a Document Reference.

One example query I am having trouble formulating is getting all tweets for a specific user. You can hardcode the Twitter Id of a User into a query and query the Tweets collection, but it's not practical on the front-end where a person may not know their legislator's Twitter Id off of the top of their head and only know their legislator's name in the Users collection.

So from the Users collection, based on a document with that specific name (Ilhan Omar), get that User documents Twitter Id and/or Twitter Screen Name (783792992/IlhanMN), with the Twitter Id and/or Twitter Screen Name now search through the Tweets collection and find all tweets with said Twitter Id and/or Twitter Screen Name.

user_7911215 · Answer

Just saw pipelines and aggregation. This pipe line allows me to get all tweets for a certain user using their name to match and then the Twitter Id of matched user to get their tweets.

    name_lookup_pipeline = [{'$match': {'Name':'Ilhan Omar'}},
                            {'$lookup':{
                                'from':'Tweets',
                                'localField':'Twitter Id',
                                'foreignField':'Twitter Id',
                                'as':'users_tweets'
                            }}]

How to do a One-to-Many Query in MongoDB/PyMongo

1 Answers