Query:
cbq> SELECT {SUBSTR(t, 0, 10) : {
> did : {
> "foull_entries" : COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
> "total_entries" : COUNT(*),
> "nulld_entries" : COUNT(CASE WHEN ARRAY_LENGTH(bcn)==0 THEN 1 ELSE NULL END)
> }
> }}
> FROM sync_gateway
> WHERE type="bcn_scan" AND SUBSTR(t, 0, 10) BETWEEN "2016-11-01" AND "2016-12-15"
> GROUP BY SUBSTR(t, 0, 10), did
> ORDER BY SUBSTR(t, 0, 10) ASC, did ASC;
Result:
{
"2016-12-14": {
"5fe03c918f85052bcd4b4094f1fae051": {
"foull_entries": 0,
"nulld_entries": 426,
"total_entries": 426
}
}
},
{
"2016-12-14": {
"8dadc3de40bda851abdb4982eebb21fc": {
"foull_entries": 320,
"nulld_entries": 0,
"total_entries": 320
}
}
},
{
"2016-12-14": {
"8f2d11c831dc6eaa1144c9db5409437e": {
"foull_entries": 0,
"nulld_entries": 2880,
"total_entries": 2880
}
}
},
{
"2016-12-14": {
"abc7ff93c3f3c4bcde0baa28f2fa688c": {
"foull_entries": 1425,
"nulld_entries": 216,
"total_entries": 1641
}
}
},
{
"2016-12-14": {
"c6a995f8d8f360d21dcc2c5f5f2a7fd4": {
"foull_entries": 0,
"nulld_entries": 1321,
"total_entries": 1321
}
}
},
{
"2016-12-15": {
"5fe03c918f85052bcd4b4094f1fae051": {
"foull_entries": 0,
"nulld_entries": 673,
"total_entries": 673
}
}
},
{
"2016-12-15": {
"8dadc3de40bda851abdb4982eebb21fc": {
"foull_entries": 247,
"nulld_entries": 0,
"total_entries": 247
}
}
},
{
"2016-12-15": {
"8f2d11c831dc6eaa1144c9db5409437e": {
"foull_entries": 0,
"nulld_entries": 1485,
"total_entries": 1485
}
}
},
{
"2016-12-15": {
"abc7ff93c3f3c4bcde0baa28f2fa688c": {
"foull_entries": 924,
"nulld_entries": 0,
"total_entries": 924
}
}
}
Desired:
{
"2016-12-14": {[
"5fe03c918f85052bcd4b4094f1fae051": {
"foull_entries": 0,
"nulld_entries": 426,
"total_entries": 426
},
"8dadc3de40bda851abdb4982eebb21fc": {
"foull_entries": 320,
"nulld_entries": 0,
"total_entries": 320
},
"8f2d11c831dc6eaa1144c9db5409437e": {
"foull_entries": 0,
"nulld_entries": 2880,
"total_entries": 2880
},
"abc7ff93c3f3c4bcde0baa28f2fa688c": {
"foull_entries": 1425,
"nulld_entries": 216,
"total_entries": 1641
},"c6a995f8d8f360d21dcc2c5f5f2a7fd4": {
"foull_entries": 0,
"nulld_entries": 1321,
"total_entries": 1321
}
]}
},
{
"2016-12-15": {[
"5fe03c918f85052bcd4b4094f1fae051": {
"foull_entries": 0,
"nulld_entries": 673,
"total_entries": 673
},
"8dadc3de40bda851abdb4982eebb21fc": {
"foull_entries": 247,
"nulld_entries": 0,
"total_entries": 247
},
"8f2d11c831dc6eaa1144c9db5409437e": {
"foull_entries": 0,
"nulld_entries": 1485,
"total_entries": 1485
},
"abc7ff93c3f3c4bcde0baa28f2fa688c": {
"foull_entries": 924,
"nulld_entries": 0,
"total_entries": 924
}
]}
}