Query to get count on sub document

SQL++
1

Hi All, our document structure is -
1st document
{ "someId": "123", "someDetails": { "ABC": { "submissionId": "somebatch", "status": "OPEN" }, "DEF": { "submissionId": "somebatch", "status": "OPEN" }, "GHI": { "submissionId": "somenewbatch", "status": "OPEN" } }, "nsid": "detail" }
2nd document -

{ "someId": "456", "someDetails": { "ABC": { "submissionId": "somebatch1", "status": "OPEN" }, "DEF": { "submissionId": "somebatch1", "status": "OPEN" } }, "nsid": "detail" }
How can I get a query and index which can return something like -

Name - Count
ABC - 2
DEF - 2
GHI - 1

Any help will be appreciated.

2 
CREATE INDEX ix1 ON default (ALL OBJECT_NAMES(someDetails)) WHERE nsid = "detail";
SELECT dname, COUNT(1) AS cnt
FROM default AS d
UNNEST OBJECT_NAMES(d.someDetails) AS dname
WHERE d.nsid = "detail"
GROUP BY dname;
1 Like
3

@vsr1 Our new requirement is now to get the count by status “OPEN” and “COMPLETED”. To achieve that I have to also get OBJECT_VALUE? Can you please help with the same and also the index for it.

Name -Open Count - Completed Count
ABC - 2 - 0
DEF - 2 - 0
GHI - 1 - 0

4 
SELECT dp.name,
                SUM( CASE WHEN dp.val.status = "OPEN" TEHN 1 ELSE 0 END) AS open,
                SUM( CASE WHEN dp.val.status = "COMPLETED" TEHN 1 ELSE 0 END) AS completed,
FROM default AS d 
UNNEST OBJECT_PAIRS(d.someDetails) AS dp
WHERE d.nsid = "detail"  
GROUP BY dp.name ;

Index will based on your predicates.

5

@vsr1 when I run this query in query manager, it works fine but when I use the same query in code, it doesn’t work. I copied the query multiple times from code and ran it in query manager and it works fine but when the code is executed it retuns 0 rows.

JsonObject pVal = JsonObject.create().put(“bucketName”, bucketName)
.put(“nsid”, NSID_DETAIL);

    N1qlQueryResult result = couchbaseBucket.query(N1qlQuery.parameterized(COUNT_QUERY, pVal));
6

What is query. i am not expert in SDK, May be post in JavaSDK topic

7

private static final String COUNT_QUERY = “SELECT dp.name, dp.val.status, COUNT(1) AS cnt” +
" FROM $bucketName AS d " +
" UNNEST OBJECT_PAIRS(d.someDetails) AS dp "+
" WHERE d.nsid = $nsid " +
" GROUP BY dp.name, dp.val.status ";

8

You can’t parameterize bucketname. Follow this https://www.programcreek.com/java-api-examples/?class=com.couchbase.client.java.query.N1qlQuery&method=parameterized

FYI: updated query reflect your desired output