After indexing field n1ql gives no result

N1QL
1

I have following query,

SELECT pin.pin_id,pin.images,pin.identifier FROM activity USE INDEX(activity_pin_identifier USING GSI) UNNEST pins AS pin
WHERE META(activity).id=‘d2f058d3a8d0daa6535bcbf3abfc4819’ AND pin.identifier=‘83017b49ea3a4d8a9501c828de21286a’;

This gives no result when I index identifier key but when I remove indexing from identifier key, this query gives result but that is slow and takes somewhere around 400ms.I am using identifier to check duplicacy

Demo document I have in my database

[
  {
    "pin": {
      "action": {
        "commentCount": 0,
        "flagCount": 0,
        "likeCount": 0
      },
      "alt": 11.1,
      "cat_id": "2",
      "cat_name": "Traffic Issues",
      "child_cat_id": "15",
      "child_cat_name": "Accident",
      "course": 11.11,
      "expiry_date": 1829727003,
      "flag": 1,
      "identifier": "83017b49ea3a4d8a9501c828de21286a",
      "images": [],
      "modified_by": "",
      "name": "NAVIMAPS",
      "photo": "avatar.jpg",
      "pin_create_date": "2017-12-25 15:00:04",
      "pin_id": "35427",
      "pin_privacy": 0,
      "pin_rate": 0,
      "pin_story": "accident",
      "pin_story_title": "",
      "pin_type": "report",
      "place_id": "",
      "place_lat": 28.478456,
      "place_long": 77.15326,
      "place_name": "",
      "point": 0,
      "progress": 0,
      "quality": 30,
      "social_id": 101,
      "speed": 44.2,
      "status": 1,
      "user_id": "d2f058d3a8d0daa6535bcbf3abfc4819",
      "user_name": "NAVIMAPS",
      "utc": 1465195349
    }
  }
]

This is the query I ran to index identifier key
CREATE INDEX activity_pin_identifier ON activity((distinct (array (pin.identifier) for pin in pins end)))

2

Make sure your index is on-line and built correctly and did not skip the keys due to size limit.

By looking query the following might be better.

SELECT pin.pin_id, pin.images, pin.identifier 
FROM activity USE KEYS ["d2f058d3a8d0daa6535bcbf3abfc4819"]
UNNEST pins AS pin
WHERE pin.identifier="83017b49ea3a4d8a9501c828de21286a";
3

@vsr1 your query is fine but it also take more than 600 ms, what does index is on-line mean.In my database I have a bucket called activity which has pins array and which again has document in it so basically what I mean is that pins is basically an array of arrays and it can have 10k entries in it.so basically query is searching for pins based on identifier key which I am using to check duplicate records but on applying indexing on identifier key It does not return any result but when I remove indexing from identifier key It returns result but that query is slow.

4

Please check indexer.log and see if any documents skipped for indexing due to size limitations.

https://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/createindex.html

If you have array of array of 10K elements of huge documents it takes time.

You can also try following option.

SELECT  ps
FROM activity USE KEYS ["d2f058d3a8d0daa6535bcbf3abfc4819"]
ps = ARRAY {pin.pin_id, pin.images, pin.identifier} FOR pin IN pins WHEN pin.identifier="83017b49ea3a4d8a9501c828de21286a" END
WHERE  ARRAY_LENGTH(ps) > 0 ;