Array index field is not convered

SQL++
1

Hi,

I’m trying to use covered field when using an array index.

This is my document

{
    "values": {
        "blocks": [
            {
                "type": "pageMeta"
            }
        ]
    }
}

Index:

create index idx_test on engage_testing(DISTINCT array v.type for v in values.blocks WHEN v.type=‘pageMeta’);

Explain:

Explain shows that my query still fetches.

explain select first v for v in values.blocks when v.type=‘pageMeta’ end from engage_testing where any v in values.blocks satisfies v.type=‘pageMeta’ end

{
    "requestID": "91a53126-b694-45d8-9fc9-5b18d8031e74",
    "clientContextID": "d31795b2-90e6-4195-af1b-e85f7cb34a86",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "DistinctScan",
                        "scan": {
                            "#operator": "IndexScan",
                            "index": "idx_test",
                            "index_id": "62c14b9e984dfacd",
                            "keyspace": "engage_testing",
                            "namespace": "default",
                            "spans": [
                                {
                                    "Range": {
                                        "High": [
                                            "\"pageMeta\""
                                        ],
                                        "Inclusion": 3,
                                        "Low": [
                                            "\"pageMeta\""
                                        ]
                                    }
                                }
                            ],
                            "using": "gsi"
                        }
                    },
                    {
                        "#operator": "Fetch",
                        "keyspace": "engage_testing",
                        "namespace": "default"
                    },
                    {
                        "#operator": "Parallel",
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "Filter",
                                    "condition": "any `v` in ((`engage_testing`.`values`).`blocks`) satisfies ((`v`.`type`) = \"pageMeta\") end"
                                },
                                {
                                    "#operator": "InitialProject",
                                    "result_terms": [
                                        {
                                            "expr": "first `v` for `v` in ((`engage_testing`.`values`).`blocks`) when ((`v`.`type`) = \"pageMeta\") end"
                                        }
                                    ]
                                },
                                {
                                    "#operator": "FinalProject"
                                }
                            ]
                        }
                    }
                ]
            },
            "text": "select first v for v in `values`.blocks when v.type='pageMeta' end from engage_testing where any v in `values`.blocks satisfies v.type='pageMeta' end"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.851ms",
        "executionTime": "3.822ms",
        "resultCount": 1,
        "resultSize": 2708
    }
}

Is there a way to cover a block with type = ‘pageMeta’ in memory so that my query does not fetch the document? The document size is pretty big. I would like to avoid fetching document at all if that’s possible.

Thank you in advance.

2 
create index `idx_test` on engage_testing(ALL array v.type for v in `values` .blocks WHEN v.type=‘pageMeta’);


select v.type from engage_testing AS d UNNEST d.`values`.blocks AS v where v.type=‘pageMeta’;

Use UNNEST with ALL array index. Make sure UNNEST alias same as array index variable.

3

Thank you @vsr1. It worked as expected.

I have one more question. Is there a way to cover more than 1 field? In this case, it’s only covering v.type. Is there a way to include more than one?

I’ve tried

ALL array [v.type, v.name]

but it did not take the index at all.

4

This type of things are tricky and may not even choose index.
The predicate must have same as [v.type,v.name] and needs to be equality give right results

Right way to do this is:
CREATE INDEX ix1 ON default (ALL ARRAY [v.type,v.name] FOR v IN blocks END);
SELECT [v.type,v.name]
FROM default AS d
UNNEST d.blocks AS v
WHERE [v.type,v.name] = [“doc”,“xyz”];

5

Thank you @vsr1

I will try that as soon as possible. Thank you again!