Mongo Aggregate Objects with $lookup using non matching values
I've got an Object Mission referring to another object Position with a key _p_position.
Mission objects look like:
{
_id: "ijjn97678",
_p_position: "Position$qwerty123",
...
}
Position objects look like:
{
_id: "qwerty123",
...
}
I don't know if it is Mongo or Parse convention but as one can see a Position$ is added on relational position attribute in missions.
I'd like to aggregate both into a single Object to get a results similar to the following:
{
_id: "ijjn97678",
_p_position: "Position$qwerty123",
positions: [
{
_id: "qwerty123"
}
]
}
using:
missions.aggregate([
{
$lookup: {
as: "position",
from: "Position",
foreignField: "_id",
localField: "_p_position",
},
},
])
But I need to remove Position$ from _p_position. Is there a way I can compute "_p_position" before it is used to find a matching Position's id ?
PS: I only have reading rights on DB
mongodb
edited Nov 14 '18 at 18:32
mickl
13.5k51536
asked Nov 14 '18 at 16:08
theFreedomBananatheFreedomBanana
1,2231021
add a comment |
I've got an Object Mission referring to another object Position with a key _p_position.
Mission objects look like:
{
_id: "ijjn97678",
_p_position: "Position$qwerty123",
...
}
Position objects look like:
{
_id: "qwerty123",
...
}
I don't know if it is Mongo or Parse convention but as one can see a Position$ is added on relational position attribute in missions.
I'd like to aggregate both into a single Object to get a results similar to the following:
{
_id: "ijjn97678",
_p_position: "Position$qwerty123",
positions: [
{
_id: "qwerty123"
}
]
}
using:
missions.aggregate([
{
$lookup: {
as: "position",
from: "Position",
foreignField: "_id",
localField: "_p_position",
},
},
])
But I need to remove Position$ from _p_position. Is there a way I can compute "_p_position" before it is used to find a matching Position's id ?
PS: I only have reading rights on DB
mongodb
edited Nov 14 '18 at 18:32
mickl
13.5k51536
asked Nov 14 '18 at 16:08
theFreedomBananatheFreedomBanana
1,2231021
1
If you're using 3.6 or above, you can use join condition . Provide samples of your collections if you need help.
– matthPen
Nov 14 '18 at 16:45
@matthPen I've been looking atjoin conditionand tried to useletto pass a variable into thepipelinebut I can't aggregate a Position into the final object. If you can provide a piece of code it would be very much appreciated. As for a sample of collections I don't use much more than an_idfor Position and_p_positionin Mission
– theFreedomBanana
Nov 14 '18 at 17:52
add a comment |
I've got an Object Mission referring to another object Position with a key _p_position.
Mission objects look like:
{
_id: "ijjn97678",
_p_position: "Position$qwerty123",
...
}
Position objects look like:
{
_id: "qwerty123",
...
}
I don't know if it is Mongo or Parse convention but as one can see a Position$ is added on relational position attribute in missions.
I'd like to aggregate both into a single Object to get a results similar to the following:
{
_id: "ijjn97678",
_p_position: "Position$qwerty123",
positions: [
{
_id: "qwerty123"
}
]
}
using:
missions.aggregate([
{
$lookup: {
as: "position",
from: "Position",
foreignField: "_id",
localField: "_p_position",
},
},
])
But I need to remove Position$ from _p_position. Is there a way I can compute "_p_position" before it is used to find a matching Position's id ?
PS: I only have reading rights on DB
mongodb
edited Nov 14 '18 at 18:32
mickl
13.5k51536
asked Nov 14 '18 at 16:08
theFreedomBananatheFreedomBanana
1,2231021
I've got an Object Mission referring to another object Position with a key _p_position.
Mission objects look like:
{
_id: "ijjn97678",
_p_position: "Position$qwerty123",
...
}
Position objects look like:
{
_id: "qwerty123",
...
}
I don't know if it is Mongo or Parse convention but as one can see a Position$ is added on relational position attribute in missions.
I'd like to aggregate both into a single Object to get a results similar to the following:
{
_id: "ijjn97678",
_p_position: "Position$qwerty123",
positions: [
{
_id: "qwerty123"
}
]
}
using:
missions.aggregate([
{
$lookup: {
as: "position",
from: "Position",
foreignField: "_id",
localField: "_p_position",
},
},
])
But I need to remove Position$ from _p_position. Is there a way I can compute "_p_position" before it is used to find a matching Position's id ?
PS: I only have reading rights on DB
mongodb
mongodb
edited Nov 14 '18 at 18:32
mickl
13.5k51536
asked Nov 14 '18 at 16:08
theFreedomBananatheFreedomBanana
1,2231021
edited Nov 14 '18 at 18:32
mickl
13.5k51536
asked Nov 14 '18 at 16:08
theFreedomBananatheFreedomBanana
1,2231021
edited Nov 14 '18 at 18:32
mickl
13.5k51536
edited Nov 14 '18 at 18:32
mickl
13.5k51536
edited Nov 14 '18 at 18:32
mickl
13.5k51536
13.5k51536
asked Nov 14 '18 at 16:08
theFreedomBananatheFreedomBanana
1,2231021
asked Nov 14 '18 at 16:08
theFreedomBananatheFreedomBanana
1,2231021
asked Nov 14 '18 at 16:08
theFreedomBananatheFreedomBanana
1,2231021
1,2231021
1
If you're using 3.6 or above, you can use join condition . Provide samples of your collections if you need help.
– matthPen
Nov 14 '18 at 16:45
@matthPen I've been looking atjoin conditionand tried to useletto pass a variable into thepipelinebut I can't aggregate a Position into the final object. If you can provide a piece of code it would be very much appreciated. As for a sample of collections I don't use much more than an_idfor Position and_p_positionin Mission
– theFreedomBanana
Nov 14 '18 at 17:52
add a comment |
1
If you're using 3.6 or above, you can use join condition . Provide samples of your collections if you need help.
– matthPen
Nov 14 '18 at 16:45
@matthPen I've been looking atjoin conditionand tried to useletto pass a variable into thepipelinebut I can't aggregate a Position into the final object. If you can provide a piece of code it would be very much appreciated. As for a sample of collections I don't use much more than an_idfor Position and_p_positionin Mission
– theFreedomBanana
Nov 14 '18 at 17:52
1
1
If you're using 3.6 or above, you can use join condition . Provide samples of your collections if you need help.
– matthPen
Nov 14 '18 at 16:45
If you're using 3.6 or above, you can use join condition . Provide samples of your collections if you need help.
– matthPen
Nov 14 '18 at 16:45
@matthPen I've been looking at
join condition and tried to use let to pass a variable into the pipeline but I can't aggregate a Position into the final object. If you can provide a piece of code it would be very much appreciated. As for a sample of collections I don't use much more than an _id for Position and _p_position in Mission– theFreedomBanana
Nov 14 '18 at 17:52
@matthPen I've been looking at
join condition and tried to use let to pass a variable into the pipeline but I can't aggregate a Position into the final object. If you can provide a piece of code it would be very much appreciated. As for a sample of collections I don't use much more than an _id for Position and _p_position in Mission– theFreedomBanana
Nov 14 '18 at 17:52
add a comment |
1 Answer
1
active
oldest
votes
You can use $addFields to add another field which will be then passed to $lookup stage. To get the part that's following the dollar sign you need: $indexOfBytes and $substr operators. Additionally dollar sign itself is a special character in Aggregation Framework (represents a field reference) so you need $literal to force it to be considered as regular field
db.missions.aggregate([
{
$addFields: {
value: {
$let: {
vars: { index: { $indexOfBytes: [ "$_p_position", { $literal: "$" } ] } },
in: { $substr: [ "$_p_position", { $add: [ "$$index", 1 ] } , { $strLenBytes: "$_p_position" } ] }
}
}
}
},
{
$lookup: {
from: "Position",
localField: "value",
foreignField: "_id",
as: "position"
}
}
])
answered Nov 14 '18 at 18:05
micklmickl
13.5k51536
I only have reading rights on DB :(
– theFreedomBanana
Nov 14 '18 at 18:21
1
@theFreedomBanana this solution is not modifying your data, it's just adding temporary field for aggregation pipeline purpose, just try it :)
– mickl
Nov 14 '18 at 18:24
Worked indeed! I owe you one. Thxs
– theFreedomBanana
Nov 14 '18 at 18:36
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304388%2fmongo-aggregate-objects-with-lookup-using-non-matching-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use $addFields to add another field which will be then passed to $lookup stage. To get the part that's following the dollar sign you need: $indexOfBytes and $substr operators. Additionally dollar sign itself is a special character in Aggregation Framework (represents a field reference) so you need $literal to force it to be considered as regular field
db.missions.aggregate([
{
$addFields: {
value: {
$let: {
vars: { index: { $indexOfBytes: [ "$_p_position", { $literal: "$" } ] } },
in: { $substr: [ "$_p_position", { $add: [ "$$index", 1 ] } , { $strLenBytes: "$_p_position" } ] }
}
}
}
},
{
$lookup: {
from: "Position",
localField: "value",
foreignField: "_id",
as: "position"
}
}
])
answered Nov 14 '18 at 18:05
micklmickl
13.5k51536
I only have reading rights on DB :(
– theFreedomBanana
Nov 14 '18 at 18:21
1
@theFreedomBanana this solution is not modifying your data, it's just adding temporary field for aggregation pipeline purpose, just try it :)
– mickl
Nov 14 '18 at 18:24
Worked indeed! I owe you one. Thxs
– theFreedomBanana
Nov 14 '18 at 18:36
add a comment |
You can use $addFields to add another field which will be then passed to $lookup stage. To get the part that's following the dollar sign you need: $indexOfBytes and $substr operators. Additionally dollar sign itself is a special character in Aggregation Framework (represents a field reference) so you need $literal to force it to be considered as regular field
db.missions.aggregate([
{
$addFields: {
value: {
$let: {
vars: { index: { $indexOfBytes: [ "$_p_position", { $literal: "$" } ] } },
in: { $substr: [ "$_p_position", { $add: [ "$$index", 1 ] } , { $strLenBytes: "$_p_position" } ] }
}
}
}
},
{
$lookup: {
from: "Position",
localField: "value",
foreignField: "_id",
as: "position"
}
}
])
answered Nov 14 '18 at 18:05
micklmickl
13.5k51536
I only have reading rights on DB :(
– theFreedomBanana
Nov 14 '18 at 18:21
1
@theFreedomBanana this solution is not modifying your data, it's just adding temporary field for aggregation pipeline purpose, just try it :)
– mickl
Nov 14 '18 at 18:24
Worked indeed! I owe you one. Thxs
– theFreedomBanana
Nov 14 '18 at 18:36
add a comment |
You can use $addFields to add another field which will be then passed to $lookup stage. To get the part that's following the dollar sign you need: $indexOfBytes and $substr operators. Additionally dollar sign itself is a special character in Aggregation Framework (represents a field reference) so you need $literal to force it to be considered as regular field
db.missions.aggregate([
{
$addFields: {
value: {
$let: {
vars: { index: { $indexOfBytes: [ "$_p_position", { $literal: "$" } ] } },
in: { $substr: [ "$_p_position", { $add: [ "$$index", 1 ] } , { $strLenBytes: "$_p_position" } ] }
}
}
}
},
{
$lookup: {
from: "Position",
localField: "value",
foreignField: "_id",
as: "position"
}
}
])
answered Nov 14 '18 at 18:05
micklmickl
13.5k51536
You can use $addFields to add another field which will be then passed to $lookup stage. To get the part that's following the dollar sign you need: $indexOfBytes and $substr operators. Additionally dollar sign itself is a special character in Aggregation Framework (represents a field reference) so you need $literal to force it to be considered as regular field
db.missions.aggregate([
{
$addFields: {
value: {
$let: {
vars: { index: { $indexOfBytes: [ "$_p_position", { $literal: "$" } ] } },
in: { $substr: [ "$_p_position", { $add: [ "$$index", 1 ] } , { $strLenBytes: "$_p_position" } ] }
}
}
}
},
{
$lookup: {
from: "Position",
localField: "value",
foreignField: "_id",
as: "position"
}
}
])
answered Nov 14 '18 at 18:05
micklmickl
13.5k51536
answered Nov 14 '18 at 18:05
micklmickl
13.5k51536
answered Nov 14 '18 at 18:05
micklmickl
13.5k51536
answered Nov 14 '18 at 18:05
micklmickl
13.5k51536
13.5k51536
I only have reading rights on DB :(
– theFreedomBanana
Nov 14 '18 at 18:21
1
@theFreedomBanana this solution is not modifying your data, it's just adding temporary field for aggregation pipeline purpose, just try it :)
– mickl
Nov 14 '18 at 18:24
Worked indeed! I owe you one. Thxs
– theFreedomBanana
Nov 14 '18 at 18:36
add a comment |
I only have reading rights on DB :(
– theFreedomBanana
Nov 14 '18 at 18:21
1
@theFreedomBanana this solution is not modifying your data, it's just adding temporary field for aggregation pipeline purpose, just try it :)
– mickl
Nov 14 '18 at 18:24
Worked indeed! I owe you one. Thxs
– theFreedomBanana
Nov 14 '18 at 18:36
I only have reading rights on DB :(
– theFreedomBanana
Nov 14 '18 at 18:21
I only have reading rights on DB :(
– theFreedomBanana
Nov 14 '18 at 18:21
1
1
@theFreedomBanana this solution is not modifying your data, it's just adding temporary field for aggregation pipeline purpose, just try it :)
– mickl
Nov 14 '18 at 18:24
@theFreedomBanana this solution is not modifying your data, it's just adding temporary field for aggregation pipeline purpose, just try it :)
– mickl
Nov 14 '18 at 18:24
Worked indeed! I owe you one. Thxs
– theFreedomBanana
Nov 14 '18 at 18:36
Worked indeed! I owe you one. Thxs
– theFreedomBanana
Nov 14 '18 at 18:36
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304388%2fmongo-aggregate-objects-with-lookup-using-non-matching-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
If you're using 3.6 or above, you can use join condition . Provide samples of your collections if you need help.
– matthPen
Nov 14 '18 at 16:45
@matthPen I've been looking at
join conditionand tried to useletto pass a variable into thepipelinebut I can't aggregate a Position into the final object. If you can provide a piece of code it would be very much appreciated. As for a sample of collections I don't use much more than an_idfor Position and_p_positionin Mission– theFreedomBanana
Nov 14 '18 at 17:52