add condition to mysql json_arrayagg function
I have a json query that gives me json of a joined table of person and pets:
SELECT json_object(
'personId', p.id,
'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;
my issue is that person can have 0 or more pets, and when a person have 0 pets I get list with 1 empty pet, and what I would like to get in that case is empty list.
this is what I get:
{
"personId": 1,
"pets": [
{
"petId": null,
"petName": ""
}
]
}
and I need:
{
"personId": 1,
"pets":
}
is that possible?
mysql
asked Dec 28 '18 at 0:32
jack miao
462519
add a comment |
I have a json query that gives me json of a joined table of person and pets:
SELECT json_object(
'personId', p.id,
'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;
my issue is that person can have 0 or more pets, and when a person have 0 pets I get list with 1 empty pet, and what I would like to get in that case is empty list.
this is what I get:
{
"personId": 1,
"pets": [
{
"petId": null,
"petName": ""
}
]
}
and I need:
{
"personId": 1,
"pets":
}
is that possible?
mysql
asked Dec 28 '18 at 0:32
jack miao
462519
Duplicate of stackoverflow.com/questions/49960156/…
– Barmar
Dec 28 '18 at 0:55
add a comment |
I have a json query that gives me json of a joined table of person and pets:
SELECT json_object(
'personId', p.id,
'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;
my issue is that person can have 0 or more pets, and when a person have 0 pets I get list with 1 empty pet, and what I would like to get in that case is empty list.
this is what I get:
{
"personId": 1,
"pets": [
{
"petId": null,
"petName": ""
}
]
}
and I need:
{
"personId": 1,
"pets":
}
is that possible?
mysql
asked Dec 28 '18 at 0:32
jack miao
462519
I have a json query that gives me json of a joined table of person and pets:
SELECT json_object(
'personId', p.id,
'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;
my issue is that person can have 0 or more pets, and when a person have 0 pets I get list with 1 empty pet, and what I would like to get in that case is empty list.
this is what I get:
{
"personId": 1,
"pets": [
{
"petId": null,
"petName": ""
}
]
}
and I need:
{
"personId": 1,
"pets":
}
is that possible?
mysql
mysql
asked Dec 28 '18 at 0:32
jack miao
462519
asked Dec 28 '18 at 0:32
jack miao
462519
asked Dec 28 '18 at 0:32
jack miao
462519
asked Dec 28 '18 at 0:32
jack miao
462519
asked Dec 28 '18 at 0:32
jack miao
462519
462519
Duplicate of stackoverflow.com/questions/49960156/…
– Barmar
Dec 28 '18 at 0:55
add a comment |
Duplicate of stackoverflow.com/questions/49960156/…
– Barmar
Dec 28 '18 at 0:55
Duplicate of stackoverflow.com/questions/49960156/…
– Barmar
Dec 28 '18 at 0:55
Duplicate of stackoverflow.com/questions/49960156/…
– Barmar
Dec 28 '18 at 0:55
add a comment |
2 Answers
2
active
oldest
votes
The problem is that LEFT JOIN still returns columns from the table you're joining with, it just sets their values to NULL.
You can use IF to test COUNT(pt.id), as this won't count null values.
SELECT json_object(
'personId', p.id,
'pets', IF(COUNT(pt.id) = 0, JSON_ARRAY(),
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 0:50
Barmar
420k35244344
add a comment |
Another possibility is to put the aggregation in a correlated subquery and use coalesce() to replace it with an empty array if no rows exist.
SELECT json_object('personID', p.id,
'pets', coalesce((SELECT json_arrayagg(json_object('petId', t.id,
'petName', t.name))
FROM pets t
WHERE t.person_id = p.id),
json_array()))
FROM person p;
answered Dec 28 '18 at 1:00
sticky bit
13k71632
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%2f53952383%2fadd-condition-to-mysql-json-arrayagg-function%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The problem is that LEFT JOIN still returns columns from the table you're joining with, it just sets their values to NULL.
You can use IF to test COUNT(pt.id), as this won't count null values.
SELECT json_object(
'personId', p.id,
'pets', IF(COUNT(pt.id) = 0, JSON_ARRAY(),
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 0:50
Barmar
420k35244344
add a comment |
The problem is that LEFT JOIN still returns columns from the table you're joining with, it just sets their values to NULL.
You can use IF to test COUNT(pt.id), as this won't count null values.
SELECT json_object(
'personId', p.id,
'pets', IF(COUNT(pt.id) = 0, JSON_ARRAY(),
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 0:50
Barmar
420k35244344
add a comment |
The problem is that LEFT JOIN still returns columns from the table you're joining with, it just sets their values to NULL.
You can use IF to test COUNT(pt.id), as this won't count null values.
SELECT json_object(
'personId', p.id,
'pets', IF(COUNT(pt.id) = 0, JSON_ARRAY(),
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 0:50
Barmar
420k35244344
The problem is that LEFT JOIN still returns columns from the table you're joining with, it just sets their values to NULL.
You can use IF to test COUNT(pt.id), as this won't count null values.
SELECT json_object(
'personId', p.id,
'pets', IF(COUNT(pt.id) = 0, JSON_ARRAY(),
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 0:50
Barmar
420k35244344
answered Dec 28 '18 at 0:50
Barmar
420k35244344
answered Dec 28 '18 at 0:50
Barmar
420k35244344
answered Dec 28 '18 at 0:50
Barmar
420k35244344
420k35244344
add a comment |
add a comment |
Another possibility is to put the aggregation in a correlated subquery and use coalesce() to replace it with an empty array if no rows exist.
SELECT json_object('personID', p.id,
'pets', coalesce((SELECT json_arrayagg(json_object('petId', t.id,
'petName', t.name))
FROM pets t
WHERE t.person_id = p.id),
json_array()))
FROM person p;
answered Dec 28 '18 at 1:00
sticky bit
13k71632
add a comment |
Another possibility is to put the aggregation in a correlated subquery and use coalesce() to replace it with an empty array if no rows exist.
SELECT json_object('personID', p.id,
'pets', coalesce((SELECT json_arrayagg(json_object('petId', t.id,
'petName', t.name))
FROM pets t
WHERE t.person_id = p.id),
json_array()))
FROM person p;
answered Dec 28 '18 at 1:00
sticky bit
13k71632
add a comment |
Another possibility is to put the aggregation in a correlated subquery and use coalesce() to replace it with an empty array if no rows exist.
SELECT json_object('personID', p.id,
'pets', coalesce((SELECT json_arrayagg(json_object('petId', t.id,
'petName', t.name))
FROM pets t
WHERE t.person_id = p.id),
json_array()))
FROM person p;
answered Dec 28 '18 at 1:00
sticky bit
13k71632
Another possibility is to put the aggregation in a correlated subquery and use coalesce() to replace it with an empty array if no rows exist.
SELECT json_object('personID', p.id,
'pets', coalesce((SELECT json_arrayagg(json_object('petId', t.id,
'petName', t.name))
FROM pets t
WHERE t.person_id = p.id),
json_array()))
FROM person p;
answered Dec 28 '18 at 1:00
sticky bit
13k71632
answered Dec 28 '18 at 1:00
sticky bit
13k71632
answered Dec 28 '18 at 1:00
sticky bit
13k71632
answered Dec 28 '18 at 1:00
sticky bit
13k71632
13k71632
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53952383%2fadd-condition-to-mysql-json-arrayagg-function%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
Duplicate of stackoverflow.com/questions/49960156/…
– Barmar
Dec 28 '18 at 0:55