mongodb - 在 mongodb 3.4 中查询

Question

我有大型 XML 文件，我必须将其转换为 json 并存储在 mongodb 中。转换和插入的python代码是：

import pymysql
import re
import json
import xmltodict
from pymongo import MongoClient

# Open Database Connection.
db = pymysql.connect("fffff","ddd","fgf","hnj")

# prepare a cursor object
cursor = db.cursor()

# execute SQL query 
cursor.execute("SELECT jlp.appid, convert(MAX(lex.response) using utf8) FROM jos_lender_portfolio jlp INNER JOIN jos_lexnex_data lex ON jlp.appid = lex.appid\
                group by appid limit 10;")

# Fetch all rows
data = cursor.fetchall()

a = (r'(?=<response>)(.*)(?<=</response>)')
def cleanxml(xml):
    if re.findall(a, xml, re.S):
        file = re.findall(a, xml, re.S)[0]
    else:
        file = "<response>NA</response>"
    return file
data = list(data)
client = MongoClient()
db = client['lexnex']
collection = db['test']
for row in data:
    thexml = cleanxml(row[1])
    jsonString = json.dumps(xmltodict.parse(thexml), indent = 4)
    d = json.loads(jsonString)
    newdict = {"caseid" : row[0]}
    newdict.update(d)
    jsondata = json.dumps(newdict, indent = 3)
    f = json.loads(jsondata)
    db.test.insert_one(f)

现在，问题是：我对 mongodb 非常陌生，并且在查询我的数据库时遇到问题。我有以下 json：

"_id":ObjectId("5aeff8537871560bf05d8c25"),
"caseid":44136,
"response":{
   "Header":{
      "TransactionId":"18092257R1069402",
      "Status":"0"
   },
   "Records":{
      "Record":[
         {
            "Filings":{
               "Filing":{
                  "Type":"INITIAL FILING",
                  "Date":{
                     "Day":"23",
                     "Month":"9",
                     "Year":"2008"
                  }
               }
            },
            "FilingJurisdiction":"NY",
            "MatchedParty":{
               "PartyType":"D",
               "Address":{

                  "City":"BROOKLYN",
                  "State":"NY",
                     },
               "OriginName":"GOLDLINE"
            },
            "Secureds":{
               "Secured":{
                  "Addresses":{
                     "Address":{
                        "City":"SCHAUMBURG",
                        "State":"IL"
                     }
                  }
               }
            }
         },
         {
,
            "Filings":{
               "Filing":{
                  "Type":"INITIAL FILING",
                  "Date":{
                     "Day":"23",
                     "Month":"9",
                     "Year":"2008"
                  }
               }
            },
            "FilingJurisdiction":"NY",
            "MatchedParty":{
               "PartyType":"D",
               "Address":{
                  "City":"BROOKLYN",
                  "State":"NY",
               },
               "OriginName":"GOLD"
            },
            "Secureds":{
               "Secured":{
                  "Addresses":{
                     "Address":{
                        "City":"SCHAUMBURG",
                        "State":"IL"
                     }
                  }
               }
            }
         }
      ]
   }
}

这是一个非常大的文档的一小部分，并且有超过一百万个这样的文档。现在，我想要的预期结果是每个caseid、 的某些部分Filings和Secureds. 这是示例预期输出：

"_id":ObjectId("5aeff8537871560bf05d8c25"),
"caseid":44136,
"Filings":{
   [
      "Filing":{
         "Type":"INITIAL FILING",
         "Date":{
            "Day":"23",
            "Month":"9",
            "Year":"2008"
         }
      },
      "Secureds":{
         "Secured":{
            "Addresses":{
               "Address":{
                  "City":"SCHAUMBURG",
                  "State":"IL"
               }
            }
         }
      },
      {
         "Filing":{
            "Type":"INITIAL FILING",
            "Date":{
               "Day":"23",
               "Month":"9",
               "Year":"2008"
            }
         }
      },
      "Secureds":{
         "Secured":{
            "Addresses":{
               "Address":{
                  "City":"SCHAUMBURG",
                  "State":"IL"
               }
            }
         }
      }
   ]
}

有几个 caseids，每个 caseids 都有 0 个或多个文件。我不知道该怎么做。我知道诸如简单查询之类的基础知识。但是，我认为这需要 $unwind 和 $group 一起使用。到目前为止，我所写的仅此而已：

db.test.aggregate([{$unwind:{path: '$response'}},{"$group":{_id:{caseid:"$caseid"}}}])

请帮忙。

标签： mongodb