Search between multiple collections in elasticsearch having common fields

Question

I have two different collections as shown in table:

• T-shirt having fields [Size, ID]
• Brand having fields [Name, ID]

I need to search for only small sized T-shirt of Nike brand. The query should be like Size equals Small AND Name equals Nike but the AND operation does not give any result and the OR operation gives all four results of small sized t-shirt and nike brand. Both the collections are bound by common ID field. Is there any way to solve this?

Answer 1

The best possible solution is to denormalize the tables.