Subquery for retrieving count
I'm trying to select all parents with the count of their children.
I have the following query:
SELECT
a.*,
(SELECT COUNT(*) FROM demo b WHERE b.parent = a.name) as count
FROM demo a
WHERE
meta(a).id LIKE "xyz:%"
AND a.parent IS MISSING
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0
My documents look similar to:
xyz:1
{
id: 1,
name: "parent",
createdAt: 1234
}
xyz:2
{
id: 2,
name: "child",
parent: "parent",
createdAt: 5678
}
I get the below error:
Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM demo.
Error code: 5010
UPDATE:
The below query seem to work:
SELECT
a.*,
(SELECT COUNT(id) as count FROM demo b WHERE b.parent = "parent")[0].count as count
FROM demo a
WHERE
meta(a).id LIKE "xyz:%"
AND a.parent IS MISSING
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0
but if I replace "parent" with a.name it gives the same error.
couchbase n1ql
edited Jan 3 at 16:44
Johan Larson
1,4951813
asked Jan 3 at 8:04
Niket MalikNiket Malik
6801920
add a comment |
I'm trying to select all parents with the count of their children.
I have the following query:
SELECT
a.*,
(SELECT COUNT(*) FROM demo b WHERE b.parent = a.name) as count
FROM demo a
WHERE
meta(a).id LIKE "xyz:%"
AND a.parent IS MISSING
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0
My documents look similar to:
xyz:1
{
id: 1,
name: "parent",
createdAt: 1234
}
xyz:2
{
id: 2,
name: "child",
parent: "parent",
createdAt: 5678
}
I get the below error:
Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM demo.
Error code: 5010
UPDATE:
The below query seem to work:
SELECT
a.*,
(SELECT COUNT(id) as count FROM demo b WHERE b.parent = "parent")[0].count as count
FROM demo a
WHERE
meta(a).id LIKE "xyz:%"
AND a.parent IS MISSING
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0
but if I replace "parent" with a.name it gives the same error.
couchbase n1ql
edited Jan 3 at 16:44
Johan Larson
1,4951813
asked Jan 3 at 8:04
Niket MalikNiket Malik
6801920
add a comment |
I'm trying to select all parents with the count of their children.
I have the following query:
SELECT
a.*,
(SELECT COUNT(*) FROM demo b WHERE b.parent = a.name) as count
FROM demo a
WHERE
meta(a).id LIKE "xyz:%"
AND a.parent IS MISSING
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0
My documents look similar to:
xyz:1
{
id: 1,
name: "parent",
createdAt: 1234
}
xyz:2
{
id: 2,
name: "child",
parent: "parent",
createdAt: 5678
}
I get the below error:
Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM demo.
Error code: 5010
UPDATE:
The below query seem to work:
SELECT
a.*,
(SELECT COUNT(id) as count FROM demo b WHERE b.parent = "parent")[0].count as count
FROM demo a
WHERE
meta(a).id LIKE "xyz:%"
AND a.parent IS MISSING
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0
but if I replace "parent" with a.name it gives the same error.
couchbase n1ql
edited Jan 3 at 16:44
Johan Larson
1,4951813
asked Jan 3 at 8:04
Niket MalikNiket Malik
6801920
I'm trying to select all parents with the count of their children.
I have the following query:
SELECT
a.*,
(SELECT COUNT(*) FROM demo b WHERE b.parent = a.name) as count
FROM demo a
WHERE
meta(a).id LIKE "xyz:%"
AND a.parent IS MISSING
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0
My documents look similar to:
xyz:1
{
id: 1,
name: "parent",
createdAt: 1234
}
xyz:2
{
id: 2,
name: "child",
parent: "parent",
createdAt: 5678
}
I get the below error:
Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM demo.
Error code: 5010
UPDATE:
The below query seem to work:
SELECT
a.*,
(SELECT COUNT(id) as count FROM demo b WHERE b.parent = "parent")[0].count as count
FROM demo a
WHERE
meta(a).id LIKE "xyz:%"
AND a.parent IS MISSING
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0
but if I replace "parent" with a.name it gives the same error.
couchbase n1ql
couchbase n1ql
edited Jan 3 at 16:44
Johan Larson
1,4951813
asked Jan 3 at 8:04
Niket MalikNiket Malik
6801920
edited Jan 3 at 16:44
Johan Larson
1,4951813
asked Jan 3 at 8:04
Niket MalikNiket Malik
6801920
edited Jan 3 at 16:44
Johan Larson
1,4951813
edited Jan 3 at 16:44
Johan Larson
1,4951813
edited Jan 3 at 16:44
Johan Larson
1,4951813
1,4951813
asked Jan 3 at 8:04
Niket MalikNiket Malik
6801920
asked Jan 3 at 8:04
Niket MalikNiket Malik
6801920
asked Jan 3 at 8:04
Niket MalikNiket Malik
6801920
6801920
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:
SELECT l.name, COUNT(1)
FROM demo l JOIN demo r ON r.name = l.child
GROUP BY l.name
To run this query you will need an index on the r side:
CREATE INDEX demo_name_idx ON demo(name)
Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.
answered Jan 3 at 13:09
Johan LarsonJohan Larson
1,4951813
I'm getting an error{ "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }
– Niket Malik
Jan 7 at 13:08
Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.
– Johan Larson
Jan 7 at 13:51
My couchbase version:Community Edition 5.0.1 build 5003, I guess this does not support?
– Niket Malik
Jan 7 at 14:46
You need 5.5 or later for ANSI joins.
– Johan Larson
Jan 7 at 17:17
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%2f54018421%2fsubquery-for-retrieving-count%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 need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:
SELECT l.name, COUNT(1)
FROM demo l JOIN demo r ON r.name = l.child
GROUP BY l.name
To run this query you will need an index on the r side:
CREATE INDEX demo_name_idx ON demo(name)
Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.
answered Jan 3 at 13:09
Johan LarsonJohan Larson
1,4951813
I'm getting an error{ "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }
– Niket Malik
Jan 7 at 13:08
Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.
– Johan Larson
Jan 7 at 13:51
My couchbase version:Community Edition 5.0.1 build 5003, I guess this does not support?
– Niket Malik
Jan 7 at 14:46
You need 5.5 or later for ANSI joins.
– Johan Larson
Jan 7 at 17:17
add a comment |
You need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:
SELECT l.name, COUNT(1)
FROM demo l JOIN demo r ON r.name = l.child
GROUP BY l.name
To run this query you will need an index on the r side:
CREATE INDEX demo_name_idx ON demo(name)
Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.
answered Jan 3 at 13:09
Johan LarsonJohan Larson
1,4951813
I'm getting an error{ "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }
– Niket Malik
Jan 7 at 13:08
Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.
– Johan Larson
Jan 7 at 13:51
My couchbase version:Community Edition 5.0.1 build 5003, I guess this does not support?
– Niket Malik
Jan 7 at 14:46
You need 5.5 or later for ANSI joins.
– Johan Larson
Jan 7 at 17:17
add a comment |
You need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:
SELECT l.name, COUNT(1)
FROM demo l JOIN demo r ON r.name = l.child
GROUP BY l.name
To run this query you will need an index on the r side:
CREATE INDEX demo_name_idx ON demo(name)
Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.
answered Jan 3 at 13:09
Johan LarsonJohan Larson
1,4951813
You need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:
SELECT l.name, COUNT(1)
FROM demo l JOIN demo r ON r.name = l.child
GROUP BY l.name
To run this query you will need an index on the r side:
CREATE INDEX demo_name_idx ON demo(name)
Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.
answered Jan 3 at 13:09
Johan LarsonJohan Larson
1,4951813
edited Jan 7 at 13:50
answered Jan 3 at 13:09
Johan LarsonJohan Larson
1,4951813
answered Jan 3 at 13:09
Johan LarsonJohan Larson
1,4951813
answered Jan 3 at 13:09
Johan LarsonJohan Larson
1,4951813
1,4951813
I'm getting an error{ "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }
– Niket Malik
Jan 7 at 13:08
Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.
– Johan Larson
Jan 7 at 13:51
My couchbase version:Community Edition 5.0.1 build 5003, I guess this does not support?
– Niket Malik
Jan 7 at 14:46
You need 5.5 or later for ANSI joins.
– Johan Larson
Jan 7 at 17:17
add a comment |
I'm getting an error{ "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }
– Niket Malik
Jan 7 at 13:08
Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.
– Johan Larson
Jan 7 at 13:51
My couchbase version:Community Edition 5.0.1 build 5003, I guess this does not support?
– Niket Malik
Jan 7 at 14:46
You need 5.5 or later for ANSI joins.
– Johan Larson
Jan 7 at 17:17
I'm getting an error
{ "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }– Niket Malik
Jan 7 at 13:08
I'm getting an error
{ "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }– Niket Malik
Jan 7 at 13:08
Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.
– Johan Larson
Jan 7 at 13:51
Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.
– Johan Larson
Jan 7 at 13:51
My couchbase version:
Community Edition 5.0.1 build 5003, I guess this does not support?– Niket Malik
Jan 7 at 14:46
My couchbase version:
Community Edition 5.0.1 build 5003, I guess this does not support?– Niket Malik
Jan 7 at 14:46
You need 5.5 or later for ANSI joins.
– Johan Larson
Jan 7 at 17:17
You need 5.5 or later for ANSI joins.
– Johan Larson
Jan 7 at 17:17
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%2f54018421%2fsubquery-for-retrieving-count%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
