After indexing field n1ql gives no result

SQL++
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.

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 ;