I Have tried to prepare the script using Chat GPT and i have modified few things it is working fine build the indexes , but here there is one problem build indexes are going by sequential order, in your case above code trigger whole data stream in _default data space so all indexes are build at once. but here it is considering each collection as one data space. so if any of indexes under that collection then all of that indexes might build all at once.

in my case there are 3 scope and each scope contains 23 collections and each collection have one single index. that's might be the reason for its behaviour.
thank you in advance , please add any changes are require in this code for better performance or improvements
i have not tested it for normal buckets which does not have scopes and collections.

#!/bin/bash

#set -x # Enable debugging

QUERY_HOST=http://localhost:8091
USERNAME=Administrator
PASSWORD=password

# Function to build indexes for a given bucket, scope, and collection
build_indexes() {
local bucket=$1
local scope=$2
local collection=$3

echo "Building indexes for bucket: $bucket, scope: $scope, collection: $collection ..."

# Query to get index names for the collection
index_names=$( /opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -q=true -s="SELECT name FROM system:indexes WHERE keyspace_id = '$collection' AND scope_id = '$scope' AND bucket_id = '$bucket' AND state = 'deferred'" | \
sed -n -e '/{/,$p' | \
jq -r '.results[].name' | \
sed ':a;/.*/{N;s/\n/\`,\`/;ba}' )

echo "Index Names: $index_names"

if [ -n "$index_names" ]; then
# Build the indexes
/opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -s="$(echo "BUILD INDEX ON \`$bucket\`.\`$scope\`.\`$collection\` (\`$index_names\`)")"
else
echo "No deferred indexes to build for bucket: $bucket, scope: $scope, collection: $collection ..!"
fi

# Wait for completion
until [ $(/opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -q=true -s="SELECT COUNT(*) as unbuilt FROM system:indexes WHERE keyspace_id = '$collection' AND scope_id = '$scope' AND bucket_id = '$bucket' AND state <> 'online'" | sed -n -e '/{/,$p' | jq -r '.results[].unbuilt') -eq 0 ]; do
echo "Waiting for indexes to build for bucket: $bucket, scope: $scope, collection: $collection ..."
sleep 5
done

echo "All indexes built for bucket: $bucket, scope: $scope, collection: $collection !!!"
}

# Main loop to iterate over each bucket
for bucket in "bkt" "bkt_cache" ; do
echo "###Processing bucket: $bucket ...###"

# Get all scopes for the bucket
scopes=$( /opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -q=true -s="SELECT DISTINCT raw \`keyspaces\`.\`scope\` from system:keyspaces where \`keyspaces\`.\`bucket\`='$bucket'; " |jq -r '.results[]' )

echo "Scopes: $scopes"

if [ -z "$scopes" ]; then
echo "No valid scopes found for bucket: $bucket ..!"
continue
fi

for scope in $scopes; do
# Skip if the scope is null
if [ "$scope" == "null" ] || [ -z "$scope" ]; then
echo "Skipping null or empty scope for bucket: $bucket ..!"
continue
fi

echo "Processing scope: $scope >#"

# Get all collections for the scope
collections=$( /opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -q=true -s="select DISTINCT raw \`keyspaces\`.\`id\` FROM system:keyspaces where \`keyspaces\`.\`scope\`='$scope' and \`keyspaces\`.\`bucket\`='$bucket';" | jq -r '.results[]' )

echo "Collections: $collections "

if [ -z "$collections" ]; then
echo " No collections found for scope: $scope in bucket: $bucket ..!"
continue
fi

for collection in $collections; do
# Build indexes for each collection
build_indexes "$bucket" "$scope" "$collection"
done
done
done


the output will be like bellow ,

root@cbr-725-couchbase-cluster-3:/data# cat script_output1.log
Processing bucket: upc
Scopes: null
scope1
stg3
Skipping null or empty scope for bucket: upc
Processing scope: scope1
Collections: product
attachment
glCode
....

Building indexes for bucket: bucket1, scope: bucket1, collection: product
Index Names:
No deferred indexes to build for bucket: bucket1, scope: bucket1, collection: product
All indexes built for bucket: bucket1, scope: bucket1, collection: product

this is very helpful where there is no scopes and collection.
how do we same operation when we have more scopes and more collections in a bucket? Brant Burnett could you help on same please.

Your main thing to adjust will be how it's inserting the index name into the BUILD query. The example above runs one BUILD per bucket, you'll instead need to run one BUILD per collection. The query to get the list of indices changes to:

SELECT name FROM system:indexes where bucket_id = '$i' AND scope_id = '$j' AND keyspace_id = '$k'

In the above example, $k would be the collection name. Then adjust the query which actually builds the indices to be:

echo "BUILD INDEX ON \`$i\`.\`$j\`.\`$k\` (\`$( \

I'm less certain about the best way to loop and get three variables $i, $j, and $k from the loop. That's a bash scripting question, which is an area I'm not an expert.

Or from Couchbase documentation :

BUILD INDEX ON `travel-sample` ((
SELECT RAW name
FROM system:indexes
WHERE keyspace_id = 'travel-sample'
AND state = 'deferred' ));

source : https://docs.couchbase.com/...

Cool, that wasn't an option on older versions of Couchbase, any idea when it was added? I'm guessing around 5.5 since it's using subqueries, but I'm not sure.

Thanks for the speedy reply Brant. Actually there are 10 indexes in that bucket. I don't believe that's the issue. 😔

Are the indexes already built, though? This script filters to only get indexes that are waiting to be built after creating using {"defer_build": true}, they will show as "Created" as the status in the Couchbase UI.

Brant - that was the issue, - THANK YOU!!!!! How can I a make a humble donation for your effort?

Instead of a donation, I just ask that you pay it forward. Whenever you find something useful, post it somewhere for others. Or next time you find a simple bug in an open source project, put in a pull request to fix it yourself.

Indeed I will!! You got it!

I got syntax errors and I just copy pasted the above

BUILD INDEX ON `customers` (``)
{
"requestID": "cff16fc9-0f3f-4771-a8b3-1f5eff0be018",
"errors": [
{
"code": 3000,
"msg": "syntax error - at `"
}
],
"status": "fatal",
"metrics": {
"elapsedTime": "446.066µs",
"executionTime": "343.978µs",
"resultCount": 0,
"resultSize": 0,
"errorCount": 1
}
}

Judging by the generated query it was running, I think that means that there were no indexes on the `customers` bucket waiting to be built. I think that's an edge case this script doesn't account for.

I have opened a defect to improve this behaviour MB-32153. Which is planned for the next major release.

Awesome!

Nice script Brant, couple of notes:
1) AND state <> 'online'" is looking weird in the above script section (check it and you'll understand)
2) On certain configs you'll need to supply cbq with the credentials (cbq -u=Administrator -p=password -e $QUERY_HOST)
3) cbq path can be set as variable in the beginning of the script
4) maybe we can fetch all bucket names through n1ql instead of having to write them manually

Thanks a lot

Thanks for the feedback, I adjusted the script based on some of your points. I shied away from fetching all the bucket names for now, on the theory that a user may not want to hit all of the buckets in some cases.