Possible to do compound array index?

dnguyen · April 30, 2016, 7:21pm

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

geraldss · May 1, 2016, 2:56am

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.

dnguyen · May 1, 2016, 11:38pm

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

geraldss · May 2, 2016, 2:39am

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.

dnguyen · May 2, 2016, 3:07am

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.

geraldss · May 2, 2016, 5:49am

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.

dnguyen · May 2, 2016, 11:06pm

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

geraldss · May 2, 2016, 11:22pm

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

dnguyen · May 3, 2016, 2:31am

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
    }
}

geraldss · May 3, 2016, 2:45am

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.

Topic		Replies	Views
Array Indexing in Couchbase 4.5 SQL++	1	1306	September 9, 2016
Effective array index to match multiple items Couchbase Server	7	1816	March 22, 2017
Composite index from an array of objects SQL++ arrays , array-query	4	2935	July 14, 2021
Array Index on multiple arrays SQL++ index	7	3171	June 6, 2018
How to index multiple elements in ARRAY along with CONCATENATE operator? Couchbase Server	8	647	December 9, 2022

Possible to do compound array index?

Related topics