python - MongoDB 在没有 $unwind 的情况下获得 $avg 的嵌套数组

Question

我正在将 MongoDB 与 NoSQL 解决方案进行性能比较。我正在将 100GB 数据集上的查询与多个嵌套数组进行比较。

由于嵌套，MongoDB 上的查询运行有点慢（项目、展开、项目、展开、组）。那么有什么方法可以在这里更有效地获得嵌套数组的平均值吗？

这是数据，查询如下：

格式良好的 JSON 图像：

和一份样本文件

{"_id":{"$oid":"5ecfeb2de01ea0eed23cf087"},"metadata":{"data_version":"4","match_id":"EUW1_4592599338","participants":["BfQY8t_srYunpZfkaaPUbVCTd4NT57Y1JzKPvpOcAYnbtKz-xTqsW0Vr8KPrp1_Y5L-zkYX0ysHQoA","kwZi-teckO1QSYPs68rtOCCLcOGQ764Lw482239etadYwA18U8g4FnTi4F04GsFNVRQsyFjjHpBPSQ","TUjub4oDDyHScn2-xooOCoB5L_BUaBXP4q2UaP_LzXwM42VMxlwY5ja4a7k2wD91YKTqFMhqaykKkA","i5poFaSv47Z7zkMLWHCtUOYbEH59vrNk9bo7OzqezRtt3ZVuXFL_r8rAkszNBH27OpKddkbdct16Cw","IQ5h32krdx4xHQtmEzzQ5zVYtcq298rJ9V44UmnYX9c1FW9r5T2RCQ_m-HrgXY5x8EfuZb8-4QjoNg","xr-JPWAA_v05ml-VO8L2Edp1UWLx-DHsx6jgK494F-GJ86iszZYw2vp4IlWZK_UK63nipijZBExpUQ","Ee7k_6uF9N-VMNxF7WJmnUnWIYVNSUGpqGrAwRsOEDrUAZo4Hxre_kzv6dA2to9orl7VUJWSpa1owA","X1NTCdrYLJrC5P1QlJeyROHLqv0D7D7lyZ-iETP31DYnKZZrl_EO1YD77whlcb_JakNyZMDjT3yvYw"]},"info":{"game_datetime":{"$numberLong":"1589204753972"},"game_length":2227.107666015625,"game_variation":"TFT3_GameVariation_MidGameFoN","game_version":"Version 10.9.318.6057 (Apr 23 2020/18:02:32) [PUBLIC] <Releases/10.9>","participants":[{"companion":{"content_ID":"40d843f3-7441-4254-b70a-8b3369b5325b","skin_ID":13,"species":"PetGhosty"},"gold_left":11,"last_round":40,"level":9,"placement":1,"players_eliminated":3,"puuid":"BfQY8t_srYunpZfkaaPUbVCTd4NT57Y1JzKPvpOcAYnbtKz-xTqsW0Vr8KPrp1_Y5L-zkYX0ysHQoA","time_eliminated":2218.819580078125,"total_damage_to_players":204,"traits":[{"name":"Blaster","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"Chrono","num_units":6,"style":3,"tier_current":3,"tier_total":3},{"name":"Cybernetic","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"DarkStar","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"ManaReaver","num_units":1,"style":0,"tier_current":0,"tier_total":1},{"name":"Rebel","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Set3_Blademaster","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Set3_Brawler","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"Sniper","num_units":2,"style":3,"tier_current":1,"tier_total":1},{"name":"SpacePirate","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"StarGuardian","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"TemplateTrait","num_units":1,"style":0,"tier_current":0},{"name":"Vanguard","num_units":4,"style":3,"tier_current":2,"tier_total":2}],"units":[{"character_id":"TFT3_Caitlyn","items":[88,56],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_Poppy","items":[],"name":"","rarity":0,"tier":1},{"character_id":"TFT3_Leona","items":[99,16,49],"name":"","rarity":0,"tier":3},{"character_id":"TFT3_Blitzcrank","items":[],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_Shen","items":[58],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_Ezreal","items":[],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_Jayce","items":[45,37,47],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_WuKong","items":[99,36,36],"name":"","rarity":3,"tier":2},{"character_id":"TFT3_Jhin","items":[57,12,26],"name":"","rarity":3,"tier":2},{"character_id":"TFT3_Thresh","items":[],"name":"","rarity":4,"tier":1}]},{"companion":{"content_ID":"a6f8fb43-3803-4293-8697-c0e8963b4001","skin_ID":7,"species":"PetGemTiger"},"gold_left":5,"last_round":40,"level":9,"placement":2,"players_eliminated":1,"puuid":"kwZi-teckO1QSYPs68rtOCCLcOGQ764Lw482239etadYwA18U8g4FnTi4F04GsFNVRQsyFjjHpBPSQ","time_eliminated":2218.819580078125,"total_damage_to_players":153,"traits":[{"name":"Chrono","num_units":3,"style":1,"tier_current":1,"tier_total":3},{"name":"Cybernetic","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"DarkStar","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"ManaReaver","num_units":3,"style":3,"tier_current":1,"tier_total":1},{"name":"Protector","num_units":3,"style":1,"tier_current":1,"tier_total":3},{"name":"Set3_Blademaster","num_units":3,"style":1,"tier_current":1,"tier_total":3},{"name":"Set3_Celestial","num_units":5,"style":2,"tier_current":2,"tier_total":3},{"name":"Sniper","num_units":2,"style":3,"tier_current":1,"tier_total":1}],"units":[{"character_id":"TFT3_Caitlyn","items":[],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_JarvanIV","items":[],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_Xayah","items":[22,29],"name":"","rarity":0,"tier":3},{"character_id":"TFT3_Shen","items":[],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_Rakan","items":[37,5],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_XinZhao","items":[17,57,66],"name":"","rarity":1,"tier":3},{"character_id":"TFT3_Kassadin","items":[35],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_Ashe","items":[],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_Irelia","items":[56,16,19],"name":"","rarity":3,"tier":2},{"character_id":"TFT3_Thresh","items":[],"name":"","rarity":4,"tier":1}]},{"companion":{"content_ID":"b2200481-6c7e-49c9-b9fa-96136144c58d","skin_ID":1,"species":"PetQiyanaDog"},"gold_left":0,"last_round":28,"level":7,"placement":8,"players_eliminated":0,"puuid":"TUjub4oDDyHScn2-xooOCoB5L_BUaBXP4q2UaP_LzXwM42VMxlwY5ja4a7k2wD91YKTqFMhqaykKkA","time_eliminated":1595.7633056640625,"total_damage_to_players":45,"traits":[{"name":"Chrono","num_units":2,"style":1,"tier_current":1,"tier_total":3},{"name":"Cybernetic","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"Infiltrator","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Rebel","num_units":2,"style":0,"tier_current":0,"tier_total":3},{"name":"Set3_Brawler","num_units":4,"style":3,"tier_current":2,"tier_total":2},{"name":"Set3_Mystic","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"Set3_Sorcerer","num_units":2,"style":1,"tier_current":1,"tier_total":4},{"name":"Set3_Void","num_units":3,"style":3,"tier_current":1,"tier_total":1}],"units":[{"character_id":"TFT3_TwistedFate","items":[],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_Malphite","items":[56],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_KhaZix","items":[15],"name":"Khazix","rarity":0,"tier":2},{"character_id":"TFT3_Blitzcrank","items":[25],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_Sona","items":[88,6],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_Vi","items":[],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_ChoGath","items":[15],"name":"Chogath","rarity":3,"tier":1},{"character_id":"TFT3_VelKoz","items":[34,3],"name":"","rarity":3,"tier":1}]},{"companion":{"content_ID":"5897ad9f-4665-4372-8f3e-6c878adb8918","skin_ID":1,"species":"PetTFTAvatar"},"gold_left":10,"last_round":31,"level":7,"placement":5,"players_eliminated":0,"puuid":"i5poFaSv47Z7zkMLWHCtUOYbEH59vrNk9bo7OzqezRtt3ZVuXFL_r8rAkszNBH27OpKddkbdct16Cw","time_eliminated":1762.3421630859375,"total_damage_to_players":83,"traits":[{"name":"Chrono","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Cybernetic","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"Demolitionist","num_units":1,"style":0,"tier_current":0,"tier_total":1},{"name":"Infiltrator","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Rebel","num_units":3,"style":1,"tier_current":1,"tier_total":3},{"name":"Set3_Blademaster","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Set3_Brawler","num_units":4,"style":3,"tier_current":2,"tier_total":2},{"name":"Set3_Sorcerer","num_units":1,"style":0,"tier_current":0,"tier_total":4},{"name":"Set3_Void","num_units":3,"style":3,"tier_current":1,"tier_total":1}],"units":[{"character_id":"TFT3_Ziggs","items":[46,6],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_Malphite","items":[55,5],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_KhaZix","items":[15,11,14],"name":"Khazix","rarity":0,"tier":3},{"character_id":"TFT3_Blitzcrank","items":[88],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_MasterYi","items":[],"name":"","rarity":2,"tier":1},{"character_id":"TFT3_Vi","items":[],"name":"","rarity":2,"tier":1},{"character_id":"TFT3_ChoGath","items":[67],"name":"Chogath","rarity":3,"tier":2},{"character_id":"TFT3_VelKoz","items":[3],"name":"","rarity":3,"tier":1}]},{"companion":{"content_ID":"c6e980e3-9933-457b-9d60-d7b46b82dd1e","skin_ID":8,"species":"PetGriffin"},"gold_left":3,"last_round":34,"level":7,"placement":3,"players_eliminated":0,"puuid":"IQ5h32krdx4xHQtmEzzQ5zVYtcq298rJ9V44UmnYX9c1FW9r5T2RCQ_m-HrgXY5x8EfuZb8-4QjoNg","time_eliminated":1931.5399169921875,"total_damage_to_players":110,"traits":[{"name":"Chrono","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"DarkStar","num_units":4,"style":1,"tier_current":1,"tier_total":3},{"name":"Infiltrator","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Set3_Celestial","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Set3_Mystic","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"Sniper","num_units":2,"style":3,"tier_current":1,"tier_total":1},{"name":"SpacePirate","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"StarGuardian","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"Vanguard","num_units":4,"style":3,"tier_current":2,"tier_total":2}],"units":[{"character_id":"TFT3_Poppy","items":[7],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_Mordekaiser","items":[77,37],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_Shaco","items":[],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_Jayce","items":[11,39,14],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_Karma","items":[],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_Ashe","items":[2],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_WuKong","items":[],"name":"","rarity":3,"tier":1},{"character_id":"TFT3_Jhin","items":[19,26,1],"name":"","rarity":3,"tier":2}]},{"companion":{"content_ID":"d61df27b-9446-43be-b2a9-c64c28d6db71","skin_ID":13,"species":"PetSGCat"},"gold_left":2,"last_round":30,"level":8,"placement":6,"players_eliminated":1,"puuid":"xr-JPWAA_v05ml-VO8L2Edp1UWLx-DHsx6jgK494F-GJ86iszZYw2vp4IlWZK_UK63nipijZBExpUQ","time_eliminated":1705.921142578125,"total_damage_to_players":64,"traits":[{"name":"Cybernetic","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"DarkStar","num_units":2,"style":0,"tier_current":0,"tier_total":3},{"name":"Demolitionist","num_units":2,"style":3,"tier_current":1,"tier_total":1},{"name":"Infiltrator","num_units":4,"style":3,"tier_current":2,"tier_total":3},{"name":"MechPilot","num_units":3,"style":3,"tier_current":1,"tier_total":1},{"name":"Mercenary","num_units":1,"style":3,"tier_current":1,"tier_total":1},{"name":"Set3_Blademaster","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Set3_Celestial","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Set3_Mystic","num_units":2,"style":1,"tier_current":1,"tier_total":2},{"name":"Set3_Sorcerer","num_units":1,"style":0,"tier_current":0,"tier_total":4},{"name":"SpacePirate","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"Valkyrie","num_units":2,"style":3,"tier_current":1,"tier_total":1}],"units":[{"character_id":"TFT3_KaiSa","items":[],"name":"","rarity":1,"tier":1},{"character_id":"TFT3_Annie","items":[],"name":"","rarity":1,"tier":1},{"character_id":"TFT3_Shaco","items":[16,15],"name":"","rarity":2,"tier":1},{"character_id":"TFT3_Rumble","items":[55,66],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_Karma","items":[],"name":"","rarity":2,"tier":1},{"character_id":"TFT3_Kayle","items":[],"name":"","rarity":3,"tier":2},{"character_id":"TFT3_Fizz","items":[],"name":"","rarity":3,"tier":2},{"character_id":"TFT3_Gangplank","items":[14,45,15],"name":"","rarity":4,"tier":1},{"character_id":"TFT3_Ekko","items":[],"name":"","rarity":4,"tier":1},{"character_id":"TFT3_Lulu","items":[],"name":"","rarity":4,"tier":1}]},{"companion":{"content_ID":"2bba1e3b-6b2a-44a2-829c-fd563c74aab1","skin_ID":10,"species":"PetDSWhale"},"gold_left":0,"last_round":33,"level":7,"placement":4,"players_eliminated":2,"puuid":"Ee7k_6uF9N-VMNxF7WJmnUnWIYVNSUGpqGrAwRsOEDrUAZo4Hxre_kzv6dA2to9orl7VUJWSpa1owA","time_eliminated":1864.9774169921875,"total_damage_to_players":97,"traits":[{"name":"Chrono","num_units":2,"style":1,"tier_current":1,"tier_total":3},{"name":"Cybernetic","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"DarkStar","num_units":2,"style":0,"tier_current":0,"tier_total":3},{"name":"ManaReaver","num_units":1,"style":0,"tier_current":0,"tier_total":1},{"name":"Protector","num_units":2,"style":1,"tier_current":1,"tier_total":3},{"name":"Set3_Blademaster","num_units":3,"style":1,"tier_current":1,"tier_total":3},{"name":"Set3_Celestial","num_units":4,"style":2,"tier_current":2,"tier_total":3},{"name":"Sniper","num_units":2,"style":3,"tier_current":1,"tier_total":1}],"units":[{"character_id":"TFT3_Fiora","items":[88],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_Caitlyn","items":[5,89],"name":"","rarity":0,"tier":3},{"character_id":"TFT3_JarvanIV","items":[],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_Xayah","items":[12,19,19],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_Shen","items":[66],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_Rakan","items":[36],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_Kassadin","items":[],"name":"","rarity":2,"tier":3},{"character_id":"TFT3_Ashe","items":[34,14],"name":"","rarity":2,"tier":2}]},{"companion":{"content_ID":"270079d1-2d24-41c2-b6a1-34613304e16c","skin_ID":7,"species":"PetGhosty"},"gold_left":5,"last_round":30,"level":7,"placement":7,"players_eliminated":0,"puuid":"X1NTCdrYLJrC5P1QlJeyROHLqv0D7D7lyZ-iETP31DYnKZZrl_EO1YD77whlcb_JakNyZMDjT3yvYw","time_eliminated":1696.2069091796875,"total_damage_to_players":70,"traits":[{"name":"Chrono","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"DarkStar","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"ManaReaver","num_units":1,"style":0,"tier_current":0,"tier_total":1},{"name":"Protector","num_units":2,"style":1,"tier_current":1,"tier_total":3},{"name":"Rebel","num_units":1,"style":0,"tier_current":0,"tier_total":3},{"name":"Set3_Blademaster","num_units":3,"style":1,"tier_current":1,"tier_total":3},{"name":"Set3_Celestial","num_units":4,"style":2,"tier_current":2,"tier_total":3},{"name":"Sniper","num_units":1,"style":0,"tier_current":0,"tier_total":1},{"name":"StarGuardian","num_units":1,"style":0,"tier_current":0,"tier_total":2},{"name":"Vanguard","num_units":1,"style":0,"tier_current":0,"tier_total":2}],"units":[{"character_id":"TFT3_Poppy","items":[],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_JarvanIV","items":[],"name":"","rarity":0,"tier":2},{"character_id":"TFT3_Xayah","items":[33,29,19],"name":"","rarity":0,"tier":3},{"character_id":"TFT3_Shen","items":[99,24,33],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_Rakan","items":[],"name":"","rarity":1,"tier":2},{"character_id":"TFT3_Kassadin","items":[44],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_MasterYi","items":[77],"name":"","rarity":2,"tier":2},{"character_id":"TFT3_Ashe","items":[26],"name":"","rarity":2,"tier":2}]}],"queue_id":1100,"tft_set_number":3}}

以下是我在 Python 中使用的聚合。

result = client['tft']['matches'].aggregate([
    {
        '$project': {
            '_id': False, 
            'metadata.match_id': True, 
            'info.participants': True
        }
    }, {
        '$unwind': {
            'path': '$info.participants', 
            'preserveNullAndEmptyArrays': False
        }
    }, {
        '$project': {
            '_id': False, 
            'metadata.match_id': True, 
            'info.participants.puuid': True, 
            'info.participants.units': True, 
            'info.participants.placement': True
        }
    }, {
        '$unwind': {
            'path': '$info.participants.units', 
            'preserveNullAndEmptyArrays': False
        }
    }, {
        '$group': {
            '_id': {
                'unitName': '$info.participants.units.name', 
                'unitTier': '$info.participants.units.tier'
            }, 
            'avgPosition': {
                '$avg': '$info.participants.placement'
            }
        }
    }, {
        '$out': 'aggs'
    }
])

标签： pythonmongodbmongodb-querypymongo