Couchbase - get all document prefixes

user_5537652 0
Viewed 214

We have a fairly large couchbase bucket that holds several document types, denoted by document prefixes. For example, we have some documents prefixed with device---[document name], some with market---[document name]). I want a list of all the existing prefixes. This is what I have tried so far:

select split(meta().id, "---")[0] as doc_type
from configs
group by doc_type;

I would hope for it to return something like

[
  {
    "doc_type": "device"
  },
  {
    "doc_type": "market"
  },
]

It fails with

"code": 4210, 
"msg": "Expression must be a group key or aggregate: (split((meta(`docsis_configs`).`id`), \"---\")[0])",

I'm not sure how to solve the aggregation problem and have tried various things. However I suspect that even if I got that to work, this query would be very inefficient. Can someone help me with the aggregation? And is there a smarter way to do this? Or is this fine?

n1ql couchbase
2 Answers

You can also use the Eventing Service below is some simple code that will create a counter document for each unique prefix. This will update a counter for every document you have.

Note dst_bkt is a bucket binding to a different bucket in r+w mode this bucket should start out empty it will get a doc per prefix with the counts.

This will also work without and indexes.

function OnUpdate(doc, meta) {
    var pfx =  meta.id.split("---");
    if (!pfx[1]) return; // bad key so return and skip
    couchbase.increment(dst_bkt,{"id": pfx[0] });
}

Note, there is a non standard hack to improve the performance drastically if you only want to run this as a one off tool (no counts) if interested please DM me.

user_124530320

Only projection alias can be referenced in ORDER BY.

select doc_type
from configs
LET doc_type = split(meta().id, "---")[0]
group by doc_type;

OR

select doc_type
from configs
group by split(meta().id, "---")[0] AS doc_type;

There is no other way to achieve this with N1QL.

If you want you can use couchbase map reduce views (https://docs.couchbase.com/server/current/learn/views/views-intro.html)

Also eventing service might be option.

user_60805360
Related