What is the best way to get referenced data

SQL++
1

In my app i have 2 fields which are buckets and category’s and which a contact can be associated with. Rather then storing the name of bucket or category i store the bucket or category docid.

 "categories": [
		"contact_category::FC276FBD-DC3A-4E18-8244-E89EF24E022E"
	],
	 "buckets": [
		"leadbucket::1268F5B4-9BB1-4A76-BCEF-3BE1D59BF97C",
		"leadbucket::D6802064-8AC5-4E5A-855E-B59C32859C81"
	],

so what is the best approach to be able to get the name field of these bucket and category doc’s ? would it make sense to use a join and join these or would a sub query the better solution ? To keep in mind i am also going to use this when creating a list so there might be 100 contacts per page which could have 1 to 10 bucket keys and or category keys so we are talking possibly 1000 sub queries or joint.

for now i am using something like this

(SELECT text, meta().id as DocId
FROM `Contacts`
USE KEYS d.buckets) AS buckets

wondering if this is the best approach ?

2

It all depends on format of your output.

If you want Array of categories and array of buckets use subquery

     SELECT   (SELECT c.text, meta(c).id as DocId  FROM `Contacts`  AS c USE KEYS d. categories) AS categories
                          (SELECT b.text, meta(b).id as DocId  FROM `Contacts`  AS b USE KEYS d.buckets) AS buckets
     FROM .....
    WHERE ....

If you want separate documents use JOIN.