Find deeply nested data in MongoDB

Hi!

My current aim is to find all securities that have Source = Manual on a security field (e.g. Instrument_Name, Instrument_InstrumentType) this means a particular field has been manually updated by user. The problem comes from the fact that there are lots of Sources ( e.g. GSMApi, OpenFigi) and lots of security fields on each of the sources. I am trying to narrow this down, but cannot seem to find a proper query to do so. Also I’m not exactly good with queries on MongoDB and anything I’ve tried so far is either 0 results fetched or an error. I believe these are not arrays, but rather nested documents. I have included current data structure in a screenshot. Also below fetches single result by using full path -

db.getCollection(‘security-data’).find({“SourceFields.RemotePlus.Instrument_Issue_Issuer_Name.Source”: ‘Manual’})

However RemotePlus.Instrument_Issue_Issuer_Name is not a constant, the manual update can happen anywhere, on any source and any of its field (even multiple) this is where I’m stuck.

below is current data structure -

Thanks!

Hi @Ed_Motor - Welcome to the community :wave:

However RemotePlus.Instrument_Issue_Issuer_Name is not a constant, the manual update can happen anywhere, on any source and any of its field (even multiple) this is where I’m stuck.

Can you provide the following information:

  1. MongoDB version in use.
  2. 4-5 sample document(s) including a mix of where RemotePlus.Instrument_Issue_Issuer_Name varies in different level fields.
  3. Expected output of the 4-5 sample documents provided.

Regards,
Jason

Hi @Jason_Tran,
thanks for reply!

Sorry for late response.

  1. I think it is - “name”:“MongoDB Internal Client”,“version”:“4.2.6-18-g6cdb6ab” .
  2. From above what I meant is that - “SourceFields.<source>.<field_name>.Source”: ‘Manual’, where <source> and <field_name> would be variables, rest will always stay the same in regards to levels. So I would like to filter out all records(securities) which would have <field_name>.Source”: ‘Manual’.

Please let me know if that is enough info.

Regards,
Ed

1 Like

Hi @Ed_Motor - Thanks for getting back to me with that information.

  1. From above what I meant is that - “SourceFields.< source >.< field_name >.Source”: ‘Manual’, where < source > and < field_name > would be variables, rest will always stay the same in regards to levels. So I would like to filter out all records(securities) which would have <field_name>.Source”: ‘Manual’.

Specific to the above, would you be able to provide 4-5 sample documents and the expected / desired output?

I can load this into my test environment and attempt to see if what you are expecting is possible. I have some ideas at the moment based off the description but sample documents and expected output would help verify these.

Regards,
Jason

1 Like

Hi @Jason_Tran!

I’m using Robo 3T to navigate myself around. What would be the best way to provide sample documents? As for expected/desired output, as long as it’s at least top level I can go from there, meaning we just find all documents where criteria is met, records doesn’t have to be expanded, etc -

Regards,
Ed

Hi @Ed_Motor,

I’m using Robo 3T to navigate myself around. What would be the best way to provide sample documents?

I’m not too familiar with Robo3T but perhaps you could try export some documents using MongoDB Compass by following the procedure noted here and send a few sample document(s) once exported as JSON here.

Please redact any personal or sensitive data before posting the sample document(s) here.

Regards,
Jason

Ok, having some problems connecting with MongoDB Compass | MongoDB I remember it used to work just recently…

An error occurred while loading navigation: Unsupported OP_QUERY command: connectionStatus. The client driver may require an upgrade. For more details see https://dochub.mongodb.org/core/legacy-opcode-removal

I’ve extracted one document using below approach with Robo 3T.

Unfortunately I’m not allowed to add any attachments as a new user to verify if that is good enough. :pensive:

I’ve extracted one document using below approach with Robo 3T.

That should be a good start. Are you able to paste it as text after copying the JSON (based off your screenshot)? I am not sure what the output would be from that from Robo3T as I have not used it before unfortunately.

Example:

{
"name":"Jason",
"userId": 123456,
"Location": "Unknown"
}

Ok, here is an example doc, please give it a try.

{
    "_id" : NUUID("c26442ad-a8df-4095-8086-405994222c53"),
    "SecurityAggregateVersion" : NumberLong(4),
    "MarketDataAggregateVersion" : NumberLong(1),
    "Fields" : {
        "Issuer_Fundamentals_CountryOfRisk_Iso3166Alpha2" : {
            "_t" : "StringSecurityDataField",
            "LastUpdate" : "16620353903630469",
            "EffectiveDateTime" : "2022-09-01T12:29:45.8112946",
            "AssetType" : -1,
            "Source" : "ABCD",
            "BillingCategory" : "Test Billing",
            "BillingInfo" : [ 
                [ 
                    "master_information.instrument_master.trex_asset_type", 
                    "20"
                ], 
                [ 
                    "equity.equity_details.ADR_structure", 
                    "## ERROR: Field not returned from BICE ##"
                ], 
                [ 
                    "master_information.organization_master.org_country_code", 
                    "AT"
                ], 
                [ 
                    "debt.payment_schedule.pool_factor", 
                    null
                ], 
                [ 
                    "debt.cmo_details.tranche_number", 
                    null
                ], 
                [ 
                    "debt.fixed_income.debt_type", 
                    "## ERROR: Field not returned from BICE ##"
                ]
            ],
            "Value" : "AT678"
        },
        "Issuer_Fundamentals_Name" : {
            "_t" : "StringSecurityDataField",
            "LastUpdate" : "16620353903630469",
            "EffectiveDateTime" : "2022-09-01T12:29:45.8112946",
            "AssetType" : -1,
            "Source" : "ABCD",
            "BillingCategory" : "Test Billing",
            "BillingInfo" : [ 
                [ 
                    "master_information.instrument_master.trex_asset_type", 
                    "20"
                ], 
                [ 
                    "equity.equity_details.ADR_structure", 
                    "## ERROR: Field not returned from BICE ##"
                ], 
                [ 
                    "master_information.organization_master.org_country_code", 
                    "AT"
                ], 
                [ 
                    "debt.payment_schedule.pool_factor", 
                    null
                ], 
                [ 
                    "debt.cmo_details.tranche_number", 
                    null
                ], 
                [ 
                    "debt.fixed_income.debt_type", 
                    "## ERROR: Field not returned from BICE ##"
                ]
            ],
            "Value" : "Centrobank AG"
        }
    },
    "SourceFields" : {
        "ABCD" : {
            "Instrument_Issue_Issuer_IdcIssuer" : {
                "_t" : "StringSecurityDataField",
                "LastUpdate" : "16620353903726245",
                "EffectiveDateTime" : "2022-09-01T12:29:45.8112946",
                "AssetType" : -1,
                "Source" : "ABCD",
                "BillingCategory" : "Test Billing",
                "BillingInfo" : [ 
                    [ 
                        "master_information.instrument_master.trex_asset_type", 
                        "20"
                    ], 
                    [ 
                        "equity.equity_details.ADR_structure", 
                        "## ERROR: Field not returned from BICE ##"
                    ], 
                    [ 
                        "master_information.organization_master.org_country_code", 
                        "AT"
                    ], 
                    [ 
                        "debt.payment_schedule.pool_factor", 
                        null
                    ], 
                    [ 
                        "debt.cmo_details.tranche_number", 
                        null
                    ], 
                    [ 
                        "debt.fixed_income.debt_type", 
                        "## ERROR: Field not returned from BICE ##"
                    ]
                ],
                "Value" : "CENHAN"
            },
            "Instrument_Issue_IssueCurrency_Iso4217Alpha3" : {
                "_t" : "StringSecurityDataField",
                "LastUpdate" : "16620353903726245",
                "EffectiveDateTime" : "2022-09-01T12:29:45.8112946",
                "AssetType" : -1,
                "Source" : "ABCD",
                "BillingCategory" : "Test Billing",
                "BillingInfo" : [ 
                    [ 
                        "master_information.instrument_master.trex_asset_type", 
                        "20"
                    ], 
                    [ 
                        "equity.equity_details.ADR_structure", 
                        "## ERROR: Field not returned from BICE ##"
                    ], 
                    [ 
                        "master_information.organization_master.org_country_code", 
                        "AT"
                    ], 
                    [ 
                        "debt.payment_schedule.pool_factor", 
                        null
                    ], 
                    [ 
                        "debt.cmo_details.tranche_number", 
                        null
                    ], 
                    [ 
                        "debt.fixed_income.debt_type", 
                        "## ERROR: Field not returned from BICE ##"
                    ]
                ],
                "Value" : "EUR"
            },
            "Instrument_Debt_CouponSchedule" : {
                "_t" : "ArraySecurityDataField",
                "LastUpdate" : "16620388323222898",
                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                "AssetType" : 0,
                "Source" : "Manual",
                "BillingCategory" : "Manual Entry",
                "OverrideUntil" : "2021-01-14T14:18:43.13",
                "Value" : [ 
                    {
                        "_t" : "RecordSecurityDataField",
                        "LastUpdate" : "16620388323222898",
                        "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                        "AssetType" : 0,
                        "Source" : "Manual",
                        "OverrideUntil" : "2021-01-14T14:18:43.13",
                        "Value" : {
                            "Price" : {
                                "_t" : "DecimalSecurityDataField",
                                "LastUpdate" : "16620388323222898",
                                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                                "AssetType" : 0,
                                "Source" : "Manual",
                                "OverrideUntil" : "2021-01-14T00:00:00",
                                "Value" : "77777.0"
                            },
                            "IsPercentage" : {
                                "_t" : "BooleanSecurityDataField",
                                "LastUpdate" : "16620388323222898",
                                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                                "AssetType" : 0,
                                "Source" : "Manual",
                                "OverrideUntil" : "2021-01-14T00:00:00",
                                "Value" : false
                            },
                            "Date" : {
                                "_t" : "DateSecurityDataField",
                                "LastUpdate" : "16620388323222898",
                                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                                "AssetType" : 0,
                                "Source" : "Manual",
                                "OverrideUntil" : "2021-01-14T00:00:00",
                                "Value" : "2020-01-01"
                            }
                        }
                    }
                ]
            }
        },
        "OpenFigi" : {
            "Instrument_Naming_ShortName" : {
                "_t" : "StringSecurityDataField",
                "LastUpdate" : "16620353905711796",
                "EffectiveDateTime" : "2022-09-01T12:29:46.2499029",
                "AssetType" : -1,
                "Source" : "OpenFigi",
                "BillingCategory" : "OpenFigi",
                "BillingInfo" : [],
                "Value" : "TEST 0 PERP"
            },
            "Instrument_Classification_BloombergSecurityType" : {
                "_t" : "StringSecurityDataField",
                "LastUpdate" : "16620353905711796",
                "EffectiveDateTime" : "2022-09-01T12:29:46.2499029",
                "AssetType" : -1,
                "Source" : "OpenFigi",
                "BillingCategory" : "OpenFigi",
                "BillingInfo" : [],
                "Value" : "EURO-ZONE"
            },
            "Instrument_PriceDivisor" : {
                "_t" : "DecimalSecurityDataField",
                "LastUpdate" : "16620369913791479",
                "EffectiveDateTime" : "2022-09-01T10:18:43.13",
                "AssetType" : 0,
                "Source" : "Manual",
                "BillingCategory" : "Test Billing",
                "BillingInfo" : [ 
                    [ 
                        "master_information.instrument_master.trex_asset_type", 
                        "20"
                    ], 
                    [ 
                        "equity.equity_details.ADR_structure", 
                        "## ERROR: Field not returned from BICE ##"
                    ], 
                    [ 
                        "master_information.organization_master.org_country_code", 
                        "AT"
                    ], 
                    [ 
                        "debt.payment_schedule.pool_factor", 
                        null
                    ], 
                    [ 
                        "debt.cmo_details.tranche_number", 
                        null
                    ], 
                    [ 
                        "debt.fixed_income.debt_type", 
                        "## ERROR: Field not returned from BICE ##"
                    ]
                ],
                "OverrideUntil" : "2021-09-30T14:18:43.13",
                "Value" : "101.0"
            },
            "Instrument_Debt_CouponSchedule" : {
                "_t" : "ArraySecurityDataField",
                "LastUpdate" : "16620388323222898",
                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                "AssetType" : 0,
                "Source" : "Manual",
                "BillingCategory" : "Manual Entry",
                "OverrideUntil" : "2021-01-14T14:18:43.13",
                "Value" : [ 
                    {
                        "_t" : "RecordSecurityDataField",
                        "LastUpdate" : "16620388323222898",
                        "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                        "AssetType" : 0,
                        "Source" : "Manual",
                        "OverrideUntil" : "2021-01-14T14:18:43.13",
                        "Value" : {
                            "Price" : {
                                "_t" : "DecimalSecurityDataField",
                                "LastUpdate" : "16620388323222898",
                                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                                "AssetType" : 0,
                                "Source" : "Manual",
                                "OverrideUntil" : "2021-01-14T00:00:00",
                                "Value" : "77777.0"
                            },
                            "IsPercentage" : {
                                "_t" : "BooleanSecurityDataField",
                                "LastUpdate" : "16620388323222898",
                                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                                "AssetType" : 0,
                                "Source" : "Manual",
                                "OverrideUntil" : "2021-01-14T00:00:00",
                                "Value" : false
                            },
                            "Date" : {
                                "_t" : "DateSecurityDataField",
                                "LastUpdate" : "16620388323222898",
                                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                                "AssetType" : 0,
                                "Source" : "Manual",
                                "OverrideUntil" : "2021-01-14T00:00:00",
                                "Value" : "2020-01-01"
                            }
                        }
                    }
                ]
            }
        },
        "RemotePlus" : {
            "Instrument_MarketData_DailyVolume" : {
                "_t" : "DecimalSecurityDataField",
                "LastUpdate" : "16620353906311889",
                "EffectiveDateTime" : "2022-09-01T00:00:00",
                "AssetType" : -1,
                "Source" : "RemotePlus",
                "BillingCategory" : "Test Billing2",
                "BillingInfo" : [ 
                    [ 
                        "trexASSET", 
                        "20"
                    ], 
                    [ 
                        "TYP1", 
                        null
                    ], 
                    [ 
                        "ETDTYPE", 
                        null
                    ], 
                    [ 
                        "ATYPE", 
                        "30"
                    ], 
                    [ 
                        "COQ", 
                        "AT"
                    ]
                ],
                "Value" : "294000.0"
            },
            "Instrument_MarketData_LastPrice" : {
                "_t" : "DecimalSecurityDataField",
                "LastUpdate" : "16620353906311889",
                "EffectiveDateTime" : "2022-09-01T00:00:00",
                "AssetType" : -1,
                "Source" : "RemotePlus",
                "BillingCategory" : "Test Billing2",
                "BillingInfo" : [ 
                    [ 
                        "trexASSET", 
                        "20"
                    ], 
                    [ 
                        "TYP1", 
                        null
                    ], 
                    [ 
                        "ETDTYPE", 
                        null
                    ], 
                    [ 
                        "ATYPE", 
                        "30"
                    ], 
                    [ 
                        "COQ", 
                        "AT"
                    ]
                ],
                "Value" : "0.309"
            },
            "Instrument_PriceDivisor" : {
                "_t" : "DecimalSecurityDataField",
                "LastUpdate" : "16620369913791479",
                "EffectiveDateTime" : "2022-09-01T10:18:43.13",
                "AssetType" : 0,
                "Source" : "Manual",
                "BillingCategory" : "Test Billing",
                "BillingInfo" : [ 
                    [ 
                        "master_information.instrument_master.trex_asset_type", 
                        "20"
                    ], 
                    [ 
                        "equity.equity_details.ADR_structure", 
                        "## ERROR: Field not returned from BICE ##"
                    ], 
                    [ 
                        "master_information.organization_master.org_country_code", 
                        "AT"
                    ], 
                    [ 
                        "debt.payment_schedule.pool_factor", 
                        null
                    ], 
                    [ 
                        "debt.cmo_details.tranche_number", 
                        null
                    ], 
                    [ 
                        "debt.fixed_income.debt_type", 
                        "## ERROR: Field not returned from BICE ##"
                    ]
                ],
                "OverrideUntil" : "2021-09-30T14:18:43.13",
                "Value" : "101.0"
            },
            "Instrument_Debt_CouponSchedule" : {
                "_t" : "ArraySecurityDataField",
                "LastUpdate" : "16620388323222898",
                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                "AssetType" : 0,
                "Source" : "Manual",
                "BillingCategory" : "Manual Entry",
                "OverrideUntil" : "2021-01-14T14:18:43.13",
                "Value" : [ 
                    {
                        "_t" : "RecordSecurityDataField",
                        "LastUpdate" : "16620388323222898",
                        "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                        "AssetType" : 0,
                        "Source" : "Manual",
                        "OverrideUntil" : "2021-01-14T14:18:43.13",
                        "Value" : {
                            "PrBICE" : {
                                "_t" : "DecimalSecurityDataField",
                                "LastUpdate" : "16620388323222898",
                                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                                "AssetType" : 0,
                                "Source" : "Manual",
                                "OverrideUntil" : "2021-01-14T00:00:00",
                                "Value" : "77777.0"
                            },
                            "IsPercentage" : {
                                "_t" : "BooleanSecurityDataField",
                                "LastUpdate" : "16620388323222898",
                                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                                "AssetType" : 0,
                                "Source" : "Manual",
                                "OverrideUntil" : "2021-01-14T00:00:00",
                                "Value" : false
                            },
                            "Date" : {
                                "_t" : "DateSecurityDataField",
                                "LastUpdate" : "16620388323222898",
                                "EffectiveDateTime" : "2021-01-12T15:18:43.13",
                                "AssetType" : 0,
                                "Source" : "Manual",
                                "OverrideUntil" : "2021-01-14T00:00:00",
                                "Value" : "2020-01-01"
                            }
                        }
                    }
                ]
            }
        }
    }
}

Thanks @Ed_Motor,

I’m not too sure what you’re expecting as an output but I have done some testing to obtain the below output. From the sample document you provided, I can see there are 5 instances where "SourceFields.<variable1>.<variable2>.Soruce" have a value of “Manual”. Is that correct? If so, here is the output from my testing:

[
  {
    _id: ObjectId("63116f45413bc94d7e966242"),
    groupedFilteredArray: [
      {
        Instrument_Debt_CouponSchedule: {
          _t: 'ArraySecurityDataField',
          LastUpdate: '16620388323222898',
          EffectiveDateTime: '2021-01-12T15:18:43.13',
          AssetType: 0,
          Source: 'Manual',
          BillingCategory: 'Manual Entry',
          OverrideUntil: '2021-01-14T14:18:43.13',
          Value: [
            {
              _t: 'RecordSecurityDataField',
              LastUpdate: '16620388323222898',
              EffectiveDateTime: '2021-01-12T15:18:43.13',
              AssetType: 0,
              Source: 'Manual',
              OverrideUntil: '2021-01-14T14:18:43.13',
              Value: {
                Price: {
                  _t: 'DecimalSecurityDataField',
                  LastUpdate: '16620388323222898',
                  EffectiveDateTime: '2021-01-12T15:18:43.13',
                  AssetType: 0,
                  Source: 'Manual',
                  OverrideUntil: '2021-01-14T00:00:00',
                  Value: '77777.0'
                },
                IsPercentage: {
                  _t: 'BooleanSecurityDataField',
                  LastUpdate: '16620388323222898',
                  EffectiveDateTime: '2021-01-12T15:18:43.13',
                  AssetType: 0,
                  Source: 'Manual',
                  OverrideUntil: '2021-01-14T00:00:00',
                  Value: false
                },
                Date: {
                  _t: 'DateSecurityDataField',
                  LastUpdate: '16620388323222898',
                  EffectiveDateTime: '2021-01-12T15:18:43.13',
                  AssetType: 0,
                  Source: 'Manual',
                  OverrideUntil: '2021-01-14T00:00:00',
                  Value: '2020-01-01'
                }
              }
            }
          ]
        }
      },
      {
        Instrument_PriceDivisor: {
          _t: 'DecimalSecurityDataField',
          LastUpdate: '16620369913791479',
          EffectiveDateTime: '2022-09-01T10:18:43.13',
          AssetType: 0,
          Source: 'Manual',
          BillingCategory: 'Test Billing',
          BillingInfo: [
            [
              'master_information.instrument_master.trex_asset_type',
              '20'
            ],
            [
              'equity.equity_details.ADR_structure',
              '## ERROR: Field not returned from BICE ##'
            ],
            [
              'master_information.organization_master.org_country_code',
              'AT'
            ],
            [ 'debt.payment_schedule.pool_factor', null ],
            [ 'debt.cmo_details.tranche_number', null ],
            [
              'debt.fixed_income.debt_type',
              '## ERROR: Field not returned from BICE ##'
            ]
          ],
          OverrideUntil: '2021-09-30T14:18:43.13',
          Value: '101.0'
        },
        Instrument_Debt_CouponSchedule: {
          _t: 'ArraySecurityDataField',
          LastUpdate: '16620388323222898',
          EffectiveDateTime: '2021-01-12T15:18:43.13',
          AssetType: 0,
          Source: 'Manual',
          BillingCategory: 'Manual Entry',
          OverrideUntil: '2021-01-14T14:18:43.13',
          Value: [
            {
              _t: 'RecordSecurityDataField',
              LastUpdate: '16620388323222898',
              EffectiveDateTime: '2021-01-12T15:18:43.13',
              AssetType: 0,
              Source: 'Manual',
              OverrideUntil: '2021-01-14T14:18:43.13',
              Value: {
                Price: {
                  _t: 'DecimalSecurityDataField',
                  LastUpdate: '16620388323222898',
                  EffectiveDateTime: '2021-01-12T15:18:43.13',
                  AssetType: 0,
                  Source: 'Manual',
                  OverrideUntil: '2021-01-14T00:00:00',
                  Value: '77777.0'
                },
                IsPercentage: {
                  _t: 'BooleanSecurityDataField',
                  LastUpdate: '16620388323222898',
                  EffectiveDateTime: '2021-01-12T15:18:43.13',
                  AssetType: 0,
                  Source: 'Manual',
                  OverrideUntil: '2021-01-14T00:00:00',
                  Value: false
                },
                Date: {
                  _t: 'DateSecurityDataField',
                  LastUpdate: '16620388323222898',
                  EffectiveDateTime: '2021-01-12T15:18:43.13',
                  AssetType: 0,
                  Source: 'Manual',
                  OverrideUntil: '2021-01-14T00:00:00',
                  Value: '2020-01-01'
                }
              }
            }
          ]
        }
      },
      {
        Instrument_PriceDivisor: {
          _t: 'DecimalSecurityDataField',
          LastUpdate: '16620369913791479',
          EffectiveDateTime: '2022-09-01T10:18:43.13',
          AssetType: 0,
          Source: 'Manual',
          BillingCategory: 'Test Billing',
          BillingInfo: [
            [
              'master_information.instrument_master.trex_asset_type',
              '20'
            ],
            [
              'equity.equity_details.ADR_structure',
              '## ERROR: Field not returned from BICE ##'
            ],
            [
              'master_information.organization_master.org_country_code',
              'AT'
            ],
            [ 'debt.payment_schedule.pool_factor', null ],
            [ 'debt.cmo_details.tranche_number', null ],
            [
              'debt.fixed_income.debt_type',
              '## ERROR: Field not returned from BICE ##'
            ]
          ],
          OverrideUntil: '2021-09-30T14:18:43.13',
          Value: '101.0'
        },
        Instrument_Debt_CouponSchedule: {
          _t: 'ArraySecurityDataField',
          LastUpdate: '16620388323222898',
          EffectiveDateTime: '2021-01-12T15:18:43.13',
          AssetType: 0,
          Source: 'Manual',
          BillingCategory: 'Manual Entry',
          OverrideUntil: '2021-01-14T14:18:43.13',
          Value: [
            {
              _t: 'RecordSecurityDataField',
              LastUpdate: '16620388323222898',
              EffectiveDateTime: '2021-01-12T15:18:43.13',
              AssetType: 0,
              Source: 'Manual',
              OverrideUntil: '2021-01-14T14:18:43.13',
              Value: {
                PrBICE: {
                  _t: 'DecimalSecurityDataField',
                  LastUpdate: '16620388323222898',
                  EffectiveDateTime: '2021-01-12T15:18:43.13',
                  AssetType: 0,
                  Source: 'Manual',
                  OverrideUntil: '2021-01-14T00:00:00',
                  Value: '77777.0'
                },
                IsPercentage: {
                  _t: 'BooleanSecurityDataField',
                  LastUpdate: '16620388323222898',
                  EffectiveDateTime: '2021-01-12T15:18:43.13',
                  AssetType: 0,
                  Source: 'Manual',
                  OverrideUntil: '2021-01-14T00:00:00',
                  Value: false
                },
                Date: {
                  _t: 'DateSecurityDataField',
                  LastUpdate: '16620388323222898',
                  EffectiveDateTime: '2021-01-12T15:18:43.13',
                  AssetType: 0,
                  Source: 'Manual',
                  OverrideUntil: '2021-01-14T00:00:00',
                  Value: '2020-01-01'
                }
              }
            }
          ]
        }
      }
    ]
  }
]

The groupFilteredArray field contains 3 documents inside. However, it has 5 instances where "SourceFields.<variable1>.<variable2>.Soruce" have a value of “Manual”. From what I had counted from the original sample document, these were the paths where each instance existed:

  • 1 instance within the "SourceFields.ABCD" object
  • 2 instances within the "SourceFields.OpenFigi" object
  • 2 instances within the "SourceFields.RemotePlus" object

Note: The above output does not include the Original Source name / details (i.e. OpenFigi, ABCD, RemotePlus)

  1. From above what I meant is that - “SourceFields.< source >.< field_name >.Source”: ‘Manual’, where < source > and < field_name > would be variables, rest will always stay the same in regards to levels. So I would like to filter out all records(securities) which would have <field_name>.Source”: ‘Manual’.

I have assumed that there is 2 layers of “variables” for each of the input documents.

Would the example output based off my testing include enough information to demonstrate all records (or “securities”), where “SourceFields.< *source* >.< *field_name* >.Source”: ‘Manual’ for your use case?

I will also add, the way this is done is extremely process heavy. If possible, it may be best to retrieve the original documents and then filter them out from the application side. This operation i’ve performed may suit your use case if performed on a minimal set of document(s) (depending on your infrastructure / environment as well) in which you may not encounter resource pressure or long operation times. If you plan to run this as a “once in a while” situation, then it may work too assuming you are okay with the resource overhead.

Have you considered re-designing the schema as I noticed there is more "Source" fields that have "Manual" as the value deeper in the same document (4th, 5th layers, etc.) and especially if you plan to run this filtering very frequently.

Example aggregation for the above output:

db.collection.aggregate([
{
  "$addFields": {
    "array": {
      "$objectToArray": "$SourceFields"
    }
  }
},
{
  "$addFields": {
    "array2": {
      "$map": {
        "input": "$array",
        "in": {
          "$objectToArray": "$$this.v"
        }
      }
    }
  }
},
{ "$project": { "array2": 1 } },
{ "$unwind": "$array2" },
{
  "$project": {
    "filteredArray": {
      "$filter": {
        "input": "$array2",
        "cond": {
          "$eq": [
            "$$this.v.Source",
            "Manual"
          ]
        }
      }
    }
  }
},
{"$project":{"reconverted":{"$arrayToObject":"$filteredArray"}}},
{
  "$group": {
    "_id": "$_id",
    "groupedFilteredArray": {
      "$push": "$reconverted"
    }
  }
}
])

Some of the aggregation stages / operators used in the above for you reference:

Very important to note I have only tested this a couple of times and on the single sample document provided. Perhaps other community members may be able to chime in with more suitable alternatives to the above example. I have used $project eventually in my testing due to the resulting document after each stage becoming too large to work with visually. You can adjust accordingly if you feel this works for your use case(s). It is highly recommend to test on a test environment to verify it suits all your use case(s) and requirements before trying in production. However, I would recommend going over the following documentation regarding schema design:

Regards,
Jason

3 Likes

@Jason_Tran thank you for reply/output provided!

What you have provided I believe is good enough for me, because as long as I get:

ObjectId("63116f45413bc94d7e966242")

I can then use these ids and fetch securities in question using API calls. It’s also very good that I can immediately view fields with “Manual”, even though I don’t see the top level, that’s not an issue at all.

The 5 instances is exactly how many I would expect to be in the output in this case.

As for process heavy I think it should be fine. Environment is spinning on AWS and there are plenty of resources and really this is more of a unique situation where I suspect some data may be causing time-outs, because it was manually amended and it is affecting only particular set of securities and only staging environment. So indeed it will be “once in a while” situation.

In regards to re-design I can bring this up with developers as I’m more on end-to-end testing side of things. :slight_smile:

Interestingly I did try your provided query on my local instance with 6 sample documents and one of them having this “Manual” source, but got Fetched 0 record(s) in 0ms - is there anything I need to adjust in the query or it can be run as is?

Regards,
Ed

Glad to hear those details are sufficient for now and thanks for providing further context regarding the environment.

For the above sample document(s) statement, I will send a DM :slight_smile:

Regards,
Jason

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.