Indexing Nested Objects

SQL++
1

I have the following object

{
  "type": "devicestate",
  "device": {
    "id": "005f7b1b-7cce-5549-9748-8f80a620c572",
    "imei": "015422000009356",
    "location": {
      "latitude": 27.986065,
      "longitude": 86.92262,
      "timestamp": "2019-05-24T17:41:18.708+00:00"
    },
    "lastDeviceTimestamp": "2019-05-24T17:41:19.762+00:00",
    "online": false,
    "powerStatus": 0,
    "deleted": false,
  },
  "activitySettings": {
    "lowBattery": {
      "enabled": false
    },
    "chargedBattery": {
      "enabled": false
    },
    "offline": {
      "enabled": false
    }
  }
}

and here are the indexes that I tried

CREATE INDEX test1 ON mybucket((distinct ((device.imei)))) WHERE (type = "devicestate")

CREATE INDEX test2 ON mybucket((distinct ((device.imei)))) WHERE ((type = "devicestate") and ((device.imei) is not missing))

and all the following queries fail due to a missing index

select device.imei from mybucket
where device.imei = '015422000009356'
and type = "devicestate"

select * from mybucket
where device is not missing and device.imei is not missing
and device.imei = '015422000009356'
and type = "devicestate"

I would appreciate if someone could point out my mistake.
Cheers

2

DISTINCT keyword in index for ARRAY indexing only https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html. If the nested object index key must be path without DISTINCT keyword

CREATE INDEX test1 ON mybucket(device.imei) WHERE (type = "devicestate");

select device.imei from mybucket
where device.imei = '015422000009356'
and type = "devicestate";
3

Thank you very much. For the reference, the following index worked

CREATE INDEX test2 ON mybucket(device.imei) 
WHERE ((type = "devicestate") and ((device.imei) is not missing))

but without having “device.imei is not missing”, query engine cannot find the index.

Is this because of other document types that I have in the bucket? Why type = “devicestate” is not sufficient when all devicestate documents have device.imei value.

4

Index where clause doesn’t required ((device.imei) is not missing)).

Leading index key must be part of the query predicate because If leading index key is not part of the document indexer will not index that document. Unless query has predicate on leading index key the index will not chosen (query requesting the missing entries too)

CREATE INDEX test1 ON mybucket(device.imei) WHERE (type = "devicestate");

select device.imei from mybucket
where device.imei  IS NOT MISSING
and type = "devicestate";