[SERVER-19476] Referenced Values Created: 17/Jul/15  Updated: 09/Jul/16  Resolved: 17/Jul/15

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Minor - P4
Reporter: Bradley Arsenault Assignee: Sam Kleinman (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-745 Embedded Document Expansion (Pseudo-J... Closed
duplicates SERVER-19095 $lookup Closed
duplicates SERVER-432 server side expansion of DBRefs Closed
Backwards Compatibility: Fully Compatible
Participants:

 Description   

Here is a crazy new feature idea that handles a very common problem when dealing with databases. It concerns when you have a field on Table A which is always meant to be synchronized with a field of a referenced object on Table B.

Say you have the following tables:

Person

{ name: String, job: ObjectID }

Job

{ title: String, description: String }

Each person object references the job that the person works. For various reasons, such as indexing, request performance, or convenience, you may actually want a copy of the persons job title on the Person object itself, like so:

Person

{ name: String, job: ObjectID, jobTitle: String }

The jobTitle field is always just copied from whatever job object is referenced. This isn't amazingly clean, and it does result in duplication of data - in the ideal world it wouldn't be necessary. But this does come up a lot in practice in real world programming.

The main problem is keeping the jobTitle field up to date. Every time and place that we ever change the title field on the Job object, we have to ensure that we update every Person object which references that Job. This is a nightmare to maintain and these bugs are the bane of my existence.

What I would like to see in my database is some sort of Referenced Value or Foreign Value functionality. Instead of actually setting the jobTitle to a string, I might set it to a referenced value like so:

Person

{ name: "bob", job: ObjectId(55a7fd9c99d1c7a113000043), jobTitle: ReferencedValue("job.title") }

Now anytime I request a Person object, I will get the correct corresponding jobTitle which is contained within the referenced Job object. If the Job doesn't exist, jobTitle might return null. And if I change the corresponding Job object, Mongo will automatically update all Person objects which reference it to have the new title.

This might not be easy to implement, but it would save developers a hell of a lot of pain from referential integrity related issues. And its in keeping with the spirit of MongoDB, which is to be a powerful, fully featured database which makes development easier by handling more of the functionality itself.



 Comments   
Comment by Bradley Arsenault [ 17/Jul/15 ]

$lookup looks fascinating and quite useful, but doesn't cover the critical use cases that I desire this feature for, which is the need for having indexes on the foreign referenced value. $lookup will only allow me to retrieve the field when making the query - it won't allow me to index on it

Tricky, yes. I hope in the long term you will consider handling this trickyness in the Mongo Database so that I don't have to in my application.

Comment by Sam Kleinman (Inactive) [ 17/Jul/15 ]

Thanks for the request, and overview! There are a lot of complexities around this kind of feature particularly in the context of sharded systems, and all document updates. For these kinds of server-side query operations, the $lookup operation, currently in developmen (see SERVER-19095,) which will make it possible to produce the kind of output you're looking for using the Aggregation facility. Many drivers and ODM tools also support these kinds of references, using dbRefs, which makes sense for a large number of applications. Similarly for updating multiple documents, application level two-phase-commits can help you maintain consistency more easily. But you're right: these sorts of issues are complex and frustrating.

I hope this makes sense. I'm going to go ahead and close this issue, but if I've misunderstood something please feel free to be in touch.

Regards,
sam

Generated at Thu Feb 08 03:51:05 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.