Best way to query / search deeply nested data

SQL++
1

I’m fairly new to Couchbase and N1QL, etc. and have inherited a project to work on. It is a non-normalized database with document types that contain questionnaires, answers, people, etc.

We would like to run reports on the answers given for questionnaires. Here is a sample document:

{
  "id": "1234",
  "name": "Simple Questionnaire",
  "pages": [
    {
      "name": "About You",
      "questions": [
        {
          "id": "1",
          "question": "What is your name"
          "answer": "Glen"
        },
        {
          "id": "2",
          "question": "How old are you"
          "answer": "14"
        }
      ]
    },
    {
      "name": "Your family",
      "questions": [
        {
          "id": "3",
          "question": "How many siblings do you have"
          "answer": "3"
        },
        {
          "id": "4",
          "question": "Are you the youngest"
          "answer": "no"
        }
      ]
    },
    {
      "name": "School",
      "questions": [
        {
          "id": "5",
          "question": "What is your favourite subject"
          "answer": "Computer Science"
        },
        {
          "id": "6",
          "question": "Do you like tests"
          "answer": "no"
        }
      ]
    }
  ],
  "school": {
    "id": 12,
    "name": "A secondary school"
  },
  "type": "answers"
}

We want to query / search based on the answers given to certain questions. As these are nested 2 levels deep inside the pages array, I’m not sure of the best way to do this.

We would want to have a list of questions for the questionnaire in the filtering tool, then if someone chooses a question to filter on, we would then want to see all the possible answers for that question.

An example would be filtering by the question “How many siblings do you have” and seeing the values, [1, 3, 4, 5] in the possible answers, then choosing ‘3’ to see the above document id / summary included in our list of results.

The next step would be to allow multiple conditions for the filtering, e.g. on more than one question.

I’ve been looking at N1QL for querying fixed questions without much success and also looking at views for doing this, but I feel that would not be easy to do with just a view and key searching.

Please can someone advise on this? I’ve a horrible feeling that the answer may be normalisation of the answers to each questionnaire, but there may be other ways.

Thanks in advance.

Glen

2

Hi @glenswoop,

Try the following index and query on Couchbase 4.5.1.

CREATE INDEX idx_q ON default( DISTINCT ARRAY ( DISTINCT ARRAY q.question FOR q IN p.questions END ) FOR p IN pages END );

EXPLAIN
SELECT DISTINCT q.answer
FROM default AS d
UNNEST d.pages AS p
UNNEST p.questions AS q
WHERE q.question = "How many siblings do you have";
1 Like
3

Great, thanks very much for that! That’s working nicely and I’ve managed to extrapolate into similar queries.

I don’t suppose you have any advice on the performance aspects of N1QL with Indexes vs Views vs FTS for something like this where we may have complex “queries” based on multiple conditions, e.g. question1=“Foo” AND answer1=“Bar” AND question2=“Baz” AND answer2=“quux”
?

Thanks

Glen

4

I think N1QL is your best bet for this, but maybe I’m biased :slight_smile:

FTS is not yet GA, but you can try out the developer preview.

With views, you would be doing more programmatically, e.g. if you have complex combinations of ANDs and ORs.

For N1QL, the next developer build will have some nice improvements for your use case.