Create a basic array index and query

SQL++
1

Database with millions of JSON records for example:

{
title: “book title”,
isbn: [“0345296265”, “9780345296265”],
type: “meta”
}

Goal to search for records that contain ISBN 0345296265

CREATE INDEX index_isbn ON BooksDB( DISTINCT ARRAY i FOR i IN isbn END) WHERE type = “meta”

SELECT * from BooksDB WHERE type = “meta” AND (ANY i IN isbn SATISFIES i = “0345296265” END)

Returns in 1ms without error but an empty result none found. Is there a better way to index/search arrays?

2

Query and index seems right.

If none found means no match. Do u have match? Index has entries?

CREATE INDEX index_isbn ON `BooksDB` ( DISTINCT isbn) WHERE type = "meta";
SELECT b.*
FROM BooksDB AS b
WHERE b.type = "meta" AND ANY v IN b.isbn SATISFIES v = "0345296265" END;

If you want use search https://blog.couchbase.com/announcing-flex-index-with-couchbase/

https://index-advisor.couchbase.com/indexadvisor/

3

vsr1: thank you for the reply. It is helpful to confirm the syntax is correct and there must be something else wrong. So, I am able to get it working. It turns out there was a typo in the index creation. That was all! Now it works wonderfully. Thanks again.

4

@stb3 , Glad it is working. You can also checkout https://index-advisor.couchbase.com/indexadvisor