is it possible to get data of objects of a list using mysql script?
have a json query that gives me json of a joined table of person and pets:
SELECT
json_object(
'personId', p.id,
'firstPetName', p.firstPetName,
'pets', json_arrayagg(
json_object(
'petId', pt.id,
'petName', pt.name
)
)
)
FROM person p
LEFT JOIN pets pt ON p.id = pt.person_id
GROUP BY p.id;
I want to add a condition that if p.firstPetName is null or empty string to put the pt.petName of the first pet in the pets list, is that possible?
example:
if this is the result:
{
"personId": 1,
"firstPetName": null // or ""
"pets": [
{
"petName": "walker"
},
{
"petName": "roxi"
}
]
}
I'd would like to get:
{
"personId": 1,
"firstPetName": "walker"
"pets": [
{
"petName": "walker"
},
{
"petName": "roxi"
}
]
}
if it was possible to do something like pt[0].petName it was awesome
mysql
asked Dec 28 '18 at 14:47
jack miaojack miao
470520
|
show 3 more comments
have a json query that gives me json of a joined table of person and pets:
SELECT
json_object(
'personId', p.id,
'firstPetName', p.firstPetName,
'pets', json_arrayagg(
json_object(
'petId', pt.id,
'petName', pt.name
)
)
)
FROM person p
LEFT JOIN pets pt ON p.id = pt.person_id
GROUP BY p.id;
I want to add a condition that if p.firstPetName is null or empty string to put the pt.petName of the first pet in the pets list, is that possible?
example:
if this is the result:
{
"personId": 1,
"firstPetName": null // or ""
"pets": [
{
"petName": "walker"
},
{
"petName": "roxi"
}
]
}
I'd would like to get:
{
"personId": 1,
"firstPetName": "walker"
"pets": [
{
"petName": "walker"
},
{
"petName": "roxi"
}
]
}
if it was possible to do something like pt[0].petName it was awesome
mysql
asked Dec 28 '18 at 14:47
jack miaojack miao
470520
1
As there is noORDER BYclause in the query, how do we know for sure what is the first pet name ?
– GMB
Dec 28 '18 at 15:18
@DanielE. no cant count on that...
– jack miao
Dec 28 '18 at 15:21
@GMB just the first pet object in the list.
– jack miao
Dec 28 '18 at 15:22
@DanielE. just the first pet object in the list. like if it was possible to do something likept[0].petNamethat would be good
– jack miao
Dec 28 '18 at 15:23
@jackmiao then how is it the first item in the list ? With your example I really think it's the id.
– Daniel E.
Dec 28 '18 at 15:27
|
show 3 more comments
have a json query that gives me json of a joined table of person and pets:
SELECT
json_object(
'personId', p.id,
'firstPetName', p.firstPetName,
'pets', json_arrayagg(
json_object(
'petId', pt.id,
'petName', pt.name
)
)
)
FROM person p
LEFT JOIN pets pt ON p.id = pt.person_id
GROUP BY p.id;
I want to add a condition that if p.firstPetName is null or empty string to put the pt.petName of the first pet in the pets list, is that possible?
example:
if this is the result:
{
"personId": 1,
"firstPetName": null // or ""
"pets": [
{
"petName": "walker"
},
{
"petName": "roxi"
}
]
}
I'd would like to get:
{
"personId": 1,
"firstPetName": "walker"
"pets": [
{
"petName": "walker"
},
{
"petName": "roxi"
}
]
}
if it was possible to do something like pt[0].petName it was awesome
mysql
asked Dec 28 '18 at 14:47
jack miaojack miao
470520
have a json query that gives me json of a joined table of person and pets:
SELECT
json_object(
'personId', p.id,
'firstPetName', p.firstPetName,
'pets', json_arrayagg(
json_object(
'petId', pt.id,
'petName', pt.name
)
)
)
FROM person p
LEFT JOIN pets pt ON p.id = pt.person_id
GROUP BY p.id;
I want to add a condition that if p.firstPetName is null or empty string to put the pt.petName of the first pet in the pets list, is that possible?
example:
if this is the result:
{
"personId": 1,
"firstPetName": null // or ""
"pets": [
{
"petName": "walker"
},
{
"petName": "roxi"
}
]
}
I'd would like to get:
{
"personId": 1,
"firstPetName": "walker"
"pets": [
{
"petName": "walker"
},
{
"petName": "roxi"
}
]
}
if it was possible to do something like pt[0].petName it was awesome
mysql
mysql
asked Dec 28 '18 at 14:47
jack miaojack miao
470520
asked Dec 28 '18 at 14:47
jack miaojack miao
470520
edited Dec 28 '18 at 15:26
jack miao
asked Dec 28 '18 at 14:47
jack miaojack miao
470520
asked Dec 28 '18 at 14:47
jack miaojack miao
470520
asked Dec 28 '18 at 14:47
jack miaojack miao
470520
470520
1
As there is noORDER BYclause in the query, how do we know for sure what is the first pet name ?
– GMB
Dec 28 '18 at 15:18
@DanielE. no cant count on that...
– jack miao
Dec 28 '18 at 15:21
@GMB just the first pet object in the list.
– jack miao
Dec 28 '18 at 15:22
@DanielE. just the first pet object in the list. like if it was possible to do something likept[0].petNamethat would be good
– jack miao
Dec 28 '18 at 15:23
@jackmiao then how is it the first item in the list ? With your example I really think it's the id.
– Daniel E.
Dec 28 '18 at 15:27
|
show 3 more comments
1
As there is noORDER BYclause in the query, how do we know for sure what is the first pet name ?
– GMB
Dec 28 '18 at 15:18
@DanielE. no cant count on that...
– jack miao
Dec 28 '18 at 15:21
@GMB just the first pet object in the list.
– jack miao
Dec 28 '18 at 15:22
@DanielE. just the first pet object in the list. like if it was possible to do something likept[0].petNamethat would be good
– jack miao
Dec 28 '18 at 15:23
@jackmiao then how is it the first item in the list ? With your example I really think it's the id.
– Daniel E.
Dec 28 '18 at 15:27
1
1
As there is no
ORDER BY clause in the query, how do we know for sure what is the first pet name ?– GMB
Dec 28 '18 at 15:18
As there is no
ORDER BY clause in the query, how do we know for sure what is the first pet name ?– GMB
Dec 28 '18 at 15:18
@DanielE. no cant count on that...
– jack miao
Dec 28 '18 at 15:21
@DanielE. no cant count on that...
– jack miao
Dec 28 '18 at 15:21
@GMB just the first pet object in the list.
– jack miao
Dec 28 '18 at 15:22
@GMB just the first pet object in the list.
– jack miao
Dec 28 '18 at 15:22
@DanielE. just the first pet object in the list. like if it was possible to do something like
pt[0].petName that would be good– jack miao
Dec 28 '18 at 15:23
@DanielE. just the first pet object in the list. like if it was possible to do something like
pt[0].petName that would be good– jack miao
Dec 28 '18 at 15:23
@jackmiao then how is it the first item in the list ? With your example I really think it's the id.
– Daniel E.
Dec 28 '18 at 15:27
@jackmiao then how is it the first item in the list ? With your example I really think it's the id.
– Daniel E.
Dec 28 '18 at 15:27
|
show 3 more comments
1 Answer
1
active
oldest
votes
From mysql documentation :
JSON_ARRAYAGG(col_or_expr) [over_clause]
Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.
Hence, in your use case, the order of pets in the list is not defined. You cannot know for sure which pet will appear first in the json array generated by the json_arrayagg call.
The following query gives an answer that defaults to pet with the minimum name. The COALESCE function is then used to provide a default value for p.firstPetName.
SELECT
json_object(
'personId', p.id,
'firstPetName', COALESCE(p.firstPetName, MIN(pt.name))
'pets', json_arrayagg(
json_object(
'petId', pt.id,
'petName', pt.name
)
)
)
FROM
person p
LEFT JOIN pets pt ON p.id = pt.person_id
GROUP BY p.id;
answered Dec 28 '18 at 15:27
GMBGMB
6,8602520
Walker > Roxi, we can't answer until we know the order :/
– Daniel E.
Dec 28 '18 at 15:29
@DanielE / Walker.: but will we ever get it ?... Anyway I guess you are right, let me put my answer on hold for the time being...
– GMB
Dec 28 '18 at 15:32
@DanielE. there is no sort criteria, I just need the first object that is in the list, the head..
– jack miao
Dec 28 '18 at 16:47
@DanielE. : well I guess we raeally can't know the order, see my updated answer
– GMB
Dec 28 '18 at 16:58
@GMB you right, thanks for the detailed answer. luckily in my specific case that works too. thanks
– jack miao
Dec 28 '18 at 20:09
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%2f53960300%2fis-it-possible-to-get-data-of-objects-of-a-list-using-mysql-script%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
From mysql documentation :
JSON_ARRAYAGG(col_or_expr) [over_clause]
Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.
Hence, in your use case, the order of pets in the list is not defined. You cannot know for sure which pet will appear first in the json array generated by the json_arrayagg call.
The following query gives an answer that defaults to pet with the minimum name. The COALESCE function is then used to provide a default value for p.firstPetName.
SELECT
json_object(
'personId', p.id,
'firstPetName', COALESCE(p.firstPetName, MIN(pt.name))
'pets', json_arrayagg(
json_object(
'petId', pt.id,
'petName', pt.name
)
)
)
FROM
person p
LEFT JOIN pets pt ON p.id = pt.person_id
GROUP BY p.id;
answered Dec 28 '18 at 15:27
GMBGMB
6,8602520
Walker > Roxi, we can't answer until we know the order :/
– Daniel E.
Dec 28 '18 at 15:29
@DanielE / Walker.: but will we ever get it ?... Anyway I guess you are right, let me put my answer on hold for the time being...
– GMB
Dec 28 '18 at 15:32
@DanielE. there is no sort criteria, I just need the first object that is in the list, the head..
– jack miao
Dec 28 '18 at 16:47
@DanielE. : well I guess we raeally can't know the order, see my updated answer
– GMB
Dec 28 '18 at 16:58
@GMB you right, thanks for the detailed answer. luckily in my specific case that works too. thanks
– jack miao
Dec 28 '18 at 20:09
add a comment |
From mysql documentation :
JSON_ARRAYAGG(col_or_expr) [over_clause]
Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.
Hence, in your use case, the order of pets in the list is not defined. You cannot know for sure which pet will appear first in the json array generated by the json_arrayagg call.
The following query gives an answer that defaults to pet with the minimum name. The COALESCE function is then used to provide a default value for p.firstPetName.
SELECT
json_object(
'personId', p.id,
'firstPetName', COALESCE(p.firstPetName, MIN(pt.name))
'pets', json_arrayagg(
json_object(
'petId', pt.id,
'petName', pt.name
)
)
)
FROM
person p
LEFT JOIN pets pt ON p.id = pt.person_id
GROUP BY p.id;
answered Dec 28 '18 at 15:27
GMBGMB
6,8602520
Walker > Roxi, we can't answer until we know the order :/
– Daniel E.
Dec 28 '18 at 15:29
@DanielE / Walker.: but will we ever get it ?... Anyway I guess you are right, let me put my answer on hold for the time being...
– GMB
Dec 28 '18 at 15:32
@DanielE. there is no sort criteria, I just need the first object that is in the list, the head..
– jack miao
Dec 28 '18 at 16:47
@DanielE. : well I guess we raeally can't know the order, see my updated answer
– GMB
Dec 28 '18 at 16:58
@GMB you right, thanks for the detailed answer. luckily in my specific case that works too. thanks
– jack miao
Dec 28 '18 at 20:09
add a comment |
From mysql documentation :
JSON_ARRAYAGG(col_or_expr) [over_clause]
Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.
Hence, in your use case, the order of pets in the list is not defined. You cannot know for sure which pet will appear first in the json array generated by the json_arrayagg call.
The following query gives an answer that defaults to pet with the minimum name. The COALESCE function is then used to provide a default value for p.firstPetName.
SELECT
json_object(
'personId', p.id,
'firstPetName', COALESCE(p.firstPetName, MIN(pt.name))
'pets', json_arrayagg(
json_object(
'petId', pt.id,
'petName', pt.name
)
)
)
FROM
person p
LEFT JOIN pets pt ON p.id = pt.person_id
GROUP BY p.id;
answered Dec 28 '18 at 15:27
GMBGMB
6,8602520
From mysql documentation :
JSON_ARRAYAGG(col_or_expr) [over_clause]
Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.
Hence, in your use case, the order of pets in the list is not defined. You cannot know for sure which pet will appear first in the json array generated by the json_arrayagg call.
The following query gives an answer that defaults to pet with the minimum name. The COALESCE function is then used to provide a default value for p.firstPetName.
SELECT
json_object(
'personId', p.id,
'firstPetName', COALESCE(p.firstPetName, MIN(pt.name))
'pets', json_arrayagg(
json_object(
'petId', pt.id,
'petName', pt.name
)
)
)
FROM
person p
LEFT JOIN pets pt ON p.id = pt.person_id
GROUP BY p.id;
answered Dec 28 '18 at 15:27
GMBGMB
6,8602520
edited Dec 28 '18 at 16:56
answered Dec 28 '18 at 15:27
GMBGMB
6,8602520
answered Dec 28 '18 at 15:27
GMBGMB
6,8602520
answered Dec 28 '18 at 15:27
GMBGMB
6,8602520
6,8602520
Walker > Roxi, we can't answer until we know the order :/
– Daniel E.
Dec 28 '18 at 15:29
@DanielE / Walker.: but will we ever get it ?... Anyway I guess you are right, let me put my answer on hold for the time being...
– GMB
Dec 28 '18 at 15:32
@DanielE. there is no sort criteria, I just need the first object that is in the list, the head..
– jack miao
Dec 28 '18 at 16:47
@DanielE. : well I guess we raeally can't know the order, see my updated answer
– GMB
Dec 28 '18 at 16:58
@GMB you right, thanks for the detailed answer. luckily in my specific case that works too. thanks
– jack miao
Dec 28 '18 at 20:09
add a comment |
Walker > Roxi, we can't answer until we know the order :/
– Daniel E.
Dec 28 '18 at 15:29
@DanielE / Walker.: but will we ever get it ?... Anyway I guess you are right, let me put my answer on hold for the time being...
– GMB
Dec 28 '18 at 15:32
@DanielE. there is no sort criteria, I just need the first object that is in the list, the head..
– jack miao
Dec 28 '18 at 16:47
@DanielE. : well I guess we raeally can't know the order, see my updated answer
– GMB
Dec 28 '18 at 16:58
@GMB you right, thanks for the detailed answer. luckily in my specific case that works too. thanks
– jack miao
Dec 28 '18 at 20:09
Walker > Roxi, we can't answer until we know the order :/
– Daniel E.
Dec 28 '18 at 15:29
Walker > Roxi, we can't answer until we know the order :/
– Daniel E.
Dec 28 '18 at 15:29
@DanielE / Walker.: but will we ever get it ?... Anyway I guess you are right, let me put my answer on hold for the time being...
– GMB
Dec 28 '18 at 15:32
@DanielE / Walker.: but will we ever get it ?... Anyway I guess you are right, let me put my answer on hold for the time being...
– GMB
Dec 28 '18 at 15:32
@DanielE. there is no sort criteria, I just need the first object that is in the list, the head..
– jack miao
Dec 28 '18 at 16:47
@DanielE. there is no sort criteria, I just need the first object that is in the list, the head..
– jack miao
Dec 28 '18 at 16:47
@DanielE. : well I guess we raeally can't know the order, see my updated answer
– GMB
Dec 28 '18 at 16:58
@DanielE. : well I guess we raeally can't know the order, see my updated answer
– GMB
Dec 28 '18 at 16:58
@GMB you right, thanks for the detailed answer. luckily in my specific case that works too. thanks
– jack miao
Dec 28 '18 at 20:09
@GMB you right, thanks for the detailed answer. luckily in my specific case that works too. thanks
– jack miao
Dec 28 '18 at 20:09
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%2f53960300%2fis-it-possible-to-get-data-of-objects-of-a-list-using-mysql-script%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
As there is no
ORDER BYclause in the query, how do we know for sure what is the first pet name ?– GMB
Dec 28 '18 at 15:18
@DanielE. no cant count on that...
– jack miao
Dec 28 '18 at 15:21
@GMB just the first pet object in the list.
– jack miao
Dec 28 '18 at 15:22
@DanielE. just the first pet object in the list. like if it was possible to do something like
pt[0].petNamethat would be good– jack miao
Dec 28 '18 at 15:23
@jackmiao then how is it the first item in the list ? With your example I really think it's the id.
– Daniel E.
Dec 28 '18 at 15:27