How to return a nested query as a sub-object in MySQL?
0
I have a query that gets some meta data about an object, and then fetches comments that were made on that object. I would like my express framework to send the comments back as a nested object, but instead it's sending multiple "rows" with duplicated data.
Query result/response sent to client:
[ RowDataPacket {
[0] first_name: 'bob',
[0] last_name: 'masd',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] WriterFname: 'billy',
[0] WriterLname: 'flimbob',
[0] text: 'M COMMENT',
[0] CmtWrittenAt: 2018-11-21T17:37:02.000Z },
[0] RowDataPacket {
[0] first_name: 'bob',
[0] last_name: 'masd',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] WriterFname: 'jim',
[0] WriterLname: 'lurk',
[0] text: 'RESPONSE',
[0] CmtWrittenAt: 2018-11-21T17:37:03.000Z } ]
This is the relevant stuff from my query
SELECT
...
GROUP_CONCAT(DISTINCT Tag.name) AS tags,
Cmt.first_name AS WriterFname,
Cmt.last_name AS WriterLname,
Cmt.text,
Cmt.datetime_created AS CmtWrittenAt
FROM
...
INNER JOIN (
SELECT
User.first_name,
User.last_name,
Comment.text,
Comment.datetime_created
FROM
Comment
INNER JOIN User ON User.userID = Comment.userID
WHERE
Comment.pushpinID = ?
) AS Cmt
...
GROUP BY
...
WriterFname,WriterLname,CmtWrittenAt,Cmt.text
As you can see I'm already grouping 'tags' and sending that back as an array, and I'd like to group Comment/Cmt as a nested object.
I have gotten close by using JSON_OBJECT('fname',Cmt.first_name,'lname',Cmt.last_name,'text',Cmt.text,'time',Cmt.datetime_created) AS Comment, which gives the following:
[0] [ RowDataPacket {
[0] first_name: 'zxc',
[0] last_name: 'zxc',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] Comment:
[0] '{"text": "M COMMENT", "time": "2018-11-21 09:37:02.000000", "fname": "asd", "lname": "asd"}' },
[0] RowDataPacket {
[0] first_name: 'zxc',
[0] last_name: 'zxc',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] Comment:
[0] '{"text": "RESPONSE", "time": "2018-11-21 09:37:03.000000", "fname": "qwe", "lname": "qwe"}' } ]
But this still has a lot of duplicated data
mysql sql node.js
edited Nov 21 '18 at 19:17
tadman
153k18173207
asked Nov 21 '18 at 19:03
James L.James L.
2,94711529
|
show 2 more comments
0
I have a query that gets some meta data about an object, and then fetches comments that were made on that object. I would like my express framework to send the comments back as a nested object, but instead it's sending multiple "rows" with duplicated data.
Query result/response sent to client:
[ RowDataPacket {
[0] first_name: 'bob',
[0] last_name: 'masd',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] WriterFname: 'billy',
[0] WriterLname: 'flimbob',
[0] text: 'M COMMENT',
[0] CmtWrittenAt: 2018-11-21T17:37:02.000Z },
[0] RowDataPacket {
[0] first_name: 'bob',
[0] last_name: 'masd',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] WriterFname: 'jim',
[0] WriterLname: 'lurk',
[0] text: 'RESPONSE',
[0] CmtWrittenAt: 2018-11-21T17:37:03.000Z } ]
This is the relevant stuff from my query
SELECT
...
GROUP_CONCAT(DISTINCT Tag.name) AS tags,
Cmt.first_name AS WriterFname,
Cmt.last_name AS WriterLname,
Cmt.text,
Cmt.datetime_created AS CmtWrittenAt
FROM
...
INNER JOIN (
SELECT
User.first_name,
User.last_name,
Comment.text,
Comment.datetime_created
FROM
Comment
INNER JOIN User ON User.userID = Comment.userID
WHERE
Comment.pushpinID = ?
) AS Cmt
...
GROUP BY
...
WriterFname,WriterLname,CmtWrittenAt,Cmt.text
As you can see I'm already grouping 'tags' and sending that back as an array, and I'd like to group Comment/Cmt as a nested object.
I have gotten close by using JSON_OBJECT('fname',Cmt.first_name,'lname',Cmt.last_name,'text',Cmt.text,'time',Cmt.datetime_created) AS Comment, which gives the following:
[0] [ RowDataPacket {
[0] first_name: 'zxc',
[0] last_name: 'zxc',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] Comment:
[0] '{"text": "M COMMENT", "time": "2018-11-21 09:37:02.000000", "fname": "asd", "lname": "asd"}' },
[0] RowDataPacket {
[0] first_name: 'zxc',
[0] last_name: 'zxc',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] Comment:
[0] '{"text": "RESPONSE", "time": "2018-11-21 09:37:03.000000", "fname": "qwe", "lname": "qwe"}' } ]
But this still has a lot of duplicated data
mysql sql node.js
edited Nov 21 '18 at 19:17
tadman
153k18173207
asked Nov 21 '18 at 19:03
James L.James L.
2,94711529
1
SQL deals with simple tables of data, there's no hierarchical data. If you want to organize the results, you need to do that in JavaScript. See stackoverflow.com/questions/24302630/… for example.
– Barmar
Nov 21 '18 at 19:53
@Barmar there's no way to return hierarchical data? I was thinking I could return each column as an array using JSON_ARRAYAGG which seems to work ok, although it has duplicated the rows. At that point may be better to just write a second query I guess
– James L.
Nov 21 '18 at 20:00
You don't need a second query. Just go through the array of results and combine the related rows in the JavaScript array of objects.
– Barmar
Nov 21 '18 at 20:02
@Barmar that's a possibility, im just trying to do it all in SQL
– James L.
Nov 21 '18 at 20:03
2
There is no way to return hierarchical data in mysql period. If you return json or xml from a query, that is still just a string value as far as mysql is concerned. You may be able to use json and other string aggregation functions to produce an output that can be interpreted by js as hierarchical dataset, but that is unlikely to be an efficient solution. I would also construct the hierarchical dataset within js based on a simple, flat sql resultset.
– Shadow
Nov 21 '18 at 21:39
|
show 2 more comments
0
0
0
I have a query that gets some meta data about an object, and then fetches comments that were made on that object. I would like my express framework to send the comments back as a nested object, but instead it's sending multiple "rows" with duplicated data.
Query result/response sent to client:
[ RowDataPacket {
[0] first_name: 'bob',
[0] last_name: 'masd',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] WriterFname: 'billy',
[0] WriterLname: 'flimbob',
[0] text: 'M COMMENT',
[0] CmtWrittenAt: 2018-11-21T17:37:02.000Z },
[0] RowDataPacket {
[0] first_name: 'bob',
[0] last_name: 'masd',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] WriterFname: 'jim',
[0] WriterLname: 'lurk',
[0] text: 'RESPONSE',
[0] CmtWrittenAt: 2018-11-21T17:37:03.000Z } ]
This is the relevant stuff from my query
SELECT
...
GROUP_CONCAT(DISTINCT Tag.name) AS tags,
Cmt.first_name AS WriterFname,
Cmt.last_name AS WriterLname,
Cmt.text,
Cmt.datetime_created AS CmtWrittenAt
FROM
...
INNER JOIN (
SELECT
User.first_name,
User.last_name,
Comment.text,
Comment.datetime_created
FROM
Comment
INNER JOIN User ON User.userID = Comment.userID
WHERE
Comment.pushpinID = ?
) AS Cmt
...
GROUP BY
...
WriterFname,WriterLname,CmtWrittenAt,Cmt.text
As you can see I'm already grouping 'tags' and sending that back as an array, and I'd like to group Comment/Cmt as a nested object.
I have gotten close by using JSON_OBJECT('fname',Cmt.first_name,'lname',Cmt.last_name,'text',Cmt.text,'time',Cmt.datetime_created) AS Comment, which gives the following:
[0] [ RowDataPacket {
[0] first_name: 'zxc',
[0] last_name: 'zxc',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] Comment:
[0] '{"text": "M COMMENT", "time": "2018-11-21 09:37:02.000000", "fname": "asd", "lname": "asd"}' },
[0] RowDataPacket {
[0] first_name: 'zxc',
[0] last_name: 'zxc',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] Comment:
[0] '{"text": "RESPONSE", "time": "2018-11-21 09:37:03.000000", "fname": "qwe", "lname": "qwe"}' } ]
But this still has a lot of duplicated data
mysql sql node.js
edited Nov 21 '18 at 19:17
tadman
153k18173207
asked Nov 21 '18 at 19:03
James L.James L.
2,94711529
I have a query that gets some meta data about an object, and then fetches comments that were made on that object. I would like my express framework to send the comments back as a nested object, but instead it's sending multiple "rows" with duplicated data.
Query result/response sent to client:
[ RowDataPacket {
[0] first_name: 'bob',
[0] last_name: 'masd',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] WriterFname: 'billy',
[0] WriterLname: 'flimbob',
[0] text: 'M COMMENT',
[0] CmtWrittenAt: 2018-11-21T17:37:02.000Z },
[0] RowDataPacket {
[0] first_name: 'bob',
[0] last_name: 'masd',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] WriterFname: 'jim',
[0] WriterLname: 'lurk',
[0] text: 'RESPONSE',
[0] CmtWrittenAt: 2018-11-21T17:37:03.000Z } ]
This is the relevant stuff from my query
SELECT
...
GROUP_CONCAT(DISTINCT Tag.name) AS tags,
Cmt.first_name AS WriterFname,
Cmt.last_name AS WriterLname,
Cmt.text,
Cmt.datetime_created AS CmtWrittenAt
FROM
...
INNER JOIN (
SELECT
User.first_name,
User.last_name,
Comment.text,
Comment.datetime_created
FROM
Comment
INNER JOIN User ON User.userID = Comment.userID
WHERE
Comment.pushpinID = ?
) AS Cmt
...
GROUP BY
...
WriterFname,WriterLname,CmtWrittenAt,Cmt.text
As you can see I'm already grouping 'tags' and sending that back as an array, and I'd like to group Comment/Cmt as a nested object.
I have gotten close by using JSON_OBJECT('fname',Cmt.first_name,'lname',Cmt.last_name,'text',Cmt.text,'time',Cmt.datetime_created) AS Comment, which gives the following:
[0] [ RowDataPacket {
[0] first_name: 'zxc',
[0] last_name: 'zxc',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] Comment:
[0] '{"text": "M COMMENT", "time": "2018-11-21 09:37:02.000000", "fname": "asd", "lname": "asd"}' },
[0] RowDataPacket {
[0] first_name: 'zxc',
[0] last_name: 'zxc',
[0] corkboardID: 1,
[0] title: 'asd',
[0] datetime_created: 2018-11-21T17:37:02.000Z,
[0] image_url: 'http://example.com',
[0] description: 'asdf',
[0] tags: 'awesome,cool',
[0] Comment:
[0] '{"text": "RESPONSE", "time": "2018-11-21 09:37:03.000000", "fname": "qwe", "lname": "qwe"}' } ]
But this still has a lot of duplicated data
mysql sql node.js
mysql sql node.js
edited Nov 21 '18 at 19:17
tadman
153k18173207
asked Nov 21 '18 at 19:03
James L.James L.
2,94711529
edited Nov 21 '18 at 19:17
tadman
153k18173207
asked Nov 21 '18 at 19:03
James L.James L.
2,94711529
edited Nov 21 '18 at 19:17
tadman
153k18173207
edited Nov 21 '18 at 19:17
tadman
153k18173207
edited Nov 21 '18 at 19:17
tadman
153k18173207
153k18173207
asked Nov 21 '18 at 19:03
James L.James L.
2,94711529
asked Nov 21 '18 at 19:03
James L.James L.
2,94711529
asked Nov 21 '18 at 19:03
James L.James L.
2,94711529
2,94711529
1
SQL deals with simple tables of data, there's no hierarchical data. If you want to organize the results, you need to do that in JavaScript. See stackoverflow.com/questions/24302630/… for example.
– Barmar
Nov 21 '18 at 19:53
@Barmar there's no way to return hierarchical data? I was thinking I could return each column as an array using JSON_ARRAYAGG which seems to work ok, although it has duplicated the rows. At that point may be better to just write a second query I guess
– James L.
Nov 21 '18 at 20:00
You don't need a second query. Just go through the array of results and combine the related rows in the JavaScript array of objects.
– Barmar
Nov 21 '18 at 20:02
@Barmar that's a possibility, im just trying to do it all in SQL
– James L.
Nov 21 '18 at 20:03
2
There is no way to return hierarchical data in mysql period. If you return json or xml from a query, that is still just a string value as far as mysql is concerned. You may be able to use json and other string aggregation functions to produce an output that can be interpreted by js as hierarchical dataset, but that is unlikely to be an efficient solution. I would also construct the hierarchical dataset within js based on a simple, flat sql resultset.
– Shadow
Nov 21 '18 at 21:39
|
show 2 more comments
1
SQL deals with simple tables of data, there's no hierarchical data. If you want to organize the results, you need to do that in JavaScript. See stackoverflow.com/questions/24302630/… for example.
– Barmar
Nov 21 '18 at 19:53
@Barmar there's no way to return hierarchical data? I was thinking I could return each column as an array using JSON_ARRAYAGG which seems to work ok, although it has duplicated the rows. At that point may be better to just write a second query I guess
– James L.
Nov 21 '18 at 20:00
You don't need a second query. Just go through the array of results and combine the related rows in the JavaScript array of objects.
– Barmar
Nov 21 '18 at 20:02
@Barmar that's a possibility, im just trying to do it all in SQL
– James L.
Nov 21 '18 at 20:03
2
There is no way to return hierarchical data in mysql period. If you return json or xml from a query, that is still just a string value as far as mysql is concerned. You may be able to use json and other string aggregation functions to produce an output that can be interpreted by js as hierarchical dataset, but that is unlikely to be an efficient solution. I would also construct the hierarchical dataset within js based on a simple, flat sql resultset.
– Shadow
Nov 21 '18 at 21:39
1
1
SQL deals with simple tables of data, there's no hierarchical data. If you want to organize the results, you need to do that in JavaScript. See stackoverflow.com/questions/24302630/… for example.
– Barmar
Nov 21 '18 at 19:53
SQL deals with simple tables of data, there's no hierarchical data. If you want to organize the results, you need to do that in JavaScript. See stackoverflow.com/questions/24302630/… for example.
– Barmar
Nov 21 '18 at 19:53
@Barmar there's no way to return hierarchical data? I was thinking I could return each column as an array using JSON_ARRAYAGG which seems to work ok, although it has duplicated the rows. At that point may be better to just write a second query I guess
– James L.
Nov 21 '18 at 20:00
@Barmar there's no way to return hierarchical data? I was thinking I could return each column as an array using JSON_ARRAYAGG which seems to work ok, although it has duplicated the rows. At that point may be better to just write a second query I guess
– James L.
Nov 21 '18 at 20:00
You don't need a second query. Just go through the array of results and combine the related rows in the JavaScript array of objects.
– Barmar
Nov 21 '18 at 20:02
You don't need a second query. Just go through the array of results and combine the related rows in the JavaScript array of objects.
– Barmar
Nov 21 '18 at 20:02
@Barmar that's a possibility, im just trying to do it all in SQL
– James L.
Nov 21 '18 at 20:03
@Barmar that's a possibility, im just trying to do it all in SQL
– James L.
Nov 21 '18 at 20:03
2
2
There is no way to return hierarchical data in mysql period. If you return json or xml from a query, that is still just a string value as far as mysql is concerned. You may be able to use json and other string aggregation functions to produce an output that can be interpreted by js as hierarchical dataset, but that is unlikely to be an efficient solution. I would also construct the hierarchical dataset within js based on a simple, flat sql resultset.
– Shadow
Nov 21 '18 at 21:39
There is no way to return hierarchical data in mysql period. If you return json or xml from a query, that is still just a string value as far as mysql is concerned. You may be able to use json and other string aggregation functions to produce an output that can be interpreted by js as hierarchical dataset, but that is unlikely to be an efficient solution. I would also construct the hierarchical dataset within js based on a simple, flat sql resultset.
– Shadow
Nov 21 '18 at 21:39
|
show 2 more comments
0
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f53418934%2fhow-to-return-a-nested-query-as-a-sub-object-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53418934%2fhow-to-return-a-nested-query-as-a-sub-object-in-mysql%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
SQL deals with simple tables of data, there's no hierarchical data. If you want to organize the results, you need to do that in JavaScript. See stackoverflow.com/questions/24302630/… for example.
– Barmar
Nov 21 '18 at 19:53
@Barmar there's no way to return hierarchical data? I was thinking I could return each column as an array using JSON_ARRAYAGG which seems to work ok, although it has duplicated the rows. At that point may be better to just write a second query I guess
– James L.
Nov 21 '18 at 20:00
You don't need a second query. Just go through the array of results and combine the related rows in the JavaScript array of objects.
– Barmar
Nov 21 '18 at 20:02
@Barmar that's a possibility, im just trying to do it all in SQL
– James L.
Nov 21 '18 at 20:03
2
There is no way to return hierarchical data in mysql period. If you return json or xml from a query, that is still just a string value as far as mysql is concerned. You may be able to use json and other string aggregation functions to produce an output that can be interpreted by js as hierarchical dataset, but that is unlikely to be an efficient solution. I would also construct the hierarchical dataset within js based on a simple, flat sql resultset.
– Shadow
Nov 21 '18 at 21:39