Indexing an array of objects within a document

SQL++
, ,
1

Have the following document as an example:

{
    "id": "05a9b954-bdee-4d7f-9715-8e9e08f8cb75",
    "type": "candy",
    "flavors": [
        {
            "text": "CHOCOLATE"
        },
        {
            "text": "VANILLA"
        }
    ]
}

What would be the correct way to index the text value within flavors so that I can lookup all documents that have per example the CHOCOLATE flavor?

2 
CREATE INDEX ix1 ON bucket( DISTINCT ARRAY v.text FOR v IN flavors END ) WHERE type = "candy";
SELECT * FROM bucket WHERE type = "candy" AND ANY v IN flavors SATISFIES v.text = "VANILLA" END;

Use Array Indexing

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/functions.html

3

How about indexing multiple fields within the flavors objects?

{
    "id": "05a9b954-bdee-4d7f-9715-8e9e08f8cb75",
    "type": "candy",
    "flavors": [
        {
            "text": "CHOCOLATE",
            "number": 60
        },
        {
            "text": "VANILLA",
            "number": 25
        }
    ]
}

And then perform the following query:

SELECT * FROM bucket
WHERE type = "candy"
AND ANY flavor IN flavors SATISFIES
    flavor.text = "VANILLA" AND
    flavor.number = 60
END

So pretty much indexing both text and number? Much appreciated!

4

At present ARRAY Indexing can index single field and index can have single array index key.

If there is equality predicates on both fields you can combine them as array in the index and query. Non equality predicates makes things much complex.

CREATE INDEX ix1 ON bucket( DISTINCT ARRAY [v.text, v.`number` ] FOR v IN flavors END ) WHERE type = "candy";
SELECT * FROM bucket WHERE type = "candy" AND ANY v IN flavors SATISFIES [v.text, v.`number`] =  ["VANILLA", 60] END;