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.

This lesson uses SQLite's JSON functions, which are widely supported and map closely to what you will find in PostgreSQL and MySQL.

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.

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.

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.

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.

Try It Yourself

A support system stores tickets with JSON metadata. Each ticket includes a priority level, tags, and contact details. Query the data to find all high-priority tickets from a specific country, expanding their tags into individual rows.

CREATE TABLE tickets (
  id INTEGER PRIMARY KEY,
  subject TEXT NOT NULL,
  meta TEXT NOT NULL
);

INSERT INTO tickets (subject, meta) VALUES
  ('Login broken',    '{"priority":"high","contact":{"name":"Sara","country":"DE"},"tags":["auth","urgent"]}'),
  ('Slow dashboard',  '{"priority":"medium","contact":{"name":"Tom","country":"NL"},"tags":["performance"]}'),
  ('Payment failing', '{"priority":"high","contact":{"name":"Jan","country":"DE"},"tags":["payment","urgent"]}'),
  ('Wrong currency',  '{"priority":"low","contact":{"name":"Mei","country":"NL"},"tags":["billing"]}');

-- Find high-priority tickets from Germany and list each tag as a separate row
SELECT
  t.id,
  t.subject,
  json_extract(t.meta, '$.contact.name')    AS contact,
  tag.value                                  AS tag
FROM tickets t,
     json_each(t.meta, '$.tags') AS tag
WHERE json_extract(t.meta, '$.priority') = 'high'
  AND json_extract(t.meta, '$.contact.country') = 'DE'
ORDER BY t.id, tag.value;

Try modifying the query to also return tickets where any tag equals 'urgent', regardless of country.

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
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. Many databases let you create generated columns like ram_gb INTEGER GENERATED ALWAYS AS (json_extract(attributes, '$.ram_gb')) so you get indexed, typed access to a JSON field without duplicating your insert logic.

Next Steps

With JSON handling covered, the next lesson introduces triggers and events — automated procedures that fire when data changes, letting the database enforce business rules and maintain audit trails without relying on application code.