Index on multiple array expression

Couchbase Server
1

Hi All,

Can you please provide the right index definition for the below query:

SELECT count(*) as count FROM test_a as bckt UNNEST bckt.definingAttributes.attributes AS attribute UNNEST bckt.items AS items WHERE bckt.doc_type = ‘VARIATION_GROUP’ AND attribute.attributeType = ‘Swatch’ AND items.genericAttribute IS MISSING

2

@pruthvi ,

There is no relation between first and second UNNEST. Could you explain what exactly you are achieving so that better query can be suggested.

Based on above query. if attribute type is “Swatch”, count number of genericattribute missing in items array across all documents. Check it should have been covered.

CREATE INDEX ix1  ON test_a (DISTINCT ARRAY attribute.attributeType FOR attribute IN definingAttributes.attributes END,
                             ARRAY_SUM(ARRAY 1 FOR item IN bckt.items WHEN item.genericAttribute IS MISSING END))
WHERE doc_type = "VARIATION_GROUP";
SELECT SUM(ARRAY_SUM(ARRAY 1 FOR item IN bckt.items WHEN item.genericAttribute IS MISSING END)) AS count
FROM test_a AS bckt
WHERE bckt.doc_type = "VARIATION_GROUP"
      AND ANY attribute IN bckt.definingAttributes.attributes SATISFIES attribute.attributeType = "Swatch" END;