Count query on subdocument

Hi ALl,
Our document structure is like -

{
  "someId": "123",
  "someDetails": {
    "ABC": {
     "submissionId": "somebatch",
      "status": "OPEN"
    },
"DEF": {
     "submissionId": "somebatch",
      "status": "OPEN"
    },
"GHI": {
     "submissionId": "somenewbatch",
      "status": "OPEN"
    }
  },
  "nsid": "detail"
}

I want to get the count of documents for submissionId “somebatch”. How can I do it using N1QL query?

Use Couchbase Object functions convert dynamic fields transform into ARRAY of objects , which will make easier

SELECT  COUNT(1) AS cnt
FROM default  AS d
UNNEST OBJECT_VALUES(d.someDetails) AS ud
WHERE  WHERE ud.summissionId = "somebatch";
1 Like

@vsr1 the query runs ok when we have primary index on the bucket. In our test environment, the policy is not to create primary index but have secondary indexes. What would be the index to get good performance with this query?

CREATE INDEX ix1 ON dfault (ALL ARRAY ud.summissionId FOR ud IN OBJECT_VALUES(someDetails) END);

Variable in index (i.e ud) must match UNNEST alias.

In the count query, I have one more condition in where clause

SELECT COUNT(1) AS cnt
FROM default AS d
UNNEST OBJECT_VALUES(d.someDetails) AS ud
WHERE ud.summissionId = “somebatch” and nsid=“detail”.

I am assuming I have to add that clause in the index too for it to be used.

CREATE INDEX ix1 ON default (ALL ARRAY ud.summissionId FOR ud IN OBJECT_VALUES(someDetails) END)  WHERE nsid="detail";

SELECT COUNT(1) AS cnt
FROM default AS d
UNNEST OBJECT_VALUES(d.someDetails) AS ud
WHERE ud.summissionId = "somebatch" AND  d.nsid="detail";

I created index

CREATE INDEX ix1 ON default (ALL ARRAY ud.summissionId FOR ud IN OBJECT_VALUES(someDetails) END) where nsid=“detail”;

But the query still does not use it. If I create a index on just nsid, it uses it but is very slow.

It should use post the exact query and version of couchbase

Couchbase edition - Enterprise Edition 6.0.1 build 2037

Index - CREATE INDEX idxSubmissionIdNsid ON default(ALL ARRAY dd.submmissionId FOR dd IN OBJECT_VALUES(someDetails) END) where nsid=‘detail’ ;

Query - SELECT count(1) as count FROM default c UNNEST OBJECT_VALUES(c.someDetails) AS dd WHERE c.nsid=‘detail’ and dd.submissionId = ‘somebatch’

Index has mm and predicate has single m correct it and try again.
dd.submmissionId
dd.submissionId

1 Like

Awesome. Thanks for pointing out the typo. The query is using the index now.