COUNT condition

[sntentos]

Tried 3 approaches.

1. Since NULLIF has no counterpart expression, I tried to negate it:

   cbq> SELECT did, date, COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)) AS zero_entries, COUNT(NOT NULLIF(ARRAY_LENGTH(bcn), 0)) AS entries
   FROM sync_gateway
   > > LET date = SUBSTR(t, 0, 10)
   > WHERE type=‘bcn_scan’ AND date BETWEEN ‘2016-11-01’ AND ‘2016-12-15’
   GROUP BY did, date ORDER BY did ASC, date ASC; >
   {
   “requestID”: “38b60186-d5c8-440a-b148-f06876724ae5”,
   “signature”: {
   “date”: “json”,
   “did”: “json”,
   “entries”: “number”,
   “zero_entries”: “number”
   },
   “results”: [
   {
   “date”: “2016-11-15”,
   “did”: “1a939b4b17ea46af16592a5be2bb7ab6”,
   “entries”: 1523,
   “zero_entries”: 1523
   },
   {
   “date”: “2016-11-16”,
   “did”: “1a939b4b17ea46af16592a5be2bb7ab6”,
   “entries”: 367,
   “zero_entries”: 367
   },
   […]

Doesn’t look right

2. Then I tried the not zero value

   cbq> SELECT did, date, COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)) AS zero_entries, COUNT(NULLIF(ARRAY_LENGTH(bcn), 1)) AS entries
   FROM sync_gateway
   > > LET date = SUBSTR(t, 0, 10)
   > WHERE type=‘bcn_scan’ AND date BETWEEN ‘2016-11-01’ AND ‘2016-12-15’
   > GROUP BY did, date ORDER BY did ASC, date ASC;
   {
   “requestID”: “a0df6abe-ff68-4f27-bb67-ba6ff25139d8”,
   “signature”: {
   “date”: “json”,
   “did”: “json”,
   “entries”: “number”,
   “zero_entries”: “number”
   },
   “results”: [
   {
   “date”: “2016-11-15”,
   “did”: “1a939b4b17ea46af16592a5be2bb7ab6”,
   “entries”: 1583,
   “zero_entries”: 1523
   },
   {
   “date”: “2016-11-16”,
   “did”: “1a939b4b17ea46af16592a5be2bb7ab6”,
   “entries”: 1798,
   “zero_entries”: 367
   },
   […]

Could be, but I cannot parse 1,5M entries to verify

3. Then tried to mix the expressions:

   cbq> SELECT did, date, COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)) AS zero_entries, COUNT(ARRAY_LENGTH(bcn)<>0) AS entries
   FROM sync_gateway
   LET date = SUBSTR(t, 0, 10)
   > > > WHERE type=‘bcn_scan’ AND date BETWEEN ‘2016-11-01’ AND ‘2016-12-15’
   > GROUP BY did, date ORDER BY did ASC, date ASC;
   {
   “requestID”: “00f7b619-f1e0-455c-8e61-04ff264faf42”,
   “signature”: {
   “date”: “json”,
   “did”: “json”,
   “entries”: “number”,
   “zero_entries”: “number”
   },
   “results”: [
   {
   “date”: “2016-11-15”,
   “did”: “1a939b4b17ea46af16592a5be2bb7ab6”,
   “entries”: 1583,
   “zero_entries”: 1523
   },
   {
   “date”: “2016-11-16”,
   “did”: “1a939b4b17ea46af16592a5be2bb7ab6”,
   “entries”: 1803,
   “zero_entries”: 367
   },
   […]

They look alike (2)

---

I also tried to look at one day (Still the file is 5 MB, so search regex to the rescue!)

SELECT t, bcn, IS_ARRAY(bcn) AS bcn_test,
	COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)) AS `NULLIF0`,
	COUNT(NULLIF(ARRAY_LENGTH(bcn), 1)) AS `NULLIF1`,
	COUNT(ARRAY_LENGTH(bcn)<>0) AS diffThanZero,
	COUNT(ARRAY_LENGTH(bcn)==0) AS eqZero
FROM sync_gateway LET date = SUBSTR(t, 0, 10) WHERE type="bcn_scan" AND date="2016-11-15" AND did="1a939b4b17ea46af16592a5be2bb7ab6" GROUP BY t, bcn ORDER BY t, bcn;


 Connected to : http://localhost:8091/. Type Ctrl-D or \QUIT to exit.

 Path to history file for the shell : /root/.cbq_history 
{
	"requestID": "e15c844b-e720-4aa1-9cdc-b82c7a13f11f",
	"signature": {
		"NULLIF0": "number",
		"NULLIF1": "number",
		"bcn": "json",
		"bcn_test": "boolean",
		"diffThanZero": "number",
		"eqZero": "number",
		"t": "json"
	},
	"results": [
		{
			"NULLIF0": 1,
			"NULLIF1": 1,
			"bcn": [
				{
					"d": 7.590711939039835,
					"id": [
						"0x5475726b75204f626f416b6164656d69",
						"0x0117",
						"0x0001"
					],
					"rssi": -87,
					"tx": -58
				},
				[...]
			],
			"bcn_test": true,
			"diffThanZero": 1,
			"eqZero": 1,
			"t": "2016-11-15T07:27:50.241Z"
		},
		[...],
		{
			"NULLIF0": 0,
			"NULLIF1": 1,
			"bcn": [],
			"bcn_test": true,
			"diffThanZero": 1,
			"eqZero": 1,
			"t": "2016-11-15T11:57:49.997Z"
		}
		[...],

(Perl) Regex Scan returns 60 results for ("(NULLIF0|NULLIF1|diffThanZero|eqZero)": 0|"bcn_test": false) and only for the "NULLIF0": 0 term.

I also ran:

Connected to : http://localhost:8091/. Type Ctrl-D or \QUIT to exit.

 Path to history file for the shell : /root/.cbq_history 
SELECT did, date,
	COUNT(*) AS star,
	COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)) AS `NULLIF0`,
	COUNT(NOT NULLIF(ARRAY_LENGTH(bcn), 0)) AS `NOTNULLIF0`,
	COUNT(NULLIF(ARRAY_LENGTH(bcn), 1)) AS `NULLIF1`,
	COUNT(ARRAY_LENGTH(bcn)<>0) AS difZero,
	COUNT(ARRAY_LENGTH(bcn)==0) AS eqZero
FROM sync_gateway
LET date = SUBSTR(t, 0, 10)
WHERE type="bcn_scan" AND date BETWEEN "2016-11-01" AND "2016-12-15"
GROUP BY did, date
ORDER BY did ASC;


 Connected to : http://localhost:8091/. Type Ctrl-D or \QUIT to exit.

 Path to history file for the shell : /root/.cbq_history 
{
	"requestID": "609cd125-0470-42c5-8620-bec95a996448",
	"signature": {
		"NOTNULLIF0": "number",
		"NULLIF0": "number",
		"NULLIF1": "number",
		"date": "json",
		"did": "json",
		"difZero": "number",
		"eqZero": "number",
		"star": "number"
	},
	"results": [
		[...],
		{
		    "NOTNULLIF0": 1523,
		    "NULLIF0": 1523,
		    "NULLIF1": 1583,
		    "date": "2016-11-15",
		    "did": "1a939b4b17ea46af16592a5be2bb7ab6",
		    "difZero": 1583,
		    "eqZero": 1583,
		    "star": 1583
		},
		[...]

I assume that 1583 - 1523 = 60 indeed equals 60 (what the scan showed us above), so I guess I have to subtract COUNT (*) - COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)) to get my number.

I also “derived” a different way on computing (again on post #6), which also produces same results.

I don’t know if this helps your facilitate your answer functionally/language-related.

---

Also, I’d warmly welcome your input on the #6 post (how to ‘merge’ the documents)

@sntentos

I’d like to “merge” did value (d46cb7bd3c6001e00ba3fd43eca39428) with the attribute name data, ie

"d46cb7bd3c6001e00ba3fd43eca39428": {
	"NEW: Vaasa 1": [
		{
			"empty": 1,
			"foull": 1
		}
	]
}

---

Sidenote: Moving some (index) speedup improvements in this topic

COUNT condition: INDEX Speed-up improvements

Sidenote: Please read this topic for background on this question (specifically #8 response) https://www.couchbase.com/forums/t/count-condition/11806 Why doesn’t my index kick-in (in the first 3 queries) to give me blazing-fast results? I guess that array operations / filters are hard but the main keys are already there cbq> select * from system:indexes; { "requestID": "cf3b9ed6-18e0-475a-92f4-0cf3489dc8a2", "signature": { "*": "*" }, "results": [ { …