JSON in databases and storage

Query and index JSON columns in PostgreSQL and MySQL, work with nested documents in MongoDB, and recognise when normalising is the better answer.

PostgreSQL json and jsonb

CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  received_at timestamptz NOT NULL DEFAULT now(),
  payload     jsonb NOT NULL
);

-- json keeps the exact text; jsonb parses, deduplicates keys and is indexable
ALTER TABLE events
  ADD CONSTRAINT payload_is_object CHECK (jsonb_typeof(payload) = 'object');

-- extraction: -> returns json, ->> returns text
SELECT payload ->> 'level' AS level,
       payload -> 'context' ->> 'request_id' AS request_id
FROM events;

-- nested paths and array elements
SELECT payload #>> '{context,tags,0}' FROM events;
SELECT jsonb_array_length(payload -> 'items') FROM events;

-- containment: does the payload contain this fragment?
SELECT * FROM events WHERE payload @> '{"level":"error"}';

-- does any of these keys exist?
SELECT * FROM events WHERE payload ?| array['error','exception'];

-- a generated column makes a hot field a first-class column
ALTER TABLE events
  ADD COLUMN level text GENERATED ALWAYS AS (payload ->> 'level') STORED;

CREATE INDEX events_level_idx ON events (level, received_at DESC);

-- indexing the whole document, for containment and path queries
CREATE INDEX events_payload_idx ON events USING gin (payload jsonb_path_ops);

MySQL and MongoDB

-- MySQL: a JSON column with an extraction path
CREATE TABLE events (
  id      BIGINT AUTO_INCREMENT PRIMARY KEY,
  payload JSON NOT NULL,
  level   VARCHAR(16) GENERATED ALWAYS AS (payload ->> '$.level') STORED,
  KEY events_level_idx (level)
);

SELECT payload ->> '$.context.request_id' AS request_id
FROM events
WHERE JSON_EXTRACT(payload, '$.level') = 'error';

-- a functional index on a nested path
CREATE INDEX events_request_idx
  ON events ((payload ->> '$.context.request_id'));

// MongoDB: documents are the native form
await db.collection("events").insertOne({
  level: "error",
  context: { requestId: "req_7c1a", tags: ["api", "eu"] },
  receivedAt: new Date(),
});

// dot notation reaches into nested objects and arrays
await db.collection("events").find({ "context.tags": "api" }).toArray();

// match one element of an array against several conditions
await db.collection("events").find({
  items: { $elemMatch: { sku: "A-1", quantity: { $gte: 2 } } },
}).toArray();

// an index on a nested field, and one on an array field
await db.collection("events").createIndex({ "context.requestId": 1 });
await db.collection("events").createIndex({ "context.tags": 1 });   // multikey

// project only what you need, which is the main lever on read cost
await db.collection("events")
  .find({ level: "error" }, { projection: { "context.requestId": 1, receivedAt: 1 } })
  .toArray();

• A multikey index covers every element of an array in a document; one index can therefore serve a query over any tag.
• Projection matters more here than in a relational database: fetching a whole document to read one field moves a lot of bytes.
• An unbounded array inside a document is the classic modelling mistake - it grows until the document hits the 16 MB limit and updates become slow.

When to normalise instead

1. Store the raw payload in a JSON column for fidelity - it is your record of what actually arrived.
2. Promote the fields you query into generated columns or real columns, and index those.
3. Keep relational data relational. A foreign key inside JSON is a reference the database cannot protect.
4. Watch for the moment a JSON column gains a schema in practice: once every row has the same keys, it is a table that has not been migrated yet.
5. Add a check constraint on the type and required keys, so a malformed document cannot be inserted even before you have migrated.

-- a schema in practice, expressed as a constraint
ALTER TABLE events ADD CONSTRAINT payload_shape CHECK (
  jsonb_typeof(payload) = 'object'
  AND payload ? 'level'
  AND jsonb_typeof(payload -> 'level') = 'string'
);

FAQ

Related

JSON Lines, streaming and large documents When to use something else

Last refreshed 2026-09-18.