SQL Reference ยท Constraints and Data Types
SQL JSON types
Reference for storing JSON in SQL: JSON and JSONB types, SQLite's text-based JSON functions, extraction operators and how each database differs.
JSON columns store semi-structured documents inside a relational table. They are useful for data whose shape varies from row to row, such as event payloads, user preferences or settings, while the rest of the table keeps ordinary typed columns. SQL:2016 standardised JSON functions, and every major database has its own flavor.
SQLite has no JSON type. A JSON document is stored as TEXT, and the built-in JSON functions parse it when you call them. Since version 3.45 SQLite also has a binary form called JSONB (stored as a blob) that is faster to process, written with jsonb() and its sibling functions.
CREATE TABLE events ( id INTEGER PRIMARY KEY, payload TEXT CHECK (json_valid(payload)) );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
column_name JSON column_name TEXT CHECK (json_valid(column_name)) -- SQLite json_extract(doc, path) -- value at path doc -> path -- value at path, as JSON doc ->> path -- value at path, as an SQL value json_each(doc [, path]) -- one row per element json_object(k1, v1, ...) -- build an object json_array(v1, v2, ...) -- build an array
| Part | Description |
|---|---|
doc |
A JSON document (text in SQLite). |
path |
A path such as '$.name', '$.tags[0]' or '$.address.city'. $ is the document root. |
Returns: json_extract and ->> return SQL values (text, number or NULL). The -> operator returns JSON text, so strings keep their double quotes.
Dialect differences
| Database | Storage | Extract a field | Notes |
|---|---|---|---|
| SQLite | TEXT (or JSONB blob from 3.45) |
json_extract(x, '$.a'), x ->> '$.a' |
Documents are re-parsed on each call unless stored as JSONB. |
| PostgreSQL | JSON (text copy) and JSONB (binary, indexable) |
x ->> 'a', x #>> '{a,b}' |
Use JSONB almost always; supports GIN indexes and containment @>. |
| MySQL | JSON (binary) |
x ->> '$.a', JSON_EXTRACT(x, '$.a') |
Index through generated columns or multi-valued indexes. |
| SQL Server | NVARCHAR(MAX); native json type in SQL Server 2025 |
JSON_VALUE(x, '$.a'), JSON_QUERY(x, '$.arr') |
OPENJSON expands arrays into rows. |
| Oracle | JSON (21c+), earlier VARCHAR2/CLOB with IS JSON |
JSON_VALUE(x, '$.a'), x.a dot notation |
JSON_TABLE produces relational rows. |
-- PostgreSQL SELECT payload ->> 'page' FROM events WHERE payload @> '{"type": "click"}'; CREATE INDEX idx_events_payload ON events USING GIN (payload); -- SQL Server SELECT JSON_VALUE(payload, '$.page') FROM events; -- MySQL SELECT payload ->> '$.page' FROM events;
Usage notes
- Validate on write. In SQLite and SQL Server add
CHECK (json_valid(col))orISJSON(col) = 1so bad text never lands in the table. ->versus->>.->keeps JSON typing ("click"with quotes),->>gives a plain SQL value (click). Compare with->>inWHERE.- Missing paths produce
NULL, not an error, so a typo in the path looks like absent data. - Do not hide core columns in JSON. Columns you filter, join or constrain on belong in real columns where they can have types, constraints and indexes.
- Indexing: index the extracted expression in SQLite (
CREATE INDEX ... ON events (payload ->> '$.type')), use GIN onJSONBin PostgreSQL, and generated columns in MySQL. - Updating means replacing part of a document with
json_set(),json_remove()orjson_patch()(SQLite), orjsonb_set()(PostgreSQL). - Large documents are rewritten as a whole when changed, so frequent small updates to a huge JSON value are expensive.
- Numbers. JSON has one number type; big integers can lose precision in clients that read them as floating point.