Structured Extraction: Messy Text to Validated JSON

Tier: Foundation · Hands-on time: ~30 minutes

When to use

• •
  You have 5–500 messy text items to convert into rows/fields (beyond ~500, script it against an API — same prompts, see pb-11 for cost).
• •
  The target structure is knowable in advance (you can name the fields).
• •
  Examples: support tickets → category/severity/component; job applications → skills matrix; vendor emails → renewal dates and amounts.

Prerequisites

• •
  A defined target schema — field names, types, allowed values.
• •
  Any current AI chat assistant; python3 or jq on your machine for validation.
• •
  A handful of representative input samples, including at least one ugly edge case.

The workflow

1. 1.

   Write the schema and rules first. Be explicit about types, enums, and what to do with missing data:

   Copy

   You are an extraction engine. Extract data from the text I provide into
   JSON matching EXACTLY this schema:
   
   {
     "ticket_id": "string or null",
     "customer": "string or null",
     "category": "one of: billing | access | bug | feature-request | other",
     "severity": "one of: low | medium | high | unknown",
     "component": "string or null",
     "requested_action": "string, max 20 words, or null",
     "dates_mentioned": ["YYYY-MM-DD"]
   }
   
   Rules:
   - Output ONLY a JSON array of objects, no markdown fences, no commentary.
   - If a field is not present in the text, use null. NEVER invent values.
   - Normalize dates to YYYY-MM-DD; if the year is ambiguous, use null.
   - If a text item is not a ticket at all, still emit an object with
     ticket_id null and category "other".
   
   Reply "READY" and wait for the data.
   

2. 2.

   Calibrate on 3 samples. Paste three items, including your ugly edge case. Check the output by hand against the source text, field by field. Fix the rules (not the data) where it goes wrong — e.g., add "phone numbers are not ticket_ids".

3. 3.

   Run the batch. Paste items in chunks of 10–20, numbered, so you can map outputs back to inputs. Keep chunks small: accuracy degrades quietly on long batches.

4. 4.

   Validate mechanically. Save the output as out.json and run:

   Copy

   python3 - <<'EOF'
   import json
   rows = json.load(open("out.json"))
   allowed = {"billing","access","bug","feature-request","other"}
   for i, r in enumerate(rows):
       assert set(r) == {"ticket_id","customer","category","severity",
                         "component","requested_action","dates_mentioned"}, (i, "keys")
       assert r["category"] in allowed, (i, r["category"])
   print(f"OK: {len(rows)} rows, schema valid")
   EOF
   

   A parse error or assertion failure means regenerate that chunk — never hand-patch model output, you will miss the second error.

5. 5.

   Human-audit a sample. Randomly pick 10% of rows (minimum 5) and compare every field against the source text. If more than one field in the sample is wrong, tighten rules and re-run the batch.

Tool variants

• Chat assistant (any major vendor): the workflow above; current models follow "JSON only" reliably, and some offer an explicit structured-output/JSON mode — turn it on if present.
• API with structured outputs: when this becomes recurring, the same schema becomes a typed response format or function/tool schema — guaranteed-parse output, worth the one-hour upgrade.
• Local model via Ollama: use a model tagged for instruction following and pass format json (Ollama flag) to force valid JSON; keep chunks at ~5 items for 8B-class models.

Verification

• •
  python3 validation passes: parseable JSON, exact key set, enum values legal.
• •
  Output count equals input count for every chunk.
• •
  10% human audit shows ≤1 field error total; zero invented values (spot-check nulls: is the field truly absent from the source?).
• •
  Dates survived normalization — check any input with formats like "3/4/25" (day-first vs month-first is a classic silent corruption).

Pitfalls

• Invented values instead of nulls. The single most damaging failure. The "NEVER invent" rule plus null spot-checks are your defense.
• Schema drift mid-batch. After many chunks the model may add helpful extra fields or prose. Re-paste the schema every few chunks or start a fresh chat per batch.
• Markdown fences around JSON. Breaks naive parsers; the "no markdown fences" rule usually holds, but strip ``` in your validator anyway.
• Trusting a clean parse as correctness. Valid JSON with wrong values passes every mechanical check — the human audit is not optional.

Restricted-environment variant

• •
  Client data: pseudonymize names, emails, account numbers before pasting (CUST_001, a***@***.com); keep the mapping in a local file and rejoin after extraction — the structure survives pseudonymization intact.
• •
  PII fields you must extract (emails, phones): do that specific extraction with a local regex or a local model, and use hosted AI only for the judgment fields (category, severity).
• •
  Fully offline: Ollama with format json runs this entire playbook locally; accuracy at 8B is close to hosted models for extraction because the task requires no external knowledge.

Success criteria

• •
  A batch of ≥30 messy items converted to validated JSON in under 30 minutes.
• •
  Validator passes on first run for at least the final batch; audit error rate ≤2% of fields.
• •
  Your schema+rules prompt is saved to your prompt library (pb-05) — next time this is a 5-minute task.
• •
  Nulls in the output correspond to genuinely missing data, verified by spot-check.