mcp-output-parsing-guide.md 7.05 KB

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:

{
  "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)

Option 1: Use mcp_android_phone_get_wechat_messages (Simpler)

For simple message retrieval, use the higher-level MCP tool which returns cleaner data:

# 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:

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'<title>(.*?)</title>', xml_part)
    title = html.unescape(title_match.group(1)) if title_match else None

    # Extract URL
    url_match = re.search(r'<url>(.*?)</url>', xml_part)
    url = html.unescape(url_match.group(1)) if url_match else None

    # Extract source display name
    source_match = re.search(r'<sourcedisplayname>(.*?)</sourcedisplayname>', 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'<sourceusername>(.*?)</sourceusername>', 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:

# The tool result may have a structuredContent field
# that contains pre-parsed JSON

Testing Your Parser

Always test with a small query first:

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:

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

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]