SQLite JSON Storage for AI Agents: json_extract, json_set, and Repair Patterns (2026)

SQLite is the default memory layer for AI agents in 2026. No vector database required, no infra overhead, and the built-in JSON functions handle everything from simple state queries to complex episodic memory lookups.

The catch: LLM-generated JSON breaks. Trailing commas, unquoted keys, truncated outputs — they all fail silently at INSERT time. This guide covers the full stack: SQLite's JSON functions, agent memory patterns, and the repair step you need before any LLM output touches your database.

Written by The JSON Medic.

---

Why SQLite for AI Agent Memory

In 2025, most agent frameworks defaulted to vector databases for memory. In 2026, the pattern shifted. The memweave pattern — Markdown files as the source of truth, SQLite as the derived index — is now the dominant approach for production agents that don't need distributed storage.

The reasons are practical:

• Zero infra: SQLite runs in-process. No connection pooling, no separate server, no cloud costs.
• Full-text search: FTS5 extension handles semantic search well enough for most agent memory use cases.
• JSON native: Five JSON functions built in, no extension needed. json_extract, json_set, json_object, json_array, json_valid — all available out of the box.
• ACID transactions: Write agent state atomically. Either all tool results commit or none do.

The sqliteai/sqlite-agent extension even brings autonomous multi-step agents directly into the database — your agent's memory and execution model share the same SQLite process.

---

SQLite's Built-In JSON Functions

Before the agent patterns, the foundational functions:

json_extract(json, path)

Extract a value from stored JSON using JSONPath syntax:

-- Store agent state
CREATE TABLE agent_state (
  agent_id TEXT PRIMARY KEY,
  state    TEXT  -- JSON column
);

INSERT INTO agent_state VALUES (
  'agent-001',
  '{"step": "reasoning", "confidence": 0.87, "tools_used": ["search", "validate"]}'
);

-- Query nested values
SELECT json_extract(state, '$.step') FROM agent_state WHERE agent_id = 'agent-001';
-- → "reasoning"

SELECT json_extract(state, '$.confidence') FROM agent_state WHERE agent_id = 'agent-001';
-- → 0.87

SELECT json_extract(state, '$.tools_used[0]') FROM agent_state WHERE agent_id = 'agent-001';
-- → "search"

json_set(json, path, value)

Update a value non-destructively, returning the modified JSON:

UPDATE agent_state
SET state = json_set(state, '$.step', 'executing', '$.confidence', 0.92)
WHERE agent_id = 'agent-001';

This is safer than serializing the full object in application code — the database handles the partial update.

json_valid(json)

Returns 1 if the string is valid JSON, 0 otherwise. This is your first line of defense before any INSERT:

SELECT json_valid('{"key": "value"}');  -- 1
SELECT json_valid('{"key": "value",}'); -- 0 (trailing comma)
SELECT json_valid('not json at all');   -- 0

json_object() and json_array()

Build JSON from column values — useful for constructing agent memory summaries:

SELECT json_object(
  'agent_id', agent_id,
  'step', json_extract(state, '$.step'),
  'timestamp', datetime('now')
) AS snapshot
FROM agent_state;

json_each(json) — Table-Valued Function

Iterate over JSON arrays as rows:

CREATE TABLE tool_calls (
  run_id TEXT,
  tools  TEXT  -- JSON array
);

INSERT INTO tool_calls VALUES ('run-42', '["search", "validate", "json_repair", "summarize"]');

-- Expand to rows
SELECT value AS tool_name
FROM tool_calls, json_each(tools)
WHERE run_id = 'run-42';
-- → search, validate, json_repair, summarize

---

The Core Problem: LLM Output Breaks json_valid()

SQLite's json_valid() is strict. Any deviation from RFC 8259 returns 0, and a subsequent INSERT will store invalid JSON silently — no error, just corrupt data.

Common LLM JSON failures that break json_valid():

If you INSERT any of these directly, json_extract() will return NULL on every subsequent query. The INSERT didn't fail — the data is just permanently broken.

---

The Repair-Before-INSERT Pattern

The right pattern: validate first, repair if needed, then INSERT:

import sqlite3
import requests  # or use the aijsonmedic Python client

def safe_insert_agent_state(conn, agent_id: str, raw_json: str):
    # 1. Check validity first
    is_valid = conn.execute("SELECT json_valid(?)", (raw_json,)).fetchone()[0]
    
    if not is_valid:
        # 2. Repair before INSERT
        repaired = repair_json(raw_json)
        if repaired is None:
            raise ValueError(f"Could not repair JSON for agent {agent_id}")
        raw_json = repaired
    
    # 3. Safe to INSERT
    conn.execute(
        "INSERT OR REPLACE INTO agent_state (agent_id, state) VALUES (?, ?)",
        (agent_id, raw_json)
    )
    conn.commit()

def repair_json(broken_json: str) -> str | None:
    """Repair using AI JSON Medic's engine."""
    # Use the /json-repair endpoint at aijsonmedic.com
    # or the local jsonrepair library as a lighter fallback
    try:
        import json
        # Try stdlib first for simple cases
        json.loads(broken_json)
        return broken_json
    except json.JSONDecodeError:
        pass
    
    # Use jsonrepair for fixable cases
    try:
        from jsonrepair import repair_json as _repair
        return _repair(broken_json)
    except Exception:
        return None

For complex LLM outputs — truncated responses, markdown-wrapped JSON, deeply nested schema violations — you need a more robust repair engine. The AI JSON repair tool at aijsonmedic.com handles 16 failure categories automatically, including streaming truncation recovery.

---

Agent Memory Schema Patterns

1. Episodic Memory Table

Store what the agent did in each run:

CREATE TABLE episodic_memory (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id   TEXT NOT NULL,
  run_id     TEXT NOT NULL,
  timestamp  TEXT DEFAULT (datetime('now')),
  event_type TEXT NOT NULL,  -- 'observation', 'action', 'result', 'error'
  payload    TEXT NOT NULL,  -- JSON
  CONSTRAINT valid_json CHECK (json_valid(payload))
);

The CHECK (json_valid(payload)) constraint is a SQLite-level safety net — it rejects invalid JSON at the database layer, not just the application layer.

2. Semantic Memory Table

Store facts the agent has learned:

CREATE TABLE semantic_memory (
  key        TEXT PRIMARY KEY,
  value      TEXT NOT NULL,  -- JSON
  confidence REAL DEFAULT 1.0,
  source     TEXT,
  updated_at TEXT DEFAULT (datetime('now'))
);

-- Query with json_extract
SELECT key, json_extract(value, '$.summary') AS summary
FROM semantic_memory
WHERE confidence > 0.8
ORDER BY updated_at DESC
LIMIT 10;

3. Tool Output Cache

Cache expensive tool calls to avoid redundant LLM invocations:

CREATE TABLE tool_cache (
  tool_name   TEXT NOT NULL,
  input_hash  TEXT NOT NULL,
  output      TEXT NOT NULL,  -- JSON
  created_at  TEXT DEFAULT (datetime('now')),
  expires_at  TEXT,
  PRIMARY KEY (tool_name, input_hash),
  CONSTRAINT valid_output CHECK (json_valid(output))
);

-- Get cached result or NULL
SELECT output
FROM tool_cache
WHERE tool_name = 'web_search'
  AND input_hash = ?
  AND (expires_at IS NULL OR expires_at > datetime('now'));

---

Querying Agent State at Scale

Once you have JSON stored, json_extract in WHERE and ORDER BY clauses lets you query on any field without schema migrations:

-- Find all agents in the 'planning' step with high confidence
SELECT agent_id, json_extract(state, '$.confidence') AS conf
FROM agent_state
WHERE json_extract(state, '$.step') = 'planning'
  AND CAST(json_extract(state, '$.confidence') AS REAL) > 0.85
ORDER BY conf DESC;

-- Count errors by type from episodic memory
SELECT
  json_extract(payload, '$.error_type') AS error_type,
  COUNT(*) AS occurrences
FROM episodic_memory
WHERE event_type = 'error'
GROUP BY 1
ORDER BY 2 DESC;

-- Find all tool calls in a specific run
SELECT
  json_extract(payload, '$.tool') AS tool,
  json_extract(payload, '$.duration_ms') AS duration_ms
FROM episodic_memory
WHERE run_id = 'run-42'
  AND event_type = 'action'
ORDER BY timestamp;

---

Generated Column Indexes for Performance

For high-frequency queries on extracted JSON fields, create a generated column with an index:

ALTER TABLE agent_state ADD COLUMN step TEXT
  GENERATED ALWAYS AS (json_extract(state, '$.step')) VIRTUAL;

CREATE INDEX idx_agent_step ON agent_state(step);

-- Now this query uses the index
SELECT * FROM agent_state WHERE step = 'planning';

This avoids the per-row json_extract overhead on large tables.

---

Validating Complex Agent Outputs

When your LLM returns structured agent outputs — tool parameters, function call schemas, nested memory objects — json_valid() only confirms it's valid JSON, not that it matches your expected schema.

For schema validation, pipe the stored JSON through your application layer or use the JSON Schema validator to check structure before you rely on it in production. This matters especially for:

• Tool parameter schemas (if the shape is wrong, the tool call silently fails)
• Multi-agent handoffs (one agent's output is another's input — corruption propagates)
• Long-running agent loops (a single malformed JSON at step 3 can corrupt step 30)

---

The Full Stack in 2026

The pattern that works in production:

LLM output (raw text)
     ↓
json_valid() check
     ↓ if invalid
Repair (jsonrepair / AI JSON Medic)
     ↓
INSERT with CHECK constraint
     ↓
json_extract() queries
     ↓
Agent proceeds with clean state

SQLite handles the storage and query layer. The repair step handles LLM imperfection. The combination means your agents run on reliable state without the operational complexity of a vector database or external schema store.

---

Quick Reference

---

Next Steps

• Test your LLM's JSON output quality with the free JSON repair tool — paste a raw LLM response to see which categories of errors appear
• Use the JSON validator to check agent output schemas before they hit production pipelines
• The SQLite docs on JSON functions cover edge cases like json_patch and json_quote not covered here

The repair-before-INSERT pattern is one guard. Schema validation is the other. Together they make SQLite a production-grade agent memory layer, not just a lightweight convenience.