Couchbase 4.5 return part of document

Question

I'm currently working on a project where we are using couchbase 4.1 as of today for a eCommerce site.

I want to store our websites entire category structure in Couchbase as a single document and then query for a specific category and return that category in some cases and in other cases I would like to return the category and its child categories.

I'm pretty sure I have to use the array indexeer to make this work efficient but I'm quite new to Couchbase so I'm not sure how it should be structured (or even if it's possible).

Part of my document looks like this (there is 4 levels in the structure and about 8-10 top level categories):

{
  "Categories": [
    {
      "DisplayName": "Category One",
      "Id": 1,
      "Categories": [
        {
          "DisplayName": "Child category",
          "Id": 10,
          "Categories": [
            {
              "DisplayName": "Child child category",
              "Id": 100,
              "Categories": [
                {
                  "DisplayName": "Child child child category",
                  "Id": 1000
                },
                {
                  "DisplayName": "Sibling child category",
                  "Id": 1001
                }                
              ]
            },
            {
              "DisplayName": "Child",
              "Id": 101,
              "Categories": [
                {
                  "DisplayName": "Another child category",
                  "Id": 2001
                }
              ]
            }            
          ]
        }
      ]
    }
  ]
}

If I query for Id = 100 I would like to have my result look like this:

{
  "DisplayName": "Child child category",
  "Id": 100,
  "Categories": [
    {
      "DisplayName": "Child child child category",
      "Id": 1000
    },
    {
      "DisplayName": "Sibling child category",
      "Id": 1001
    }                
  ]
}

In some cases I am not interessted having the childs. I have tried to create my query using the array (N1QL) to select into my arrays but I'm not sure whether it's even possible when having levels of complex objects.

Can give me some guidedance on how this is possible (even if it is?). We are using the Couchbase .NET client.

Best regards Martin

Answer 1

This is interesting, because you are trying to store everything inside one document, and then query into that document. Here is one approach.

To query Category Id 100 without sub-categories:

SELECT c.Id, c.DisplayName
FROM default
UNNEST ( ARRAY cat FOR cat WITHIN Categories WHEN cat.Id IS NOT NULL END ) AS c
WHERE c.Id = 100;

To query Category Id 100 with sub-categories:

SELECT c.Id, c.DisplayName, c.Categories
FROM default
UNNEST ( ARRAY cat FOR cat WITHIN Categories WHEN cat.Id IS NOT NULL END ) AS c
WHERE c.Id = 100;

To query Category Id 100 with only one level of sub-categories:

SELECT c.Id, c.DisplayName, sub.Id AS SubId, sub.DisplayName AS SubDisplayName
FROM default
UNNEST ( ARRAY cat FOR cat WITHIN Categories WHEN cat.Id IS NOT NULL END ) AS c
LEFT OUTER UNNEST c.Categories AS sub
WHERE c.Id = 100;