CODEWITHSUNDEEP
arrayscouchbasesql++
Hani Gotc
Hani Gotc

Reputation: 900

Couchbase: Create Index on Array containing Array of objects

I am trying to create an index on the following structure:


"creators": [
   {
        "ag_name": "Travel",
        "ag_ids": [
            {
                "id": "1234",
                "type": "TEST"
            }
        ]
    }
]

The index that I created is the following:

CREATE INDEX `Test_Index` ON `bucket`((ARRAY(ARRAY [t.ag_name, v] FOR v IN OBJECT_VALUES(t.`ag_ids`) END) FOR t IN `indexed_data`.`pos` END))
WHERE ((SUBSTR0((META().`id`), 0, 2) = "tt") AND (`indexed_data` IS VALUED))

Question

I started using couchbase a couple of hours ago. I was wondering. Is the index that I created correct? I mean it is being created successfully. But I am not sure if it’s covering all the fields including the ones in the substructure array

Query

SELECT META().id
FROM bucket
WHERE SUBSTR0((META().`id`), 0, 2) = "tt"
AND indexed_data.reservation_type = "HOLA"
AND chain_code="FOO1"
AND indexed_data.property_code="BAR1"
AND ANY creator IN indexed_data.creators SATISFIES creator.ag_name="FOO" END
AND ANY creator IN indexed_data.creators SATISFIES (ANY ag in creator.ag_ids SATISFIES ag.id="1234" END AND ANY ag in creator.ag_ids SATISFIES ag.type="TEST" END) END

Upvotes: 2

Views: 240

Answers (2)

vsr
vsr

Reputation: 7414

The only way above query you can have covering index indexed_data.creators ARRAY as whole. Example 1 at https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html#covering-array-index. You can also create ARRAY index one of the field from ARRAY. As you are referencing multiple fields from array you will not able to use Implicit Covering Array Index that described above link

CREATE INDEX ix1 ON bucket (chain_code,indexed_data.reservation_type, indexed_data.property_code,  indexed_data.creators ) 
    WHERE SUBSTR0((META().`id`), 0, 2) = "tt";

Also you are doing AND of multiple ANY clauses of same ARRAY. i.e. means it can match with any position in the array If need same position have all matched you should use following query.

SELECT META().id
FROM bucket
WHERE SUBSTR0((META().`id`), 0, 2) = "tt"
   AND indexed_data.reservation_type = "HOLA"
   AND chain_code="FOO1"
   AND indexed_data.property_code="BAR1"
   AND (ANY c IN indexed_data.creators
        SATISFIES c.ag_name = "FOO"
           AND (ANY ag IN c.ag_ids
                SATISFIES ag.id = "1234" AND ag.type = "TEST"
                END)
        END);

Upvotes: 3

Matthew Groves
Matthew Groves

Reputation: 26169

I don't know if this is the best way to determine if an index is covering or not, but if you click "Plan" in the Query Workbench, you will see all the various steps visualized. If you see a "Fetch" step, then the index(es) being used are not covering your query.

Query plan visualizer

Further, if you click "Advice", a covering index will be recommended for your query.

Upvotes: 3

Related Questions