JSON in SQL

Relational databases are built around structured data — rows with fixed columns and defined types. But real applications rarely deal with perfectly structured data. A product might have a variable set of attributes. A user profile might contain nested preferences. An API response lands as a blob of JSON that you need to query without knowing its shape in advance.

Modern SQL databases solve this with native JSON support. You can store JSON as a column value and then query inside it, extract fields, filter on nested properties, and even expand JSON arrays into rows — all without leaving SQL.

What You'll Learn

By the end of this lesson you will write a single query that reaches inside a JSON column, filters rows on a value buried two levels down, and expands a JSON array into one row per element so you can aggregate over it. The skill that makes those queries trustworthy is knowing what happens when a path is wrong: json_extract does not raise an error for a path that does not exist, it returns NULL — so a misspelt path produces a filter that matches nothing and a report that is empty rather than broken.

This lesson is optional. The core path is complete without it; the extension tail exists for jobs that call for it, and it can be taken in any order. The capstone warehouse needs none of this — it stores every fact in a column, which is the right choice whenever the structure is known. You arrive able to filter rows on an expression and group over them (Filtering and Sorting, Aggregation and Grouping); what is new is that the expression reaches inside a text column that holds a document.

This lesson runs on SQLite's JSON functions. The function names here are SQLite's own — json_extract, json_each, json_group_array. PostgreSQL and MySQL solve the same problems with different spellings and, in PostgreSQL's case, dedicated json and jsonb column types rather than text; see SQLite documentation: JSON Functions And Operators for the set used here and PostgreSQL 16 documentation: JSON Functions and Operators for the PostgreSQL equivalents. The ideas carry across; the names do not.

Storing JSON in a Column

JSON is stored as text, but databases that support JSON functions understand its structure. The key insight is that you can store a flexible document inside a column and still query it precisely.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  attributes TEXT NOT NULL
);

INSERT INTO products (name, attributes) VALUES
  ('Laptop', '{"brand":"Dell","ram_gb":16,"storage_gb":512,"color":"silver"}'),
  ('Phone',  '{"brand":"Apple","ram_gb":8,"storage_gb":256,"color":"black","5g":true}'),
  ('Tablet', '{"brand":"Samsung","ram_gb":6,"storage_gb":128,"color":"white"}'),
  ('Monitor','{"brand":"LG","size_inches":27,"resolution":"4K","hdr":true}');

SELECT id, name, attributes FROM products;

Notice that each product has a different set of attributes. A monitor has no RAM, a phone has 5G support. A traditional schema would require nullable columns for every possible field or a separate product_attributes table. JSON lets you store exactly what each product needs.

Extracting Values with json_extract

The json_extract(json, path) function reads a value out of a JSON column. Paths start with $ to represent the root of the document, followed by dot notation for object keys.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  attributes TEXT NOT NULL
);

INSERT INTO products (name, attributes) VALUES
  ('Laptop', '{"brand":"Dell","ram_gb":16,"storage_gb":512,"color":"silver"}'),
  ('Phone',  '{"brand":"Apple","ram_gb":8,"storage_gb":256,"color":"black","5g":true}'),
  ('Tablet', '{"brand":"Samsung","ram_gb":6,"storage_gb":128,"color":"white"}'),
  ('Monitor','{"brand":"LG","size_inches":27,"resolution":"4K","hdr":true}');

SELECT
  name,
  json_extract(attributes, '$.brand')      AS brand,
  json_extract(attributes, '$.ram_gb')     AS ram_gb,
  json_extract(attributes, '$.color')      AS color
FROM products;

When a path does not exist for a given row — like $.ram_gb for the monitor — json_extract returns NULL. This makes it behave naturally with the rest of SQL's NULL semantics, and that is both the convenience and the hazard: a path that is absent and a path you spelled wrong are indistinguishable, because both produce NULL and neither produces an error.

CREATE TABLE p (id INTEGER PRIMARY KEY, attrs TEXT NOT NULL);
INSERT INTO p VALUES (1,'{"a":1}'),(2,'{"b":2}');

SELECT id, json_extract(attrs,'$.a') AS a FROM p ORDER BY id;
SELECT COUNT(*) AS matched FROM p WHERE json_extract(attrs,'$.a') = 1;

You can filter on extracted values just like any other expression:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  attributes TEXT NOT NULL
);

INSERT INTO products (name, attributes) VALUES
  ('Laptop', '{"brand":"Dell","ram_gb":16,"storage_gb":512,"color":"silver"}'),
  ('Phone',  '{"brand":"Apple","ram_gb":8,"storage_gb":256,"color":"black","5g":true}'),
  ('Tablet', '{"brand":"Samsung","ram_gb":6,"storage_gb":128,"color":"white"}'),
  ('Monitor','{"brand":"LG","size_inches":27,"resolution":"4K","hdr":true}');

SELECT name, json_extract(attributes, '$.ram_gb') AS ram_gb
FROM products
WHERE json_extract(attributes, '$.ram_gb') >= 8
ORDER BY ram_gb DESC;

Working with Nested JSON and Arrays

JSON documents can be deeply nested. Consider an order system where each order stores its line items as a JSON array:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  data TEXT NOT NULL
);

INSERT INTO orders (customer, data) VALUES
  ('Alice', '{
    "status": "shipped",
    "address": {"city": "Amsterdam", "country": "NL"},
    "items": [
      {"sku": "LP-001", "qty": 1, "price": 1299.00},
      {"sku": "KB-042", "qty": 2, "price": 79.50}
    ]
  }'),
  ('Bob', '{
    "status": "pending",
    "address": {"city": "Berlin", "country": "DE"},
    "items": [
      {"sku": "MN-007", "qty": 1, "price": 399.00}
    ]
  }');

SELECT
  customer,
  json_extract(data, '$.status')           AS status,
  json_extract(data, '$.address.city')     AS city,
  json_extract(data, '$.items[0].sku')     AS first_item_sku,
  json_extract(data, '$.items[0].price')   AS first_item_price
FROM orders;

Nested object keys use dot notation ($.address.city). Array elements use zero-based bracket notation ($.items[0]). You can chain them freely to reach any value in the document.

Chaining is where the silent-NULL hazard becomes a real bug rather than a curiosity, because a path that is one level too shallow is a plausible thing to write and returns nothing at all.

CREATE TABLE tickets (id INTEGER PRIMARY KEY, subject TEXT NOT NULL, meta TEXT NOT NULL);
INSERT INTO tickets (id, subject, meta) VALUES
(1,'Login broken','{"priority":"high","contact":{"name":"Sara","country":"DE"}}'),
(2,'Slow dashboard','{"priority":"medium","contact":{"name":"Tom","country":"NL"}}'),
(3,'Payment failing','{"priority":"high","contact":{"name":"Jan","country":"DE"}}'),
(4,'Wrong currency','{"priority":"low","contact":{"name":"Mei","country":"NL"}}');

SELECT id, subject, json_extract(meta, '$.contact.name') AS contact
FROM tickets
WHERE json_extract(meta, '$.country') = 'DE'
ORDER BY id;

4 row(s) affected
id | subject | contact
----------------------
1 | Login broken | Sara
3 | Payment failing | Jan

Expanding Arrays with json_each

json_extract works well for specific paths, but what if you need to process every element in a JSON array? The json_each table-valued function expands a JSON array into individual rows, one per element.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  data TEXT NOT NULL
);

INSERT INTO orders (customer, data) VALUES
  ('Alice', '{
    "status": "shipped",
    "items": [
      {"sku": "LP-001", "qty": 1, "price": 1299.00},
      {"sku": "KB-042", "qty": 2, "price": 79.50}
    ]
  }'),
  ('Bob', '{
    "status": "pending",
    "items": [
      {"sku": "MN-007", "qty": 1, "price": 399.00}
    ]
  }');

SELECT
  o.customer,
  json_extract(item.value, '$.sku')                      AS sku,
  json_extract(item.value, '$.qty')                      AS qty,
  json_extract(item.value, '$.price')                    AS unit_price,
  json_extract(item.value, '$.qty') *
    json_extract(item.value, '$.price')                  AS line_total
FROM orders o,
     json_each(o.data, '$.items') AS item;

json_each is joined to the orders table with a comma, which acts as a lateral join — for each order row, it produces one row per item in that order's array. The value column in json_each holds the JSON for each element. The result looks like a normalized table even though the data was stored as nested JSON.

That phrase — one row per item — has a consequence for orders whose array has no items in it.

CREATE TABLE d (id INTEGER PRIMARY KEY, doc TEXT NOT NULL);
INSERT INTO d VALUES (1,'{"tags":["x","y"]}'),(2,'{"tags":[]}'),(3,'{"other":1}');

SELECT d.id, t.value
FROM d, json_each(d.doc,'$.tags') AS t
ORDER BY d.id, t.value;

You can aggregate over the expanded rows just as you would with any table:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  data TEXT NOT NULL
);

INSERT INTO orders (customer, data) VALUES
  ('Alice', '{
    "status": "shipped",
    "items": [
      {"sku": "LP-001", "qty": 1, "price": 1299.00},
      {"sku": "KB-042", "qty": 2, "price": 79.50}
    ]
  }'),
  ('Bob', '{
    "status": "pending",
    "items": [
      {"sku": "MN-007", "qty": 1, "price": 399.00}
    ]
  }');

SELECT
  o.customer,
  COUNT(item.value)                                        AS item_count,
  SUM(json_extract(item.value, '$.qty') *
      json_extract(item.value, '$.price'))                 AS order_total
FROM orders o,
     json_each(o.data, '$.items') AS item
GROUP BY o.id, o.customer;

Building JSON from Relational Data

SQL can also construct JSON output from regular table data using json_object and json_array. This is useful when your application expects JSON responses but your data lives in a normalized schema.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
CREATE TABLE tags  (user_id INTEGER, tag TEXT);

INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO users VALUES (2, 'Bob',   'bob@example.com');
INSERT INTO tags  VALUES (1, 'admin'), (1, 'editor'), (2, 'viewer');

SELECT
  json_object(
    'id',    u.id,
    'name',  u.name,
    'email', u.email,
    'tags',  json_group_array(t.tag)
  ) AS user_json
FROM users u
LEFT JOIN tags t ON t.user_id = u.id
GROUP BY u.id;

json_object builds a JSON object from alternating key-value arguments. json_group_array is an aggregate function — like GROUP_CONCAT but it produces a proper JSON array. The result is a complete JSON document per user, assembled entirely in SQL.

One measured trap in that pattern

A LEFT JOIN feeding json_group_array does not produce an empty array for a row with no matches. Measured on this engine: a user with no tags comes back with a tags value of [null] — a one-element array holding a JSON null, because the LEFT JOIN manufactured one row with a NULL tag and the aggregate faithfully collected it.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE tags  (user_id INTEGER, tag TEXT);
INSERT INTO users VALUES (1,'Alice'),(2,'Bob'),(3,'Cy');
INSERT INTO tags VALUES (1,'admin'),(1,'editor'),(2,'viewer');

SELECT u.name, json_group_array(t.tag) AS tags
FROM users u
LEFT JOIN tags t ON t.user_id = u.id
GROUP BY u.id
ORDER BY u.id;

Cy comes back with [null], and a consumer that expects a list of strings will read that as one unknown tag rather than none. The fix is the same shape as every other LEFT JOIN guard: filter the nulls out of the aggregate, with json_group_array(t.tag) FILTER (WHERE t.tag IS NOT NULL), which gives an empty array instead.

Building One Document per Group

The last shape worth having is a single JSON document assembled per group, with a nested array inside it. The clauses go together the way they always do.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE tags (user_id INTEGER NOT NULL, tag TEXT NOT NULL);
INSERT INTO users VALUES (1,'Alice'),(2,'Bob'),(3,'Cy');
INSERT INTO tags VALUES (1,'admin'),(1,'editor'),(2,'viewer');
SELECT json_object('name', u.name, 'tags', json_group_array(t.tag)) AS doc

Every arrangement below runs against that projection, which is already given.

Try It Yourself

A shop stores each order's line items as a JSON array inside the order row. The starter below reports one row per order — which is not what the business asked for, because it wants the number of line items and the order total, and both of those live inside the array.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, data TEXT NOT NULL);
INSERT INTO orders (id, customer, data) VALUES
(1,'Alice','{"items":[{"sku":"LP-001","qty":1,"price_cents":129900},{"sku":"KB-042","qty":2,"price_cents":7950}]}'),
(2,'Bob','{"items":[{"sku":"MN-007","qty":1,"price_cents":39900}]}'),
(3,'Cy','{"items":[{"sku":"KB-042","qty":3,"price_cents":7950},{"sku":"LP-001","qty":1,"price_cents":129900}]}');

-- TODO 1: expand each order's items array into one row per item, by joining
--         orders to json_each over the $.items path with a comma.
--         Alice's array holds 2 items, so Alice must produce 2 rows before grouping.
-- TODO 2: add a total_cents column that sums qty times price_cents across the
--         expanded rows. Alice is 1 x 129900 plus 2 x 7950, which is 145800.
-- TODO 3: order by total_cents descending, breaking ties by customer name.
SELECT o.customer, COUNT(*) AS lines_count
FROM orders o
GROUP BY o.id, o.customer
ORDER BY o.customer;

3 row(s) affected
customer | lines_count | total_cents
------------------------------------
Cy | 2 | 153750
Alice | 2 | 145800
Bob | 1 | 39900

Once it passes, try two variations and predict each before running. First, change json_each(o.data, '$.items') to json_each(o.data, '$.lines') — no order has a lines key, so every order expands to zero rows and the report comes back empty rather than erroring, which is the same silent-path failure as the debug block above. Second, keep the correct path but change COUNT(*) to SUM(json_extract(item.value, '$.qty')) — the column now reports units ordered rather than distinct line items, so Cy goes from 2 to 4 while Bob stays at 1.

The Same Count, Two Shapes

CREATE TABLE d (id INTEGER PRIMARY KEY, doc TEXT NOT NULL);
INSERT INTO d VALUES (1,'{"tags":["x","y"]}'),(2,'{"tags":[]}'),(3,'{"other":1}');

-- Shape A: expand, then count the rows
SELECT d.id, COUNT(t.value) AS n
FROM d LEFT JOIN json_each(d.doc,'$.tags') AS t
GROUP BY d.id ORDER BY d.id;

-- Shape B: ask for the length
SELECT d.id, json_array_length(d.doc,'$.tags') AS n
FROM d ORDER BY d.id;

Key Takeaways

• JSON columns store semi-structured data in a text column that SQL can still query with full precision
• json_extract(column, '$.path') reads a value from a JSON document; missing paths return NULL
• Paths use $ for the root, dot notation for object keys, and [n] for zero-based array indexing
• json_each is a table-valued function that expands a JSON array into rows, enabling joins and aggregations
• json_object and json_group_array build JSON output from relational data — useful for API-shaped responses
• A wrong path never errors — it returns NULL, every comparison against it is NULL, and the filter built on it silently keeps nothing; select a path before you filter on it
• json_group_array over a LEFT JOIN with no match returns [null] rather than []; add FILTER (WHERE col IS NOT NULL) when you want the empty array
• JSON in SQL is a pragmatic tool for variable-structure data; it should not replace a normalized schema where the structure is known and stable

Pro Tip: Reach for JSON columns when the data is truly variable — user-defined fields, third-party API payloads, feature flags with arbitrary shapes. For data you query, filter, or join on frequently, extract it into a proper column so the database can index it. SQLite has generated columns, and a generated column can be indexed — see SQLite documentation: Generated Columns. Putting those two facts together gives you a way to index a JSON field: declaring ram_gb INTEGER GENERATED ALWAYS AS (json_extract(attributes, '$.ram_gb')) and then indexing ram_gb gets you typed access to the field without duplicating your insert logic. Run on this lesson's engine, that column reports 16 and 8 for the two documents that carry the key and None for the one that does not, and EXPLAIN QUERY PLAN for a lookup on it reports SEARCH using the index rather than a scan.

Next Steps

JSON is the second of four optional extensions, and the core path remains complete without any of them. The next one introduces triggers and automation — logic the database runs by itself when data changes, so business rules and audit trails do not depend on every caller remembering them.