Array Indexing in Couchbase 4.5

SQL++
1

Hi, I’m hoping someone can help me with this as I’ve been working on this problem for a while. Im trying to create a index on an array but I cant seem to find the right index. I’m using Couchbase 4.5

My object looks like this:

{
  "tagMaps": {
    "types": [
      {
        "key": "types",
        "value": "Cooking Demonstration/Lesson",
        "lastChanged": 1473064218455,
        "default": false
      },
      {
        "key": "types",
        "value": "Brewery Tour",
        "lastChanged": 1473064218455,
        "default": false
      }
    ]
  }
}

I’ve tried several different indexes. One that I have tried is below.

$ CREATE INDEX types0 on write (DISTINCT ARRAY x FOR x IN tagMaps.types END);

and the call.

$ Explain select * from write where any x in tagMaps.types satisfies x.`value` = 'Brewery Tour' END



{
    "requestID": "8c0b7d10-df0d-4d00-b2b3-99cbcce3fde3",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "PrimaryScan",
                        "index": "#primary",
                        "keyspace": "write",
                        "namespace": "default",
                        "using": "gsi"
                    },
                    {
                        "#operator": "Parallel",
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "Fetch",
                                    "keyspace": "write",
                                    "namespace": "default"
                                },
                                {
                                    "#operator": "Filter",
                                    "condition": "any `x` in ((`write`.`tagMaps`).`types`) satisfies ((`x`.`value`) = \"Brewery Tour\") end"
                                },
                                {
                                    "#operator": "InitialProject",
                                    "result_terms": [
                                        {
                                            "expr": "self",
                                            "star": true
                                        }
                                    ]
                                },
                                {
                                    "#operator": "FinalProject"
                                }
                            ]
                        }
                    }
                ]
            },
            "text": "select * from write where any x in tagMaps.types satisfies x.`value` = 'Brewery Tour' END"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "29.595475ms",
        "executionTime": "29.521335ms",
        "resultCount": 1,
        "resultSize": 1884
    }
}
2

try create index by

CREATE INDEX `types0` ON write ((DISTINCT (ARRAY (x.`value`) FOR x IN tagMaps.types END))) USING GSI;
1 Like