I am new to mongo and I’ve got a query that I am running as seen below:
db.getCollection('equityprice_input').aggregate([
{'$match': {'mrsBusinessDate': '2022-05-05', 'instrument': 'other', 'sourceSystem': 'bloomberg', 'mrsTime': '17:00:00', 'dataType': 'price'}},
{'$lookup': {'from': 'equityprice_input', 'localField': 'data.securities', 'foreignField': 'data.securities', 'as': 'staticData'}},
{'$project': {'_id': 1, 'mrsBusinessDate': 1, 'mrsTime': 1, 'category': 1, 'instrument': 1, 'label': 1, 'sourceSystem': 1, 'mrsDescription': 1, 'data': 1, 'staticData.data': 1, 'staticData.dataType': 1}},
{'$unwind': '$staticData'},
{'$match': {'staticData.dataType': 'static'}},
{'$project': {'_id': 1, 'mrsBusinessDate': 1, 'mrsTime': 1, 'category': 1, 'instrument': 1, 'label': 1, 'sourceSystem': 1, 'mrsDescription': 1, 'data': 1, 'staticData.data': 1}}
])
The above query returns data that has 'staticData.dataType': 'static'
and does not return if an entry does not. I need it to return an empty array when there is no 'staticData.dataType': 'static'
.
I’ve tried a few things, but my limited knowledge of mongo makes it hard to know where I am going wrong. Hoping that someone can assist.
Output looks as below when there is 'staticData.dataType': 'static'
:
What I want when there is no 'staticData.dataType': 'static'
:
2
Answers
You can put a
.toArray()
at the end of your whole aggregation to return that.https://www.mongodb.com/docs/manual/reference/method/cursor.toArray/
If I understand your logic and requirements correctly, you can get your desired output by replacing the last
"$match"
with a conditional"$set"
. Here’s the full query.A toy mongoplayground.net example demonstrates the idea.
Or if you want
"staticData": []
instead of"staticData": {"data": []}
, replace the"$set"
with: