Possible to do compound array index?

SQL++
1

I have the following below. I want to find matches on multiple attributes but can not get it working. Anyone have an idea? Edited: I replaced ‘key’ with k and ‘value’ with v.

Index:

CREATE INDEX entity_attributes_key_value_idx ON couchbase_test(DISTINCT ARRAY (attribute.k || attribute.v) FOR attribute IN attributes END) WHERE _class = "BaseEntity" USING GSI;

Document:

{
  "updateTime": 1462028351012,
  "location": {
    "x": -78,
    "y": 33
  },
  "attributes": [
    {
      "classification": "U",
      "v": "0",
      "k": "0"
    },
    {
      "classification": "U",
      "v": "0",
      "k": "1"
    },
    {
      "classification": "U",
      "v": "0",
      "k": "2"
    },
    {
      "classification": "U",
      "v": "0",
      "k": "3"
    },
    {
      "classification": "U",
      "v": "0",
      "k": "4"
    },
    {
      "classification": "U",
      "v": "0",
      "k": "5"
    },
    {
      "classification": "U",
      "v": "0",
      "k": "6"
    },
    {
      "classification": "U",
      "v": "0",
      "k": "7"
    },
    {
      "classification": "U",
      "v": "0",
      "k": "8"
    },
    {
      "classification": "U",
      "v": "0",
      "k": "9"
    }
  ],
  "_class": "BaseEntity"
}

Query 1:

SELECT couchbase_test.* FROM couchbase_test WHERE _class = "BaseEntity" AND ANY attribute IN attributes SATISFIES (attribute.k || attribute.v) = ("0" || "0") END AND ANY attribute IN attributes SATISFIES (attribute.k || attribute.v) = ("1" || "0") END
== no match…cannot match both at same time

Query 2:

SELECT couchbase_test.* FROM couchbase_test WHERE _class = "BaseEntity" AND ANY attribute1 IN attributes, ANY attribute2 IN attributes SATISFIES (attribute1.k || attribute1.v) = ("0" || "0") AND (attribute2.k || attribute2.v) = ("1" || "0") END
== no index found…does not match index text

Query 3:

SELECT couchbase_test.* FROM couchbase_test WHERE _class = "BaseEntity" AND ANY attribute1 IN attributes SATISFIES (attribute1.k || attribute1.v) = ("0" || "0") END AND ANY attribute2 IN attributes SATISFIES (attribute2.k || attribute2.v) = ("1" || "0") END
== no index found…does not match index text

Query 4:

SELECT couchbase_test.* FROM couchbase_test UNNEST couchbase_test.attributes attribute WHERE _class = "BaseEntity" AND EXISTS (SELECT attribute.k WHERE (attribute.k || attribute.v) = ("0" || "0")) AND EXISTS (SELECT attribute.k WHERE (attribute.k || attribute.v) = ("1" || "0"))
== no index found…not sure

2

Hi,

I would recommend two changes to your index and queries:

(1) Change single quotes to back ticks for escaping key and value. Single quotes are not valid for escaping keywords.

(2) Change (attribute.`key` || attribute.`value`) to [ attribute.`key`, attribute.`value` ]. This is cleaner and more precise.

3

Thanks Gerald for the help. The quotes were back ticks, but that caused issues with the forum’s formatting. I changed the index to use your suggestion and got the right query going for single key-value match. What does the proper query for matching multiple key-values at a time?

Index:

CREATE INDEX entity_attributes_key_value_idx ON couchbase_test(DISTINCT ARRAY [attribute.k, attribute.v] FOR attribute IN attributes END) WHERE _class = "BaseEntity" USING GSI;

Query:

SELECT couchbase_test.* FROM couchbase_test WHERE _class = "BaseEntity" AND ANY attribute IN attributes SATISFIES [attribute.k, attribute.v] = ["0", "0"] END

4

Hi @dnguyen,

Your index still uses single quotes instead of back ticks. Your query does not use back ticks to escape `key` and `value`.

After fixing those two things, please post the EXPLAIN output.

5

The last query with [attribute.k, attribute.v] = ["0", "0"] works using the entity_attributes_key_value_idx according to the EXPLAIN output. Is there a way to query for documents with matches on all key-values like [["0", "0"], ["1", "0"], ...]? I am thinking there is some nesting of ANY and EVERY operations.

6

You can try EXPLAIN on the following. It will work better in 4.5 Beta (coming in a few days), but you try it on 4.5 DP first:

SELECT couchbase_test.* FROM couchbase_test WHERE _class = “BaseEntity” AND ANY attribute IN attributes SATISFIES [attribute.k, attribute.v] IN [ [“0”, “0”], {“1”, “0”], … ] END

In Beta, the IN clause will support a large number of elements for indexing.

7

The query below is using entity_attributes_key_value_idx but that matches entities that have at least one key-value match, which is OR’ed.

... ANY attribute IN attributes SATISFIES [attribute.k, attribute.v] IN [["0", "0"], ["1", "0"]] END

Is there a query that matches entities that have all key-values specified, which is AND’ed?

... EVERY keyValue IN [["0", "0"], ["1", "0"]] SATISFIES keyValue IN (DISTINCT ARRAY [attribute.k, attribute.v] FOR attribute IN attributes END) END

8

Try ANY AND EVERY instead of ANY. Use EXPLAIN to make sure you still using the array index.

9

ANY AND EVERY gives me a syntax error. It is weird how ANY, EVERY are giving different results:

ANY:

cbq> EXPLAIN SELECT * FROM `couchbase_test` WHERE `_class` = "BaseEntity" AND ANY attribute IN attributes SATISFIES [attribute.`key`, attribute.`value`] IN [["0", "0"], ["1", "0"]] END;
{
    "requestID": "9cb0e67c-a397-4d23-a8e1-6141a6614f43",
    "signature": "json",
    "results": [
        {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "UnionScan",
                    "scans": [
                        {
                            "#operator": "IndexScan",
                            "index": "entity_attributes_key_value_idx",
                            "keyspace": "couchbase_test",
                            "namespace": "default",
                            "spans": [
                                {
                                    "Range": {
                                        "High": [
                                            "[\"0\",\"0\"]"
                                        ],
                                        "Inclusion": 3,
                                        "Low": [
                                            "[\"0\",\"0\"]"
                                        ]
                                    }
                                },
                                {
                                    "Range": {
                                        "High": [
                                            "[\"1\",\"0\"]"
                                        ],
                                        "Inclusion": 3,
                                        "Low": [
                                            "[\"1\",\"0\"]"
                                        ]
                                    }
                                }
                            ],
                            "using": "gsi"
                        }
                    ]
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "Fetch",
                                "keyspace": "couchbase_test",
                                "namespace": "default"
                            },
                            {
                                "#operator": "Filter",
                                "condition": "(((`couchbase_test`.`_class`) = \"BaseEntity\") and any `attribute` in (`couchbase_test`.`attributes`) satisfies ([(`attribute`.`key`), (`attribute`.`value`)] in [[\"0\", \"0\"], [\"1\", \"0\"]]) end)"
                            },
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "as": "_ID",
                                        "expr": "(meta(`couchbase_test`).`id`)"
                                    },
                                    {
                                        "as": "_CAS",
                                        "expr": "(meta(`couchbase_test`).`cas`)"
                                    },
                                    {
                                        "expr": "`couchbase_test`",
                                        "star": true
                                    }
                                ]
                            },
                            {
                                "#operator": "FinalProject"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "9.117564ms",
        "executionTime": "9.079898ms",
        "resultCount": 1,
        "resultSize": 3481
    }
}

EVERY:

cbq> EXPLAIN SELECT * FROM `couchbase_test` WHERE `_class` = "BaseEntity" AND EVERY attribute IN attributes SATISFIES [attribute.`key`, attribute.`value`] IN [["0", "0"], ["1", "0"]] END;
{
    "requestID": "cb3f8534-d013-46c0-bfce-bb5097765766",
    "signature": "json",
    "results": [
        {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "PrimaryScan",
                    "index": "#primary",
                    "keyspace": "couchbase_test",
                    "namespace": "default",
                    "using": "gsi"
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "Fetch",
                                "keyspace": "couchbase_test",
                                "namespace": "default"
                            },
                            {
                                "#operator": "Filter",
                                "condition": "(((`couchbase_test`.`_class`) = \"BaseEntity\") and every `attribute` in (`couchbase_test`.`attributes`) satisfies ([(`attribute`.`key`), (`attribute`.`value`)] in [[\"0\", \"0\"], [\"1\", \"0\"]]) end)"
                            },
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "as": "_ID",
                                        "expr": "(meta(`couchbase_test`).`id`)"
                                    },
                                    {
                                        "as": "_CAS",
                                        "expr": "(meta(`couchbase_test`).`cas`)"
                                    },
                                    {
                                        "expr": "`couchbase_test`",
                                        "star": true
                                    }
                                ]
                            },
                            {
                                "#operator": "FinalProject"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "8.17377ms",
        "executionTime": "8.131967ms",
        "resultCount": 1,
        "resultSize": 2174
    }
}

ANY AND EVERY:

cbq> EXPLAIN SELECT * FROM `couchbase_test` WHERE `_class` = "BaseEntity" AND ANY AND EVERY attribute IN attributes SATISFIES [attribute.`key`, attribute.`value`] IN [["0", "0"], ["1", "0"]] END;
{
    "requestID": "ac089ea9-f24b-4b5e-a867-78f17d9d7608",
    "errors": [
        {
            "code": 3000,
            "msg": "syntax error - at AND"
        }
    ],
    "status": "fatal",
    "metrics": {
        "elapsedTime": "895.311µs",
        "executionTime": "839.583µs",
        "resultCount": 0,
        "resultSize": 0,
        "errorCount": 1
    }
}
10

Ok, ANY AND EVERY will be in the Beta. My apologies.

It is normal for ANY to return different results from EVERY. First of all, they mean different things. And as for the EXPLAIN, array indexing is supported for ANY and for ANY AND EVERY, but not for EVERY.