Join Document using ANSI JOIN

N1QL
1

Hi,
Below is one of my where I am trying to use ANSI join.
I followed https://blog.couchbase.com/ansi-join-support-n1ql/ to implement ANSI JOIN

SELECT REGEX_REPLACE(p.place_name, “\<[^>]*>”, “”) AS place_name
FROM pins AS p USE INDEX(place-been-here USING GSI)
INNER JOIN assets AS a USE INDEX(asset-on-pins USING GSI)
ON META§.id = a.pin_id
WHERE p.place_id=‘RXZI4M’ AND p.pin_type=“atlas”
AND p.status=1 LIMIT 10 OFFSET 0;

I am two using Indexes

CREATE INDEX place-been-here ON pins(place_id,pin_create_date DESC) WHERE ((pin_type = “atlas”) and (status = 1))

And

CREATE INDEX asset-on-pins ON assets(pin_id,asset_name,created_on) WHERE (status = 1)

First Index is optional and the second one is mandatory. As it is mentioned in the documentation mandatory index is necessary. But running the above query gives me the following error.
"msg": “No index available for ANSI join term a”,
As I have mentioned above that I have created mandatory INDEX.
Below Is the sample asset document

{
  "created_on": 1546246436,
  "modified_on": 1546246436,
  "eloc": "U4LRPQ",
  "type": "atlas",
  "source": 101,
  "asset_name": "c7d371cb912be1da.jpg",
  "user_id": "1ca1fdddc37a2d6f1f7970c85ab0bcd4",
  "status": 0,
  "pin_id": "e724edc792ae8d2fc5cba2ef385a448c"
}
2

You Don’t have index. The above index doesn’t qualify due to partial index and query don’t have predicate a.status = 1;
Remove WHERE clause on above index
OR
Add a.status =1 to query where clause

1 Like