Simulating a SQL query in MQL

Hi all,
a question about “copying” a SQL query in MQL.
The SQL is
select a.*
** from T a**
** where a.id = $host_value**
** and a.value >= (select avg(b.value) from T b where b.id = a.id)**
The training collection is “icecream_data”: below the json format.
I had the following solution,
Does anyone else has a “better” solution or idea?

Thanks to all

PIPELINE
db.icecream_data.aggregate([
{
$match:
{ _id : ObjectId(“59bff494f70ff89cacc36f90”) }
},
{
$unwind:
{
path: “$trends”
}
},
{ $facet:
{
out_1: [{
$project:
{
_id: 0,
month:"$trends.month",
icecream_sales_in_millions:"$trends.icecream_sales_in_millions"
}
}],
out_2: [{
$group:
{
_id: null,
avg_icecream_sales_in_millions:{ $avg:"$trends.icecream_sales_in_millions" }
}
}]
}
},
{
$unwind:
{
path: “$out_1”
}
},
{
$unwind:
{
path: “$out_2”
}
},
{
$match:
{
$expr:{ $gte: [ “$out_1.icecream_sales_in_millions”, “$out_2.avg_icecream_sales_in_millions” ] }
}
},
{
$project:
{
month:"$out_1.month",
sales_gte_avg: “$out_1.icecream_sales_in_millions”
}
}
]).pretty()

COLLECTION JSON FORMAT
{"_id":{"$oid":“59bff494f70ff89cacc36f90”},
“trends”:[
{“month”:“January”,“avg_high_tmp”:{"$numberInt":“42”},“avg_low_tmp”:{"$numberInt":“27”},“icecream_cpi”:{"$numberDouble":“238.8”},“icecream_sales_in_millions”:{"$numberInt":“115”}},
{“month”:“February”,“avg_high_tmp”:{"$numberInt":“44”},“avg_low_tmp”:{"$numberInt":“28”},“icecream_cpi”:{"$numberDouble":“225.5”},“icecream_sales_in_millions”:{"$numberInt":“118”}},
{“month”:“March”,“avg_high_tmp”:{"$numberInt":“53”},“avg_low_tmp”:{"$numberInt":“35”},“icecream_cpi”:{"$numberDouble":“221.9”},“icecream_sales_in_millions”:{"$numberInt":“121”}},
{“month”:“April”,“avg_high_tmp”:{"$numberInt":“64”},“avg_low_tmp”:{"$numberInt":“44”},“icecream_cpi”:{"$numberDouble":“222.6”},“icecream_sales_in_millions”:{"$numberInt":“125”}},
{“month”:“May”,“avg_high_tmp”:{"$numberInt":“75”},“avg_low_tmp”:{"$numberInt":“54”},“icecream_cpi”:{"$numberDouble":“216.7”},“icecream_sales_in_millions”:{"$numberInt":“140”}},
{“month”:“June”,“avg_high_tmp”:{"$numberInt":“83”},“avg_low_tmp”:{"$numberInt":“63”},“icecream_cpi”:{"$numberDouble":“216.6”},“icecream_sales_in_millions”:{"$numberInt":“155”}},
{“month”:“July”,“avg_high_tmp”:{"$numberInt":“87”},“avg_low_tmp”:{"$numberInt":“68”},“icecream_cpi”:{"$numberDouble":“213.2”},“icecream_sales_in_millions”:{"$numberInt":“163”}},
{“month”:“August”,“avg_high_tmp”:{"$numberInt":“84”},“avg_low_tmp”:{"$numberInt":“66”},“icecream_cpi”:{"$numberDouble":“215.9”},“icecream_sales_in_millions”:{"$numberInt":“157”}},
{“month”:“September”,“avg_high_tmp”:{"$numberInt":“78”},“avg_low_tmp”:{"$numberInt":“59”},“icecream_cpi”:{"$numberDouble":“217.4”},“icecream_sales_in_millions”:{"$numberInt":“140”}},
{“month”:“October”,“avg_high_tmp”:{"$numberInt":“67”},“avg_low_tmp”:{"$numberInt":“48”},“icecream_cpi”:{"$numberDouble":“218.7”},“icecream_sales_in_millions”:{"$numberInt":“128”}},
{“month”:“November”,“avg_high_tmp”:{"$numberInt":“55”},“avg_low_tmp”:{"$numberInt":“38”},“icecream_cpi”:{"$numberDouble":“220.3”},“icecream_sales_in_millions”:{"$numberInt":“122”}},
{“month”:“December”,“avg_high_tmp”:{"$numberInt":“45”},“avg_low_tmp”:{"$numberInt":“29”},“icecream_cpi”:{"$numberDouble":“227.7”},“icecream_sales_in_millions”:{"$numberInt":“117”}}
]
}

Hi @Gianfranco_15573,

Let me check the query and get back to you on this.

Thanks,
Sonali

Hi @Gianfranco_15573,

Apologies for late reply.

Your Mongodb Aggregation Pipeline looks good to me.
By running your pipeline, I get that you wanted to fetch all those documents where icecream_sales_in_millions is greater than the average of the same field for all months.

I hope it is right.
Please let me know, if you have any questions.

Thanks,
Sonali

And can you also format your code using code tags. It’s difficult to read as it is.

1 Like