SELECT Query that returns a list of objects, that contain Object A + a count of all Objects B that correspond to Object A

n1ql
query

#1

Hey guys,
is it potentially possible to do the following with N1QL?:
(all in the same bucket)

I have 2 document types:
Doc A {
idA: anyID,

}

Doc B {
idB: ID,
idA: anyID,

}

They have a 1:* relation (A to B)
Is it possible to write a N1QL SELECT query that returns a list of objects, that contain A + a count of all B that correspond to A.

Sth. like this:
[{
doc: A {…},
count: 23
},
{
doc: A {…},
count: 45
},
…]

Thx in advance


#2

How do you differentiate Doc A , Doc B is there type field in the documents or prefix in META().id ?
On what key you have 1:* relation . DocB.idA is META().id of DocA?


#4

Hey,
sorry for the ambiguities. Both Docs have a type field.

We have something like this: We have one stage (DocA):

stage-Object = {
id: ‘stageID1’,
type: STAGE
}

And a lot of related seats in this stage (DocB):

seat-Object = {
id: ‘seatID’,
stageId: ‘stageID1’,
type: SEAT
}
where stageId corresponds to the id of the stage-Object.

Hope this clarifies it. If not tell me pls :wink:

Edit: DocB.idA is META().id of DocA? This is not the case.


#5
CREATE INDEX ix_seat ON bucket(stageId) WHERE type = "SEAT";

 SELECT  stage, q.count 
    FROM (SELECT stageId, COUNT(1) AS count  
                           FROM bucket  
                            WHERE type = "SEAT" AND stageId IS NOT NULL 
                            GROUP BY stageId
                 )  AS q 
     JOIN bucket stage ON KEYS q.stageId 
     WHERE stage.type = "SEAT";

If need non present document also

SELECT  stage, q.count, q,stageId 
FROM (SELECT stageId, COUNT(1) AS count  
                       FROM bucket  
                        WHERE type = "SEAT" AND stageId IS NOT NULL
                        GROUP BY stageId
             )  AS q 
 LEFT JOIN bucket stage ON KEYS q.stageId ;

#6

Hey,
thank you very much for the fast response.
The upper one is almost perfect, I don’t really get what the lower one is doing.

My problem in the upper one, there can be stages that don’t have a seat, but have to be shown anyways (with count 0). Is this achievable? Sry that I missed that in the first place.

Great support :relaxed:


#7
CREATE INDEX ix_seat ON default(stageId);
CREATE INDEX ix_stage ON default(id) WHERE type = "STAGE";

SELECT stage, SUM(CASE WHEN seat IS MISSING TEHN 0 ELSE 1 END) AS count
     FROM default stage LEFT JOIN default seat ON KEY seat.stageId FOR stage
     WHERE stage.type = "STAGE" AND stage.id IS NOT NULL
     GROUP BY stage;

#8

This would work perfectly if I had no seats. Problem is in my data model, empty seats are not missing but tagged with a flag empty: true/false. I thought I can just add this condition to the WHERE clause, but actually this doesn’t work.
So I have sth. like this:

SELECT stage, SUM(CASE WHEN seat IS MISSING TEHN 0 ELSE 1 END) AS count
FROM default stage LEFT JOIN default seat ON KEY seat.stageId FOR stage
WHERE stage.type = “STAGE” AND stage.id IS NOT NULL AND seat.type = "EMPTY"
GROUP BY stage;

This makes sense, because I try to join something with a condition that is not fulfilled. The question is, how to always add the stage to the result, even if no join matches? And then set the count to 0


#9

Post #6 uses LEFT JOIN and does this.

post sample document and explain how to use it. example : seat.empty value is true add to count

CREATE INDEX ix_seat ON default(stageId,empty);
CREATE INDEX ix_stage ON default(id) WHERE type = "STAGE";

SELECT stage, SUM(CASE WHEN (seat.empty == true ) TEHN 1 ELSE 0 END) AS count
     FROM default stage LEFT JOIN default seat ON KEY seat.stageId FOR stage
     WHERE stage.type = "STAGE" AND stage.id IS NOT NULL
     GROUP BY stage;

#10

You solved it with the example, that was the solution :heart_eyes:
Thank you very very much, and sorry for the confusion. I thought I simplify by not using my real documents… didn’t work.
The next time I will initially explain and post my full documents.
Sorry for the waste of time.
Great support

Edit: When I have no stages, I would expect the response to be empty, but it is:
[
{
“count”: null
}
]

can I catch this somehow in Couchbase directly or should I do this in the code?


#11

This has been fixed in CB 5.0.0 https://issues.couchbase.com/browse/MB-22482

Work around will be

SELECT q.stage, q.count FROM (
         SELECT stage, SUM(CASE WHEN (seat.empty == true ) TEHN 1 ELSE 0 END) AS count
         FROM default stage LEFT JOIN default seat ON KEY seat.stageId FOR stage
         WHERE stage.type = "STAGE" AND stage.id IS NOT NULL
         GROUP BY stage) AS q
WHERE q.stage IS NOT MISSING;

#12

Wonderful thank you very much


#13

How can we achieve this in couchbase lite?