I have the below requirement and need help to know if it is achievable with JOINs or Block Join (Nested documents)-
There are multiple tables, one primary table having all the basic details about product, the primary key is Product ID. There are 6-7 other tables having other details of product, it has Product ID column too but can have multiple entries for single Product ID.
Can you please let me know if the below are possible-
- Can we get data from multiple collections in the search results. The results should contain only one record for a single product ID?
- Can we search across multiple collections in a single query and then club the results, so that final search results will have single result for each Product ID.
- Can we perform join on more than 2 collections as we need to search across 6-7 collections and then merge the data based on product ID.
- Can we query Parent and child in nested index at the same time. Example- perform search on column1 from Parent and Column2 from Child and get the parent records with nested child in search results?
- If we can perform fielded search across multiple collections in the single query,will the filters from different collections be returned in a single search response