I need to do a mongo lookup on data, but want an empty array where no data is found-CodePudding

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':

CodePudding user response：

You can put a .toArray() at the end of your whole aggregation to return that.

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}}
]).toArray()

https://www.mongodb.com/docs/manual/reference/method/cursor.toArray/

CodePudding user response：

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.

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'}, 
{"$set": {"staticData.data": {"$cond": [ {"$eq": ["$staticData.dataType", "static"]}, "$staticData.data", [] ]}}},        
{'$project': {'_id': 1, 'mrsBusinessDate': 1, 'mrsTime': 1, 'category': 1, 'instrument': 1, 'label': 1, 'sourceSystem': 1, 'mrsDescription': 1, 'data': 1, 'staticData.data': 1}}
])

A toy mongoplayground.net example demonstrates the idea.

Or if you want "staticData": [] instead of "staticData": {"data": []}, replace the "$set" with:

{"$set": {"staticData": {"$cond": [ {"$eq": ["$staticData.dataType", "static"]}, "$staticData", [] ]}}},