Skip to Content

JSONB Operators for AI Metadata

When to use

Store semi-structured AI metadata (embedding source info, LLM tool-call traces, agent step payloads, RAG chunk attributes) and query key fields with index-level speed.

Analogy

JSONB is a flexible warehouse for irregular cargo. Operators like contains, mutate, and key-exists let you reshelve and probe without unpacking every box. Add a GIN index and queries become as fast as a labelled shelf.

Data-flow diagram

  document_chunks
    +----+---------+--------+--------------------------------------+
    | id | content | meta   | meta value                           |
    +----+---------+--------+--------------------------------------+
    | 1  | ...     | jsonb  | {"source_kind":"pdf",              |
    |    |         |        |  "tags":["rag","langgraph"],        |
    |    |         |        |  "as_of":"2026-01-31"}              |
    +----+---------+--------+--------------------------------------+
        |
        |  WHERE meta @> '{"source_kind":"pdf"}'
        v
    Index Scan using idx_chunks_meta on chunks
        Index Cond: (meta @> '{"source_kind":"pdf"}'::jsonb)

Deep explanation

JSONB is Postgres binary-JSON. Unlike plain JSON, duplicate keys are removed, keys are sorted, and the value supports operators. The workhorses for AI metadata are @> (contains), ? (key exists), <@ (is contained by), -> and ->> (extract field), and jsonb_set for safe mutation. Add a GIN index with the jsonb_path_ops opclass for the common @> pattern — that single combination gives you a sub-millisecond lookup over millions of chunks. Never store long, irregular strings in JSONB then use LIKE patterns — that misses indexing entirely and forces a sequential scan.

Examples

Example 1

-- 16a: Containment query for AI metadata
SELECT id, content
  FROM chunks
 WHERE meta @> '{"source_kind":"pdf"}'::jsonb
   AND meta @> '{"tags":["rag"]}'::jsonb
 ORDER BY ts DESC
 LIMIT 20;

’@>’ is structural: it checks ‘does meta have key source_kind equal to pdf AND tags array containing rag’. Both predicates in one query, one index hit.

Example 2

-- 16b: index for fast '@>' containment lookups
CREATE INDEX idx_chunks_meta
    ON chunks
 USING gin (meta jsonb_path_ops);

jsonb_path_ops is the small, fast, containment-only opclass. Use it when queries are exclusively ’@>’. Default jsonb_ops is bigger and slightly slower because it has to support every operator.

Example 3

-- 16c: safe per-row mutation with jsonb_set
UPDATE chunks
   SET meta = jsonb_set(meta, '{verified_by}', '"smoke_test"'::jsonb),
       updated_at = NOW()
 WHERE id = 42
   AND NOT (meta ? 'verified_by');

jsonb_set is the canonical safe mutation; using string-replace on a JSONB column corrupts the binary structure. Using a WHERE guard keeps the call idempotent on rerun.

Common mistake

Storing big JSONB chunks and querying them with meta->>‘source’ = ‘pdf’ plus a regular B-tree index — that pattern only indexes the extracted text column, and the underlying scan still re-parses JSONB on every row. Combine a GIN index with ’@>’ and the JSON parser runs only once per matched key. Another classic: forgetting jsonb_path_ops and ending up with a 12 GB GIN on a 6 GB table.

Key takeaway

Use JSONB (binary) over JSON (text). Index with ‘gin (col jsonb_path_ops)’ for ’@>’ lookups. Mutate with jsonb_set. Query with ’@>’, ’?’, ’->>’ — never with LIKE on the rendered JSON string.

Production Failure Playbook

Failure scenario 1: jsonb-gin-default-ops-bloat

Failure scenario 2: jsonb-uncompressed-toast-overflow