Create index on a bucket with array documents

SQL++
1

Hi folks

I Would like to create an index on in a bucket where each of the buckets documents are arrays
the document is an array which i append to it (.net sdk):
coll.DefaultCollection().MutateInAsync(dayDateEnKey, s => s.ArrayAppend("", new { entry }, createPath: true));

and i would like to use a group by clause to fetch the some of the data back organized
to my understanding to use a group by i would need to create an index on that said set of documents and point to the field being accessed (in this case any index of the array-document) but could not realize how exactly i could accomplish it via “create index” command

again and as always, any help would be very much appreciated, thanks!

2

You can use Primary Index.

CREATE PRIMARY INDEX on mybucket;
SELECT .............
FROM mybucket AS b
UNNEST b AS u
WHERE .......
GROUP BY .........

OR

Document it self not object you can reference that in the index using self

INSERT INTO mybucket VALUES ("k01",  [{"a":1,"b":"b"}, {"a":2,"b":"b2"}]};
CREATE INDEX ix1 ON mybucket (DISTINCT ARRAY v.a FOR v IN self END);

SELECT u.b, SUM(u.a) AS s
FROM mybucket AS b
UNNEST b AS u
WHERE u.a > 0
GROUP BY  u.b

If you still need help, post the sample document and what filed you want to group by or search.

3

priceless, thanks a lot for this… that self keyword and indexing like that is just priceless

I got around it by another way by a join and an inner FIRST in… LET variable, was amazed from the results (span over more than a few docs with each at about 2-3 mb) all this at like 2s with no index or nothing
many thanks