Use "Where" clause inside array indexing

SQL++
1

I have documents that looks like this:
> {

	...
	"creationDate": 1492004397241,
	"creator": "10cdf7c35b29a29526efd3a345f9aa76",
	"elementValues": [
		{
			"elementValueTypeId": "EVT_DATE",
			"value": "26-03-2017 - 20:42"
		},
		{
			"booleanValue": false,
			"elementValueTypeId": "EVT_IS_ACNC",
			"value": "old case"
		},
		{
			"elementValueTypeId": "EVT_CLINICAL_SIGNS",
			"value": "flue",
			"booleanValue": true
		}
	],
	"type": "EVENT",
	"visitId": "aec16650-9ec7-4244-9108-e1911a3e4497"
	...
}

And I have a query that looks something like this:

SELECT COUNT(*) as count FROM DATA_BUCKET event
UNNEST event.elementValues elementValue
JOIN DATA_BUCKET patient ON KEYS event.patientId
WHERE 
		event.`type` = "EVENT"
	AND elementValue.elementValueTypeId = "EVT_IS_ACNC"
	AND elementValue.booleanValue = true
	AND patient.origin = "AS"
 ...

I manage to create an index for the elementValueTypeId attribute:

CREATE INDEX idx_elementValue_type_array ON DATA_BUCKET((DISTINCT (ARRAY (elementValue.elementValueTypeId) FOR elementValue IN elementValues END)))

And another one for booleanValue:

CREATE INDEX idx_booleanValue ON DATA_BUCKET((DISTINCT (ARRAY (elementValue.booleanValue) FOR elementValue IN elementValues END)))

But I would like to combine both of them to have an index on elementValueTypeId that has the booleanValue to true. How can I achieve this?

I tried

CREATE INDEX idx_elementValue_type_array_boolean_true ON DATA_BUCKET((DISTINCT (ARRAY (elementValue.elementValueTypeId FOR elementValue IN elementValues WHERE elementValue.booleanValue = true END)))

But it gives me a

syntax error - at WHERE

Is their anyway I create those kind of indexes?

2 
CREATE INDEX ix1 ON DATA_BUCKET(DISTINCT ARRAY elementValue.elementValueTypeId FOR elementValue IN elementValues WHEN elementValue.booleanValue = true END) WHERE type = "EVENT";

SELECT COUNT(1) as count FROM DATA_BUCKET event JOIN DATA_BUCKET patient ON KEYS event.patientId
WHERE event.`type` = "EVENT" AND patient.origin = "AS" AND
    ANY elementValue IN event.elementValues SATISFIES elementValue.elementValueTypeId = "EVT_IS_ACNC" AND elementValue.booleanValue = true END;

Verify query using above index through EXPLAIN.

NOTE : If eventValues array is small you can use following index. Which use covering index for event. If array is too big indexer may skip the document while indexing.

CREATE INDEX ix1 ON DATA_BUCKET(DISTINCT ARRAY elementValue.elementValueTypeId FOR elementValue IN elementValues WHEN elementValue.booleanValue = true END,patientId,eventValues) WHERE type = "EVENT";

1 Like
3

What do you consider has a “small” array? What is the limit according to you?

But thanks for the answer, it works !

4

See Limitations at https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/createindex.html

cc, @deepkaran.salooja