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

geraldss · Answer 1 · 2016-04-06T01:36:29.920

2

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;

edited Apr 06 '16 at 01:36

answered Apr 01 '16 at 17:15

geraldss

2,415
1
11
12

It looks really good! I will try it out as first thing monday morning! Will it be possible to create an index as well to make the search faster? Could not find anything in the documentation and a search didn't give any real answer. – Martin Apr 02 '16 at 21:29
1

I tried creating an index for this, but it's not possible with 4.5. The issue is that all your data is in a single document. Typically, an index maps from values to documents, so you can quickly reach the right document. In your case, you need an index to reach into a part of the document, and help you avoid processing the whole document. Not really possible with 4.5. – geraldss Apr 02 '16 at 22:04
Another option is to use a map-reduce view. The map-reduce view would emit and store each combination of (Id, DisplayName, Categories), and you can query the view directly for a given Id. You can see the Couchbase docs on map-reduce views and writing an emit() function in JavaScript. – geraldss Apr 04 '16 at 01:25
When using your query is it possible to restrict the query to only fetch the first level of categories or down to second level? It works fine as it is right now but it is more about reducing the result size a bit :) – Martin Apr 04 '16 at 08:12
Added to the answer. – geraldss Apr 06 '16 at 01:37

score 1 · Answer 2 · answered Apr 01 '16 at 07:43

1

The only thing i can find is subdoc (first available in Couchbase 4.5): http://blog.couchbase.com/2016/february/subdoc-explained

answered Apr 01 '16 at 07:43

drphrozen

317
3
4

1

subdoc *could* be a great fit but it's not for querying purposes: you **have to know the path(s) you want to retrieve**. However, if the document structure can be changed so that the root object is a dictionnary, 1 entry per category, the attribute name being the category ID, then this could work. – Simon Baslé Apr 01 '16 at 08:14

Couchbase 4.5 return part of document

2 Answers2