Build all indexes in a bucket once including default , all scope and collection level indexes

Couchbase Server
1

Hi team ,

I am using this script to build indexes together which is not building all scope level indexes together and i need pass everytime each scope name, Is there way I can tune this query to build all indexes at once. Thank you

BUILD INDEX ON Test
((SELECT RAW name
FROM system:indexes
WHERE keyspace_id = ‘Test’
AND state = ‘deferred’
));

BUILD INDEX ON landmark ((
SELECT RAW name
FROM system:indexes
WHERE keyspace_id = ‘landmark’
AND scope_id = ‘inventory’
AND bucket_id = ‘travel-sample’
AND state = ‘deferred’ ));

2

Can you try:

SELECT RAW CONCAT("BUILD INDEX ON ", k ,  "(['", CONCAT2 ("','", inames), "']);")
  FROM system:indexes AS s
  LET bid = CONCAT("\`",s.bucket_id, "\`"),
      sid = CONCAT("\`", s.scope_id, "\`"),
      kid = CONCAT("\`", s.keyspace_id, "\`"),
      k = NVL2(bid, CONCAT2(".", bid, sid, kid), kid)
  WHERE s.namespace_id = "default"
  GROUP BY k
  LETTING inames = ARRAY_AGG(s.name) FILTER (WHERE s.state = 'deferred')
  HAVING ARRAY_LENGTH(inames) > 0;

This should return all the indexes ti be built.

2 Likes
3

@pdoddi Brilliant , Thank you. Please suggest if you have any book to learn N1QL queries .