I am wondering if there is a way and what the best way would be for scale and performance.
I have a bucket that stores all CRM Data including documents of type FARM. The user can get a list of all properties in
by Farm id via
where _type = 'farm' and farmID = 'xxxxxx'
so far so good. Now i have bucket which stores the all property’s which have been listed, sold etc over time. Each Listing has its own ID lets call it listing ID and there is a shared id for a listing for same property which is the apn or parcel number.
that same apn is also part of the farm. So here is what i would like to get from the query
All the farm info as well as the latest Listing for the apn of that farm and its status as long as the status is not closed. So not sure what the best way would be as a sub query can only return one field of the listing doc. Also size of bucket might be an issue as we grow about 250K a month and currentle for test i have loaded abiut 18 month but will do full load which will be closer to 10 million docs
If you need to JOIN , you must use ASNI JOIN. Checkout the following links