Skip to content
Tutorials Center
My learning

Topics

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.

SQL
CREATE TABLE events (
  id      INTEGER PRIMARY KEY,
  payload TEXT CHECK (json_valid(payload))
);

Try it

SQL Reading values out of a JSON document

Press Run (or Ctrl + Enter) to see the result here.

SQL A JSON column, filtered and expanded

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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)) or ISJSON(col) = 1 so bad text never lands in the table.
  • -> versus ->>. -> keeps JSON typing ("click" with quotes), ->> gives a plain SQL value (click). Compare with ->> in WHERE.
  • 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 on JSONB in PostgreSQL, and generated columns in MySQL.
  • Updating means replacing part of a document with json_set(), json_remove() or json_patch() (SQLite), or jsonb_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.