How does index on array works

SQL++
,
1

Hello all,

I’m new to Couchbase and I’m having a hard time trying to use indexes to get my data back. To make it worse, if I don’t create an index I cannot even get a single document on the ‘Bucket’ view on the GUI.

So let me give you an example of how my data looks like:

"id": "mymachine.com",
  "scripts": [
    {
      "name": "script1.py",
      "exit_code": "0",
      "datetime": "2021/07/07-03:16:07"
    },
    {
      "name": "script2.py",
      "exit_code": "0",
      "datetime": "2021/07/07-03:16:07"
    },
    {
      "name": "script1.sh",
      "exit_code": "1",
      "datetime": "2021/07/07-03:16:07"
    }
	]
}

And I create an index as follows (I want to query on the machine name, script name and exit status):

CREATE INDEX mybucket_success ON `mybucket`(id, DISTINCT ARRAY s.name FOR s IN scripts WHEN s.exit_code == 0 END)

But when I try to run the following queries I always the same error:

select id from `mybucket` data where scripts.exit_code == 0
// Another one:
select * from `mybucket`
// Or this:
SELECT * FROM `mybucket` WHERE ANY s IN scripts SATISFIES s.exit_code = 0 END

At this point I’m sure I do not understand how the index works or my assumption of how it should work is wrong.

Can someone give a hint on what I’m doing wrong here?

Thanks in advance,

2

For this query to work:

select * from `mybucket`;

you’ll need a primary index on the bucket:

create primary index on `mybucket`;

When you want to search on the exit_code in an array, you need to put that as the array index element instead of the when clause of the array index:

create index ix_scripts_exitcode on `mybucket` (DISTINCT array s.exit_code FOR s IN scripts END, id);

query:

select id from `mybucket` as data where ANY s IN data.scripts SATISFIES s.exit_code = 0 END;

note also that you originally have id as the leading index key, and since your query does not have a predicate on id the index will not be chosen. The suggested index has id as the second field so it can be used for covering purpose.

Also try:

http://index-advisor.couchbase.com

1 Like
3

Now I understand how the two type of indexes work, your solution was very clear and detailed. Thanks a lot for the help!

–Jose