JSON in SQL

Modern relational databases can store and query JSON alongside regular columns — useful for flexible attributes, API payloads, and settings.

Storing and reading JSON (SQLite)

-- standalone
CREATE TABLE events (
  id      INTEGER PRIMARY KEY,
  type    TEXT NOT NULL,
  payload TEXT NOT NULL CHECK (json_valid(payload))
);

INSERT INTO events (type, payload) VALUES
  ('signup',   '{"user": "ada", "plan": "pro", "referrer": {"source": "blog"}}'),
  ('signup',   '{"user": "linus", "plan": "free"}'),
  ('purchase', '{"user": "ada", "amount": 120, "items": ["keyboard", "hub"]}');

SELECT id,
  payload ->> '$.user'            AS user,
  payload ->> '$.plan'            AS plan,
  payload ->> '$.referrer.source' AS source
FROM events
WHERE type = 'signup';

+----+-------+------+--------+
| id | user  | plan | source |
+----+-------+------+--------+
|  1 | ada   | pro  | blog   |
|  2 | linus | free | NULL   |
+----+-------+------+--------+

• ->> extracts a value as plain SQL text or number; -> returns JSON. Paths start with $ and use . for keys and [n] for array positions.
• Missing keys return NULL.
• CHECK (json_valid(payload)) rejects invalid JSON.

Expanding arrays into rows

-- standalone
CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);
INSERT INTO events (payload) VALUES ('{"user": "ada", "items": ["keyboard", "hub", "stand"]}');

SELECT e.id, e.payload ->> '$.user' AS user, item.value AS item
FROM events e, json_each(e.payload, '$.items') AS item;

+----+------+----------+
| id | user |   item   |
+----+------+----------+
|  1 | ada  | keyboard |
|  1 | ada  | hub      |
|  1 | ada  | stand    |
+----+------+----------+

json_each turns a JSON array into rows, so ordinary SQL — joins, GROUP BY — works on it. (PostgreSQL: jsonb_array_elements; MySQL: JSON_TABLE.)

Building JSON

SELECT json_object('id', id, 'name', name, 'price', price) AS product
FROM products
WHERE id <= 2;

+--------------------------------------------------+
|                     product                      |
+--------------------------------------------------+
| {"id":1,"name":"Mechanical Keyboard","price":89} |
| {"id":2,"name":"USB-C Hub","price":35.5}         |
+--------------------------------------------------+

json_group_array() aggregates rows into a JSON array — handy for returning nested data to an API in one query.

PostgreSQL's jsonb

PostgreSQL's jsonb type stores JSON in a binary format that's fast to query and can be indexed:

-- PostgreSQL
CREATE INDEX idx_events_payload ON events USING GIN (payload);

SELECT * FROM events WHERE payload @> '{"plan": "pro"}';

When to use JSON columns

JSON is great for data whose shape genuinely varies, or that you store and return as a whole. But values you filter, join, or aggregate on regularly — a user ID, an amount, a status — belong in real columns, with proper types, constraints, and indexes. Paste payloads into the JSON Formatter to check their structure.