Indexing multiple properties in an array of objects

SQL++
1

We need to get multiple fields out of an array of objects, but I can’t just index the whole object because some of the fields are very large and cause errors:

Error: Encoded secondary key is too long

EDIT:
So I think I figured out the way to index multiple fields, but the query isn’t being covered by it

CREATE INDEX `ArrayTest` 
ON `zd01`
( tenantKey,
  type,
  pcr.pcrKey,
  ALL ARRAY 
  { 
	p.procedurePerformed,
	p.performingCrewMember
  }
  FOR p in pcr.treatments.procedures END
) 
WHERE (`type` = 'patientCareRecord')

The query:

SELECT meta(doc).id AS documentId,
  ARRAY 
  { 
	p.procedurePerformed,
	p.performingCrewMember
  }
  FOR p in pcr.treatments.procedures END AS procedures,
  doc.tenantKey AS tenantKey 
FROM `zd01` doc
USE INDEX(ArrayTest)
WHERE doc.tenantKey = 'a100fb4b-57e7-e711-80f4-005056a8748b'        
AND doc.type = 'patientCareRecord'
AND doc.pcr.treatments.procedures IS VALUED
2 Likes
2

Are you trying to make covering index? How big is array?

In create Index statement if use ALL or DISTINCT keyword in front of ARRAY it creates array index key (i.e UNNEST array creates separate key) and that can be used in conjunction with ANY … predicate as part of WHERE clause. As you are using in projection wants as whole array by removing ALL keyword it creates ARRAY as scalar value as index key.

Try this.

CREATE INDEX ix20 ON default ( tenantKey, ARRAY {p.procedurePerformed, p.performingCrewMember} FOR p in pcr.treatments.procedures END) WHERE (`type` = 'patientCareRecord' AND pcr.treatments.procedures IS VALUED);

SELECT meta(doc).id AS documentId,
  ARRAY
  {
    p.procedurePerformed,
    p.performingCrewMember
  }
  FOR p in pcr.treatments.procedures END AS procedures,
  doc.tenantKey AS tenantKey
FROM default doc
WHERE doc.tenantKey = 'a100fb4b-57e7-e711-80f4-005056a8748b'
AND doc.type = 'patientCareRecord'
AND doc.pcr.treatments.procedures IS VALUED;

Please checkout https://blog.couchbase.com/n1ql-practical-guide-second-edition/

3

Also, in Couchbase 5.0, the max size of the secondary key has been increased (essentially, no error up to 20 MB). Try that. Be aware, larger index sizes does require additional memory/resources for indexer, especially the memory optimized index.