CODEWITHSUNDEEP
joinazure-cosmosdb
Sam
Sam

Reputation: 4339

Inner join on the parent document from within a child

I have a document that looks like this:

{
    "id": "some guid",
    "name": "Bob",
    "following": [
        {
            "id": "some_guid",
            "priority": true
        }
    ]
}

The idea is that a user can follow other users.

What I would like to do is, for a given user get the IDs and names of all users that they are following. The name is not stored with in the "following" collection.

If I were using SQL, I'd be doing something like this:

SELECT u.following_id, u.priority, j.name FROM users INNER JOIN j on u.following_id = j.id.

What is the equivalent in DocumentDb SQL?

Upvotes: 1

Views: 302

Answers (1)

Larry Maccherone
Larry Maccherone

Reputation: 9523

The equivalent in DocumentDB is to do two round trips. First fetch the user document containing the array of who that user is following. Then build up a big OR clause and use that to fetch the documents of who they are following.

Alternatively, you may want to store the relationship in the other direction. So instead of storing who one user follows in that user's document, store the followedBy list in each of the followers. You can then do an ARRAY_CONTAINS query in one round trip.

You also might want to look at Azure's stream analytics product because it has graph querying capabilities.

Upvotes: 3

Related Questions