<p>I have a mongoDB aggregation query where the $match section contains a $in construct.</p>
<p> </p>
<div><strong> $match: {</strong></div>
<div><strong> "strategyName": "us market",</strong></div>
<div><strong> "basket.assetId": {"$in" : [1071,1080]}</strong></div>
<div><strong> }</strong></div>
<div> </div>
<div>The collection (performance) that this query is run on contains performance history for a large collection of stocks (assets). Each of these assets belongs to a sector (e.g. INDUSTRIALS) and each sector contains many assets. The performance collection only contains the assetId (a numeric primary key). It does not contain any information regarding sectors.</div>
<div> </div>
<div>I have two other collections of relevance:</div>
<ul><li><strong>sector</strong> - this contains the mapping between the numeric sectorId PK and sectorName, a string containing a text description.</li>
<li><strong>asset - </strong>this contains, among other things, a mapping of assetId to sectorId (i.e. it tells us which assets reside in which sector). A single sector will contain many assets</li>
</ul><div> </div>
<div>I want to be able to study sector performance and to do so I need to be able to use the <strong>$in</strong> construct shown above.</div>
<div> </div>
<div>To do what I want I have to perform 3 steps:</div>
<ol><li>Display sectorName and return sectorId (using a Report Parameter)</li>
<li>Using sectorId from (1) build a distinct list of assets (using a Data Set that actions a distinct query)</li>
<li>Insert the list of assets into the aggregation query and run the report. (???</li>
</ol><p> </p>
<p>The data set query is as follows:</p>
<p> </p>
<p><strong>{distinct: "asset", key: "_id", query: { "sector.sectorId" : 3 }}</strong></p>
<p> </p>
<p>The Value '3' in this query is replaced by the user selected Report Parameter (i.e. the sector they want to examine). The query returns a list of assets in a field 'values' (this is a list of integers but could easily be treated at a string)</p>
<p> </p>
<p> </p>
<p><strong>Question</strong></p>
<p> </p>
<p>How do I get the list of assets from the above dataset into a variable and then have that variable inserted into the $match statement of my main Data Set (shown at the very top).</p>
