I am having a hard time creating index that will support this query properly, all that I have tried had either only looked into a._type being “rate” or not looking at anything at all.
I basically have two different buckets, data & metadata.
I am trying to join metadata on data and also filter by existence of fields and several ids in the data object.
Here is how my query looks like:
SELECT rate FROM data AS rate JOIN metadata as service_to_cat_mapping ON KEYS rate.service_to_cat_mapping_id WHERE rate._type = "rate" AND service_to_cat_mapping._type = "service_to_cat_mapping" AND rate.service_category_id = 'cat_1234' AND rate.custom_data.zip_code = '11111' AND (rate.custom_data.city = 'New York' OR rate.custom_data.city = '' OR rate.custom_data.city is null) AND rate.custom_data.is_valid = false AND service_to_cat_mapping.is_active = true AND rate.job_id IN ['job_123','job_1234','job_12345'] AND rate.provider_id IN ['provider_123','provider_1234','provider_12345']
Couchbase version: Version: 4.1.0-5005 Community Edition (build-5005)