Array Index with with objects


#1

Hi,
So I am planning on doing some array indexing on data like -

{
"activity": 1234,
"activityDate": "20180922"
"participants": [{
		"groupId": 123,
		"userId": 44131
	},
	{
		"groupId": 1212,
		"userId": 7997
	}
]

}

Let’s say the bucket is called tournament.
Initially, I created an index using the following query ->
CREATE INDEX arr_index
ON tournament(DISTINCT ARRAY v FOR v IN participants END);

Is this a good way to use array index if I expect the following queries -

  1. fetch using participant.groupId [ select * from tournament where documentType=“Activity” and any participant in participant SATISFIES participant.groupId=2 END; ]

  2. fetch using participant.userId

  3. fetch using participant.groupId and participant.userId [ both conditions]

I looked up the query plan for the above queries and the spans array ( which from what I’ve learned is something like the filter for the index service, please correct me if I am wrong here) looked like -

“spans”: [
{
“range”: [
{
“inclusion”: 0,
“low”: “null”
}
]
}

From what I have seen up till now the above should mean I did something wrong .
So, I had the following questions -

  1. Does the index I shared above just form the index on each individual “participant” value and treats them as a blob of sort? And you cannot use the index for faster access to nested fields in this case?
  2. Will creating indexes on groupId and userId individually be more beneficial if I want queries like the once I shared above?

#2

Array Index can index single field. In your case you indexing as whole object. Query to use SATISFYING clause must use same indexed key i.e in your need object comparisons which will not possible all 3 cases.

Define index on groupId and use case 1, 2. case 1 can push the predicate to indexer. case 2 pushes groupId and userId will be applied post Fetch.

CREATE INDEX arr_index_gid ON  tournament (DISTINCT ARRAY v.groupId FOR v IN participants END) 
WHERE documentType=“Activity”  ;
SELECT  * 
FROM  tournament
WHERE documentType=“Activity”  AND ANY p IN participants SATISFIES p.groupId=2  AND p.userId = 1234END; 
SELECT  * 
FROM  tournament
WHERE documentType=“Activity”  AND ANY p IN participants SATISFIES p.groupId=2 END; 

Similarly you can define another index on userId.

If you think you are suing equality only you can also try following for case 3

CREATE INDEX arr_index_gid ON  tournament (DISTINCT ARRAY [v.groupId,v.userId] FOR v IN participants END) 
WHERE documentType=“Activity”  ;
SELECT  * 
FROM  tournament
WHERE documentType=“Activity”  AND ANY p IN participants SATISFIES [p.groupId, p.userId] = [2,1234]END;

#3

Oh I see, now I have a clearer picture. Thanks!