How do I do an aggregate query against a Couchbase array index?

Question

I have documents in my database that contain a "flags" array. Each of those has a "flag" value that contains a string. I'm trying to get the count of how many of each flag string there are across all documents. So for example, if I had two documents:

{
  "flags": [
    {
      "flag": "flag1",
      ...
    },
    {
      "flag": "flag2",
      ...
    }
  ],
  ...
},
{
  "flags": [
    {
      "flag": "flag1",
      ...
    },
    {
      "flag": "flag3",
      ...
    }
  ],
  ...
}

I would expect a result back like:

{
  {
    "flag": "flag1",
    "flag_count": 2
  },
  {
    "flag": "flag2",
    "flag_count": 1
  },
  {
    "flag": "flag3",
    "flag_count": 1
  }
}

I've created an index that looks like this:

CREATE INDEX `indexname` ON `dbname`((all (array (`f`.`flag`) for `f` in `flags` end)),`flags`) WHERE (`type` in ["type1", "type2"])

So far, the only way I've been able to get this to work is with a query like this:

SELECT f1.flag, count(*) as flag_count from dbname s unnest flags as f1 where (s.type in ["type1", "type2"]) AND any f in s.flags satisfies f.flag like '%' end group by f1.flag

This all makes sense to me except that it requires something along the lines of that AND any f in s.flags satisfies f.flag like '%' part to run at all - if I leave that out, it tells me it can't find an index that works. Is there a way to structure this such that I could leave that out? It seems unnecessary to me, but I guess I'm missing something.

score 1 · Accepted Answer · answered Jun 21 '21 at 21:46

CREATE INDEX ix1 ON dbname( ALL ARRAY f.flag FOR f IN flags END)
WHERE type IN ["type1", "type2"];

SELECT f.flag, COUNT(1) AS flag_count
FROM dbname AS d
UNNEST d.flags AS f
WHERE d.type IN ["type1", "type2"] AND f.flag LIKE "%"
GROUP BY f.flag;

If ARRAY has duplicate flag value and count one per document

SELECT f.flag, COUNT( DISTINCT META(d).id) AS flag_count
FROM dbname AS d
UNNEST d.flags AS f
WHERE d.type IN ["type1", "type2"] AND f.flag LIKE "%"
GROUP BY f.flag;

Check UNNEST https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

How do I do an aggregate query against a Couchbase array index?

1 Answers1