COUNT Condition: Data Format Discussion

SQL++
1

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
        }
    ]}
}
1 Like