Array JOIN same bucket count

SQL++
1

Hello there,
probably a couchbase newbie questions, but…
I have the following 2 doc types:

  1. Question

doc key: “question:100”
{
“type”: question",
“quesstion_id”: “100”,
“question_title”: “title1”
}

doc key: “question:200”
{
“type”: “question”,
“quesstion_id”: “200”,
“question_title”: “title2”
}

  1. Questionnaire - A collection of “questions” objects. I have many of these…

doc key : “questionnaire:161:169”
{
“type”: “questionnaire”
“category”: “161:169”,
“questions” : [
{ “question_id” : “question:100”, “display_order” : 1},
{ “question_id” : “question:200”, “display_order” : 2},
{ “question_id” : “question:300”, “display_order” : 3}
]
}

I want to have 1 query that based on the question doc keys that i have in my hand will return me the following:

  • A list of “question” objects
  • For each,
    – The actual question objet
    – The count of this questionnaires that contain this question id.

I cant wrap up my mind around it…
Should i use JOIN, UNNEST, SUBQUERIES…? No matter what i tried i didn’t get it correctly.

Thanks

2

Use 5.50 with ANSI JOIN. Checkout Example 12

CREATE INDEX ix1 ON default(DISTINCT ARRAY v.question_id FOR v IN questions END) 
WHERE type = "questionnaire";

SELECT   d,  COUNT(META(q).id) AS count
FROM default AS d USE KEYS ["question:100","question:100"]
LEFT JOIN default AS q
ON ANY v IN q.questions SATISFIES v.question_id  = META(d).id END AND q.type = "questionnaire"
GROUP BY d;
3

Thanks… I do have 4.5 installed not 5.5 ;(

I managed to construct the below one, but still not perfect…
It returns the data in case there is a questionnaire document but if there isn’t one, none is being returned…

SELECT q.question_id, count(q.question_id) cnt FROM default x
UNNEST questions AS q
WHERE x.type = ‘questionnaire’ AND
q.question_id IN [‘question:10013’, …]
GROUP BY q.question_id) as questionnaire
JOIN default question ON KEYS questionnaire.question_id

Did also tried LEFT JOIN but no luck

4 
CREATE INDEX ix1 ON default(ALL ARRAY qid.question_id FOR qid IN questions END)
WHERE type = "questionnaire";

SELECT   d,  FIRST v.cnt FOR v IN questionnaire WHEN v.qid = META(d).id END cnt
FROM default AS d USE KEYS ["question:100","question:100"]
LET questionnaire = ( SELECT qid.question_id, COUNT (DISTINCT META(q).id) AS cnt
                      FROM default AS q
                      UNNEST q.questions AS qid
                      WHERE qid.question_id IN ["question:100","question:100"] END AND q.type = "questionnaire"
                      GROUP BY qid.question_id)
;
5

Thanks but i got this err…

Error evaluating LET. - cause: FROM in correlated subquery must have USE KEYS clause

6

previous query updated and retry it

7

Thanks!
The only thing i had to change for make this work is WHEN v.qid.question_id = …
instead of WHEN v.qid =

8

Re-updated the query