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?
vsr1
June 12, 2019, 8:51pm
2
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?
vsr1
July 8, 2019, 7:35pm
4
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.
vsr1
July 8, 2019, 7:56pm
6
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.
vsr1
July 8, 2019, 8:29pm
8
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’
vsr1
July 8, 2019, 8:40pm
10
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.