Best practice for 1 to N problem

Hi there,

Just get started to use Couchbase, and I enjoy the “easy to setup” and “sql-like syntax” feature a lot. And I am currently having some general questions about 1 to N relations. Hopefully someone will help to explain.
Let say that we want to create a question to answers relation that one question will have multiple answers. And assume we have the basic schema like this.
question: {
id : “question_1”,
title: “some question”,
type: “question”
answer: {
id: "answer_1"
content: "do this."
questionId: “question_1”,
type: “answer”
I wonder what will be the best practice here that 1. should I create a answer field in question object that contains an array of answer ids and maintain the array whenever I insert a new answer? 2. Or should I just use the question id in answer to unnest into questions?

If it is options 1 I wonder what would be the best syntax to insert a new answer with uuid and update the question answers field within a single request? and if it the later option, what will be the statement to unnest answers into a “answers” field on question object? (I am getting “UNNEST term must have a name or alias” error)


you can use JOIN

SELECT q.title,
       ARRAY_AGG({"id":META(a).id,"content":a.content}) AS answer_list
FROM default a
 JOIN default q ON KEYS a.questionId
WHERE a.type == "answer"
GROUP BY q.title

to get questions with answer list.

And for options 1, you can update your document by

UPDATE default qa SET qa.answer_list = ARRAY_APPEND(qa.answer_list,{"type":"answer","content":"do it again."}) WHERE META(qa).id == "question_1";

Thanks, dude! That works

Another quick question though, I wonder how can we check if an array contains all of the values in another array, for example if I have question schema like : { type : “question”, tags : [“abc”, “def”, “xyz”] } is that possible I could check if question.tags contains [“abc”, “xyz”]?

Try this.

EVERY x IN array1 SATISFIES x IN array2 END

1 Like

Thanks for the answer, that helps a lot, not sure if I should ask so many questions in one thread, but I just wonder if there is any way I could combine two statements into one “commit”, just to use the example above, if I try to insert an answer into its own document and append its id into the question answers field (which is an array of answer ids), do I have to send out two separate request.
like this?
var answer = {id : “answer-1”, content: “answer”}
bucket.insert(“answer-1”, answer, function(){
bucket.mutateIn(‘questoin’).upsert(‘answers’, [], true).

Since I was thinking from a transnational point of view that if the one of the request failed none of them should take effect.

Please start a new question for this one.