MongoDB 3.4查询求助：按caseid提取Filings与Secureds数据

Hey Karan! Let's work through this aggregation problem step by step to get you the exact output you need from your MongoDB collection.

Background & Context #

You converted your large XML dataset to JSON and stored it in MongoDB using this Python code:

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)

Your MongoDB documents follow this structure:

{
    "_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"
                                }
                            }
                        }
                    }
                }
            ]
        }
    }
}

Your goal is to extract, for each caseid, all related Filings and Secureds data into a structured list.

What's Off With Your Current Query? #

Your existing aggregate tries to $unwind $response, but response is an object, not an array. The array you need to unwind is response.Records.Record—this is where each individual Filing/Secureds pair lives.

Correct Aggregation Pipeline #

Here's a pipeline that produces clean, valid JSON matching your intent (I adjusted the structure to be syntactically correct, since arrays can't directly contain key-value pairs without wrapping them in objects):

db.test.aggregate([
    // Step 1: Unwind the Record array to create a document per Record entry
    { $unwind: "$response.Records.Record" },
    // Step 2: Extract only the fields we need, pairing Filing and Secureds together
    {
        $project: {
            caseid: 1,
            filingPair: {
                Filing: "$response.Records.Record.Filings.Filing",
                Secureds: "$response.Records.Record.Secureds"
            }
        }
    },
    // Step 3: Group documents back together by caseid and _id, collecting all pairs
    {
        $group: {
            _id: {
                _id: "$_id",
                caseid: "$caseid"
            },
            Filings: { $push: "$filingPair" }
        }
    },
    // Step 4: Reshape output to match your desired top-level structure
    {
        $project: {
            _id: "$_id._id",
            caseid: "$_id.caseid",
            Filings: 1
        }
    }
])

What Each Step Does: #

1. $unwind: Breaks the Record array into individual documents, so each document holds one Record's Filings and Secureds.
2. $project: Creates a filingPair object to keep related Filing and Secureds data linked.
3. $group: Recombines documents by _id and caseid, pushing all filingPair objects into a single Filings array.
4. Final $project: Adjusts the output to place _id and caseid at the top level, with the consolidated Filings array.

Sample Output #

The result will look like this:

{
    "_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"
                        }
                    }
                }
            }
        }
    ]
}

Alternate Pipeline for Alternating Entries #

If you want the array to alternate individual Filing and Secureds entries (matching your original desired output structure), use this pipeline:

db.test.aggregate([
    { $unwind: "$response.Records.Record" },
    {
        $project: {
            caseid: 1,
            filingEntry: { Filing: "$response.Records.Record.Filings.Filing" },
            securedEntry: { Secureds: "$response.Records.Record.Secureds" }
        }
    },
    {
        $group: {
            _id: { _id: "$_id", caseid: "$caseid" },
            tempFilings: { $push: ["$filingEntry", "$securedEntry"] }
        }
    },
    {
        $project: {
            _id: "$_id._id",
            caseid: "$_id.caseid",
            Filings: { $reduce: { input: "$tempFilings", initialValue: [], in: { $concatArrays: ["$$value", "$$this"] } } }
        }
    }
])

Performance Tip #

With over a million documents, add an index to speed up the $group stage:

db.test.createIndex({ caseid: 1 })

内容的提问来源于stack exchange，提问作者Karan Gupta