Getting JSON From AI: Reliable Structured Data

You ask the AI to pull the information from an invoice "in JSON," and it replies: "Sure! Here's the JSON you asked for," followed by a code block, then a note explaining its choices. The next day, the amount field is called total, the date is spelled out in words, and the price becomes "€125.50", a string. For a human, it's readable. For a script or a spreadsheet, it's a breakdown.

JSON is a text format that stores data as name and value pairs. You don't need to know how to program to benefit from it, and if your destination is a spreadsheet, CSV will be even simpler. In both cases, the principle is the same: you decide on the format in advance, leaving no room for interpretation.

Describe the schema instead of saying "in JSON"

The word "JSON" alone leaves the model free to choose the structure, the names, and the types. You have to give it the complete mold. Take a restaurant owner who wants to track supplier invoices.

Before:

Extract the information from this invoice as JSON.

After:

Extract the data from the invoice below. Reply only with a valid JSON object, with no text before or after and no Markdown code fences.

Follow exactly this structure and these field names:

{
  "supplier": "string",
  "invoice_number": "string",
  "date": "YYYY-MM-DD",
  "total_excl_tax": number,
  "total_incl_tax": number,
  "lines": [
    { "description": "string", "quantity": number, "unit_price_excl_tax": number }
  ],
  "category": "drinks" | "fresh" | "groceries" | "other"
}

Rules:
- Numbers are written with a decimal point and no currency symbol.
- If a piece of information is missing or unreadable, put null. Never guess it and never calculate it.
- Don't add any fields.

Invoice:
[INVOICE TEXT]

Four choices make the difference. The schema is shown in full, so the field names stop moving. The date has an unambiguous format: 2026-03-04 can't be mistaken from one country to another. The category is a closed list, which avoids having "Drinks," "drink," and "Wines" in the same file. Finally, null gives the AI an honest way out when the information is missing.

Missing fields, the source of most errors

When a field is required in your schema and the information isn't in the document, the model tends to produce one. A VAT number can show up where there wasn't any. The JSON will be perfectly valid and wrong.

Two precautions help. The first is the null rule above, for every field. The second is to ask for proof when the stakes justify it: add a source_excerpt field that copies the passage the value came from. You can then compare quickly on a sample. For the general logic, the article on reducing hallucinations goes further.

The most common case: a CSV for Excel

If your destination is a spreadsheet, forget JSON: ask directly for a CSV. A quirk worth knowing in French-speaking locales: with French regional settings, Excel generally expects a semicolon as separator and a comma for decimals. A CSV with commas can therefore open with everything in a single column.

Example for a nonprofit treasurer converting registrations received by email, with made-up data here:

Turn the messages below into a CSV table, with no text around it and no code fences.

Parameters:
- separator: semicolon ;
- decimals: comma
- first line, exactly: last_name;first_name;activity;amount;payment_method;to_check
- activity: one of [LIST OF ACTIVITIES]
- payment_method: check, transfer, cash, or empty if not specified.

One line per registered person. If a message registers two people, make two lines. If a piece of information is missing or ambiguous, leave the cell empty and put "yes" in to_check.

Messages:
[PASTE THE MESSAGES]

Paste the result into a .csv file, or into your spreadsheet through its text import function. Watch out for personal data: before pasting messages from real people, look at what not to put in a chatbot and the terms of your plan.

Validate the result, every time

A structured output can break in three ways: the text isn't valid JSON, the structure doesn't match the schema, or the values are wrong. The first two can be checked automatically. Here's a short Python script that stops as soon as a problem appears:

import json

FIELDS = {"supplier", "invoice_number", "date", "total_excl_tax",
          "total_incl_tax", "lines", "category"}
CATEGORIES = {"drinks", "fresh", "groceries", "other"}

def validate(response: str) -> dict:
    data = json.loads(response)  # fails if it's not JSON
    if set(data) != FIELDS:
        raise ValueError(f"Incorrect fields: {set(data) ^ FIELDS}")
    if data["category"] not in CATEGORIES:
        raise ValueError(f"Unknown category: {data['category']}")
    incl_tax, excl_tax = data["total_incl_tax"], data["total_excl_tax"]
    if incl_tax is not None and excl_tax is not None and incl_tax < excl_tax:
        raise ValueError("Total including tax is lower than total excluding tax")
    return data

The check on the tax-inclusive total is plain business sense, and it catches errors that no schema would see. For the values, plan a spot check: compare ten documents by hand before trusting the process, then a few now and then.

Without code, you can ask the model to flag suspicious lines in a new conversation, but it may miss some. A sum recalculated in the spreadsheet is still safer.

Move to the API when the volume grows

If you process dozens of documents with a program, the prompt alone isn't the best solution. The main providers' APIs offer options to constrain the output to a JSON schema. Their names and guarantees evolve, so look at your provider's documentation when you get started. These options guarantee a conforming structure, not correct values: keep the null rule and the business checks.

For a multi-step flow, a JSON output also serves as a bridge between two models or two prompts, as in a prompt chain.

Try it on three real documents

Pick three documents of the same type, including a badly formed one: a scanned invoice, an incomplete message. Run them through the prompt of your choice with a schema of only five fields, then compare each value to the original. The badly formed document will tell you which rules to add before widening the schema.