# MCP WeChat Query Output Parsing Guide ## Problem The `mcp_android_phone_query_wechat` tool returns data in a deeply nested, double-escaped JSON format that is extremely difficult to parse with simple string operations. ## Output Format Structure The MCP response follows this structure: ```json { "result": "{\"rows\": [{\"msgId\": 975, \"type\": 49, ...escaped content...}]}" } ``` Where: - The outer object has a `result` field - The `result` value is a JSON-encoded string (double quotes escaped as `\"`) - Inside that string is the actual JSON with WeChat message data - Message `content` fields contain XML with HTML entities (` `, `&`) - The XML itself may contain escaped newlines (`\n`) ## Why Simple Parsing Fails 1. **Double-escaped quotes**: The JSON string uses `\"` which becomes `\` + `"` after one unescape, but needs to become just `"` 2. **Escaped backslashes**: Content may contain `\\` which needs to become `\` 3. **HTML entities in XML**: ` ` (space), `&` (&), `<`, `>` inside message content 4. **Mixed encodings**: The data goes through multiple encoding layers (JSON → string → XML → HTML entities) ## Recommended Parsing Strategy ### Option 1: Use `mcp_android_phone_get_wechat_messages` (Simpler) For simple message retrieval, use the higher-level MCP tool which returns cleaner data: ```python # This tool returns pre-parsed messages with sender, content, timestamp # Use it instead of raw SQL when you don't need complex joins ``` ### Option 2: Parse Raw SQL Output with Proper Unescaping When you need raw SQL queries, use this robust parsing approach: ```python import json import html import re def parse_mcp_wechat_output(raw_text): """ Parse the double-escaped JSON output from mcp_android_phone_query_wechat. Args: raw_text: The raw text from the MCP tool output file or response Returns: list: Parsed rows from the query result """ # Step 1: Extract the JSON object from the response text # The response may be wrapped in markdown code blocks or other formatting # Find the actual JSON content - look for {"result": "..."} match = re.search(r'\{\s*"result"\s*:\s*"(.*)"\s*\}', raw_text, re.DOTALL) if not match: # Try without the outer wrapper (direct JSON) match = re.search(r'\{\s*"result"\s*:\s*\{.*\}\s*\}', raw_text, re.DOTALL) if match: return json.loads(match.group(0)) raise ValueError("Could not find result field in output") # Step 2: Extract the inner JSON string inner_json_str = match.group(1) # Step 3: Unescape the JSON string # The string has been JSON-encoded, so we need to decode it # Use json.loads on the quoted string to properly unescape try: # Wrap in quotes to make it a valid JSON string quoted = '"' + inner_json_str + '"' decoded = json.loads(quoted) except json.JSONDecodeError: # Fallback: manual unescaping decoded = inner_json_str.replace('\\"', '"').replace('\\\\', '\\').replace('\\n', '\n') # Step 4: Parse the decoded JSON data = json.loads(decoded) return data.get('rows', []) def extract_article_info(message_content): """ Extract article title, URL, and source from a type 49 message content. Args: message_content: The XML content string from a type 49 message Returns: dict: {title, url, source_name, source_id} or None if not an article """ # The content may be prefixed with "sender:\n" if ':' in message_content and '\n' in message_content: sender, xml_part = message_content.split('\n', 1) else: xml_part = message_content sender = None # Extract title title_match = re.search(r'(.*?)', xml_part) title = html.unescape(title_match.group(1)) if title_match else None # Extract URL url_match = re.search(r'(.*?)', xml_part) url = html.unescape(url_match.group(1)) if url_match else None # Extract source display name source_match = re.search(r'(.*?)', xml_part) source_name = html.unescape(source_match.group(1)) if source_match else None # Extract source username (gh_...) source_id_match = re.search(r'(.*?)', xml_part) source_id = source_id_match.group(1) if source_id_match else None return { 'title': title, 'url': url, 'source_name': source_name, 'source_id': source_id, 'sender': sender } ``` ### Option 3: Use Structured Content (When Available) Some MCP tools return `structuredContent` alongside the text output. Check if this cleaner format is available: ```python # The tool result may have a structuredContent field # that contains pre-parsed JSON ``` ## Testing Your Parser Always test with a small query first: ```sql SELECT msgId, type, isSend, createTime FROM message WHERE talker = 'CHATROOM_ID@chatroom' ORDER BY createTime DESC LIMIT 5 ``` This returns minimal content (no XML blobs) and lets you verify timestamp parsing before attempting full content extraction. ## Timestamp Verification WeChat timestamps are in milliseconds. To verify your date range: ```python from datetime import datetime, timezone, timedelta beijing = timezone(timedelta(hours=8)) def ms_to_beijing(ms): return datetime.fromtimestamp(ms / 1000, tz=beijing) # Example: verify what range you're querying print(ms_to_beijing(1750809600000)) # Should be 2026-06-25 00:00:00+08:00 print(ms_to_beijing(1750895999999)) # Should be 2026-06-25 23:59:59+08:00 ``` ## Common Pitfalls 1. **Don't use simple string replace for unescaping**: `replace('\\"', '"')` alone fails because the backslash itself may be escaped 2. **Don't parse XML with regex for complex content**: Use `xml.etree.ElementTree` or `html.unescape` + proper XML parser for reliable extraction 3. **Check for empty results**: If your query returns 0 rows, verify the timestamp range - messages may exist for a different date 4. **Content field size**: Type 49 messages can have very large content fields (50KB+) with full XML. Use `LIMIT` to avoid overwhelming the parser. ## Working Example: Complete Pipeline ```python import json import html import re from datetime import datetime, timezone, timedelta beijing = timezone(timedelta(hours=8)) # 1. Calculate timestamp range yesterday = datetime.now(beijing) - timedelta(days=1) start_ms = int(yesterday.replace(hour=0, minute=0, second=0, microsecond=0).timestamp() * 1000) end_ms = int(yesterday.replace(hour=23, minute=59, second=59, microsecond=999999).timestamp() * 1000) # 2. Query via MCP (returns text that needs parsing) # sql = f"SELECT msgId, type, content, createTime FROM message WHERE talker = '...' AND createTime > {start_ms} AND createTime < {end_ms}" # result = mcp_android_phone_query_wechat(sql=sql) # 3. Parse the result (see parse_mcp_wechat_output above) # rows = parse_mcp_wechat_output(result_text) # 4. Extract articles # articles = [extract_article_info(row['content']) for row in rows if row['type'] == 49] ```