JSON in SQL: SQLite and PostgreSQL

When a JSON column makes sense, SQLite's `json_extract`, `->`, `->>`, `json_each` and `json_group_array`, PostgreSQL's `json` vs `jsonb`, its operators and GIN indexes — and when JSON ruins a schema.

  • Advanced
  • 22 min
  • 12 questions
  • Lesson 21 of 28

In this lesson you will learn

  • Store JSON as checked text in SQLite and read values with json_extract, -> and ->>
  • Unnest arrays into rows with json_each and build JSON with json_object and json_group_array
  • Explain PostgreSQL's json and jsonb, the @> and ? operators and GIN indexes
  • Decide which data belongs in JSON and which in ordinary columns

In a shop, a laptop has memory, a monitor has a screen size, and headphones are wireless or not. Give every property its own column and the table turns into hundreds of mostly empty columns. On top of that, online services send data as JSON. That is why modern relational databases “understand” JSON: SQLite ships JSON functions, PostgreSQL has the json and jsonb types, MySQL has a JSON type. The lesson «PostgreSQL and MySQL in practice» touched on JSON columns briefly; here we go deep — reading, unnesting arrays, building JSON, indexing and, most importantly, when not to use JSON.

Storing JSON: a checked text column

SQLite has no separate JSON type: the document is stored in a TEXT column. To keep rubbish out we add CHECK (json_valid(specs)) — json_valid checks strict, standard JSON. The core, stable data (name, price) stays in products, and the variable properties go into a separate product_specs table as JSON. Inside the document you address values with a path:

JSON pathDefinition

$ is the whole document, .key an object field, [0] an array element (counting from 0). $.ports[0] is the first port; in SQLite $.ports[#-1] is the last element. If the path doesn't exist, the result is NULL, with no error.

SQL
CREATE TABLE product_specs (
  product_id INTEGER PRIMARY KEY REFERENCES products(id),
  specs      TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
  (1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
  (2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
  (3, '{"color": "black", "wireless": true}'),
  (9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');

SELECT p.name,
       json_extract(s.specs, '$.color') AS color,
       s.specs ->> '$.ram_gb'           AS ram_gb,
       s.specs ->> '$.ports[0]'         AS first_port
FROM product_specs AS s
JOIN products AS p ON p.id = s.product_id
ORDER BY p.id;
Expected output
name | color | ram_gb | first_port
Laptop | silver | 16 | usb-c
Smartphone | black | 8 | usb-c
Headphones | black | NULL | NULL
Monitor | NULL | NULL | hdmi
A missing key (ram_gb for the headphones) simply gives NULL.
SQL
CREATE TABLE product_specs (
  product_id INTEGER PRIMARY KEY,
  specs      TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES (5, '{"color": "blue", "pens": 12,}');
Expected output
Error: CHECK constraint failed: json_valid(specs)
The trailing comma is not valid JSON — the CHECK rejects the row.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

Reading values: json_extract, -> and ->>

json_extract(doc, path) and doc ->> path return an ordinary SQL value: text, a number or NULL. doc -> path returns a piece of JSON: a string comes with quotes, an array with brackets. For a simple key you may leave out $.: specs ->> 'color'.

SQL
SELECT '{"color": "black", "ports": ["usb-c"]}' -> '$.color'  AS one_arrow,
       '{"color": "black", "ports": ["usb-c"]}' ->> '$.color' AS two_arrows,
       '{"color": "black", "ports": ["usb-c"]}' -> 'ports'    AS ports_json,
       typeof('{"ram_gb": 8}' -> '$.ram_gb')  AS type_one,
       typeof('{"ram_gb": 8}' ->> '$.ram_gb') AS type_two;
Expected output
one_arrow | two_arrows | ports_json | type_one | type_two
"black" | black | ["usb-c"] | text | integer

Predict the result

Problem

In one product's document the memory was written as text by mistake: {"ram_gb": "4"}. Will WHERE specs ->> 'ram_gb' > 10 find this product?

Show solutionHide solution

Solution

1) ->> returns the text '4', because in the JSON the value is in quotes.
2) In SQLite any text is “greater” than any number (the ordering of types).
3) So yes, it will — the 4 GB product lands in the “more than 10” list. Fix: CAST(specs ->> 'ram_gb' AS INTEGER) > 10, or check the type on write with CHECK (json_type(specs, '$.ram_gb') = 'integer').
SQL
SELECT '{"ram_gb": "4"}' ->> '$.ram_gb' > 10                   AS text_check,
       CAST('{"ram_gb": "4"}' ->> '$.ram_gb' AS INTEGER) > 10 AS number_check,
       '{"ram_gb": 4}' ->> '$.colour'                         AS typo;
Expected output
text_check | number_check | typo
1 | 0 | NULL
A misspelt key (colour) gives no error either — just NULL.

If a JSON field is searched often, create an expression index. Careful: SQLite uses the index only when the query's expression matches the indexed one exactly. An index on json_extract(specs, '$.color') is not used by specs ->> '$.color' = ... (checked: SCAN), but the same expression works:

SQL
CREATE TABLE product_specs (
  product_id INTEGER PRIMARY KEY REFERENCES products(id),
  specs      TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
  (1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
  (2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
  (3, '{"color": "black", "wireless": true}'),
  (9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');

CREATE INDEX idx_specs_color ON product_specs (specs ->> '$.color');

EXPLAIN QUERY PLAN
SELECT product_id FROM product_specs WHERE specs ->> '$.color' = 'black';
Expected output
id | parent | notused | detail
3 | 0 | 53 | SEARCH product_specs USING COVERING INDEX idx_specs_color (<expr>=?)

Unnesting arrays and building JSON

json_eachDefinition

A table-valued function: it returns one row per element of a JSON array (or object); its main columns are key (index or key), value and type. It is written in FROM like a table and may refer to the columns of the table before it.

SQL
CREATE TABLE product_specs (
  product_id INTEGER PRIMARY KEY REFERENCES products(id),
  specs      TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
  (1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
  (2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
  (3, '{"color": "black", "wireless": true}'),
  (9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');

SELECT p.name, j.key AS idx, j.value AS port
FROM product_specs AS s
JOIN products AS p ON p.id = s.product_id,
     json_each(s.specs, '$.ports') AS j
ORDER BY p.id, j.key;
Expected output
name | idx | port
Laptop | 0 | usb-c
Laptop | 1 | hdmi
Smartphone | 0 | usb-c
Monitor | 0 | hdmi
Monitor | 1 | dp
The headphones have no ports and produce no rows — json_each behaves like an inner join.

Once the array is unnested into rows, ordinary SQL works: grouping, joins, filters. Which port appears on how many products?

SQL
CREATE TABLE product_specs (
  product_id INTEGER PRIMARY KEY REFERENCES products(id),
  specs      TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
  (1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
  (2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
  (3, '{"color": "black", "wireless": true}'),
  (9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');

SELECT j.value AS port, COUNT(*) AS products
FROM product_specs AS s, json_each(s.specs, '$.ports') AS j
GROUP BY j.value
ORDER BY products DESC, port;
Expected output
port | products
hdmi | 2
usb-c | 2
dp | 1

The opposite direction — from a table to JSON — is needed for API responses. json_object(key, value, ...) builds one object, the aggregate json_group_array(x) builds an array from all values of a group (since SQLite 3.44 you can write ORDER BY inside it), and json_group_object(key, value) builds an object. The orders of customers from Azerbaijan, one JSON per customer:

SQL
SELECT c.name,
       json_object('orders', COUNT(*),
                   'products', json_group_array(p.name ORDER BY o.order_date))
         AS summary
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products  AS p ON p.id = o.product_id
WHERE c.country = 'Azerbaijan'
GROUP BY c.id
ORDER BY c.id;
Expected output
name | summary
Anar Mustafayev | {"orders":3,"products":["Laptop","Headphones","Notebook"]}
Lalə Əhmədova | {"orders":2,"products":["Notebook","Headphones"]}
Zəhra Hüseynli | {"orders":2,"products":["Smartphone","Water bottle"]}

Four functions change a document: json_set (replaces if present, adds if not), json_insert (only adds), json_replace (only replaces) and json_remove. The path $.ports[#] appends to the end of an array. Each function returns a new document, so you write UPDATE ... SET specs = json_set(specs, ...):

SQL
CREATE TABLE product_specs (
  product_id INTEGER PRIMARY KEY REFERENCES products(id),
  specs      TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
  (1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
  (2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
  (3, '{"color": "black", "wireless": true}'),
  (9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');

UPDATE product_specs
SET specs = json_set(specs, '$.ram_gb', 32, '$.ports[#]', 'sd-card')
WHERE product_id = 1;
UPDATE product_specs
SET specs = json_remove(specs, '$.color')
WHERE product_id = 2;

SELECT product_id, specs FROM product_specs WHERE product_id <= 2 ORDER BY product_id;
Expected output
product_id | specs
1 | {"ram_gb":32,"color":"silver","ports":["usb-c","hdmi","sd-card"]}
2 | {"ram_gb":8,"ports":["usb-c"]}

PostgreSQL: json vs jsonb, operators, GIN

PostgreSQL has two types. json stores the text exactly as given — whitespace, key order and duplicate keys included — and parses it again on every access. jsonb is converted to a binary form on write: of duplicate keys the last one wins, keys are reordered, reading is fast, and indexes and the “contains” operators are supported. The rule is simple: almost always jsonb; json only when the exact text must be kept. The code in this section was checked in PGlite (PostgreSQL 18).

SQL
SELECT '{"b": 1,  "a": 2, "a": 3}'::json  AS as_json,
       '{"b": 1,  "a": 2, "a": 3}'::jsonb AS as_jsonb;

Expected output

          as_json          |     as_jsonb
---------------------------+------------------
 {"b": 1,  "a": 2, "a": 3} | {"a": 3, "b": 1}
(1 row)
OperatorMeaningExample
-> / ->>by key or index: JSON / textspecs ->> 'color'
#> / #>>by a path array: JSON / textspecs #>> '{ports,0}'
@>the left side contains the right sidespecs @> '{"color": "black"}'
?this key (or this string in an array) existsspecs ? 'wireless'
|| / -merge / remove a keyspecs - 'color'
SQL
CREATE TABLE product_specs (product_id int PRIMARY KEY, specs jsonb NOT NULL);
INSERT INTO product_specs VALUES
  (1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
  (2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
  (3, '{"color": "black", "wireless": true}'),
  (9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');
CREATE INDEX idx_specs ON product_specs USING GIN (specs);

SELECT product_id,
       specs -> 'ports'              AS ports,
       specs #>> '{ports,0}'         AS first_port,
       (specs ->> 'ram_gb')::int * 2 AS double_ram
FROM product_specs
WHERE specs @> '{"color": "black"}'
ORDER BY product_id;

SELECT product_id FROM product_specs
WHERE specs -> 'ports' ? 'hdmi'
ORDER BY product_id;

Expected output

 product_id |   ports   | first_port | double_ram
------------+-----------+------------+------------
          2 | ["usb-c"] | usb-c      |         16
          3 |           |            |
(2 rows)

 product_id
------------
          1
          9
(2 rows)
->> returns text, so it is cast with ::int before arithmetic.

GIN (Generalized Inverted Index) is an “inverted index” of all keys and values inside the documents — like the subject index at the back of a book. The default operator class (jsonb_ops) speeds up @>, ?, ?| and ?&; jsonb_path_ops is smaller and faster but supports only @> (and the jsonpath operators). For specs ->> 'color' = 'black' GIN doesn't help — that needs an ordinary B-tree expression index: CREATE INDEX ON product_specs ((specs ->> 'color')). The table is tiny, so we “force” the planner towards indexes with SET enable_seqscan = off:

SQL
SET enable_seqscan = off;

EXPLAIN (COSTS OFF)
SELECT product_id FROM product_specs WHERE specs @> '{"color": "black"}';

EXPLAIN (COSTS OFF)
SELECT product_id FROM product_specs WHERE specs ->> 'color' = 'black';

Expected output

                         QUERY PLAN
------------------------------------------------------------
 Bitmap Heap Scan on product_specs
   Recheck Cond: (specs @> '{"color": "black"}'::jsonb)
   ->  Bitmap Index Scan on idx_specs
         Index Cond: (specs @> '{"color": "black"}'::jsonb)
(4 rows)

                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on product_specs
   Disabled: true
   Filter: ((specs ->> 'color'::text) = 'black'::text)
(3 rows)
@> goes through the GIN index, while ->> = is still a sequential scan (Disabled: true — the planner has no other way).
TaskSQLitePostgreSQL (jsonb)MySQL 8
Read a valued ->> '$.a'd ->> 'a'd ->> '$.a'
Array to rowsjson_eachjsonb_array_elementsJSON_TABLE
Array from a groupjson_group_arrayjsonb_aggJSON_ARRAYAGG
Build an objectjson_objectjsonb_build_objectJSON_OBJECT
Change a fieldjson_setjsonb_setJSON_SET
Indexexpression indexGIN or an expression indexindex on a generated column

When JSON is a bad idea, and where it fits

  • The field is present in every row (price, customer, date) — that is an ordinary column.
  • The field refers to another table — inside JSON there are no foreign keys and no join index; build a junction table instead of id arrays.
  • You need NOT NULL, CHECK, UNIQUE — types and rules inside JSON aren't checked by themselves.
  • One field of a large document changes often — the whole document is rewritten every time.

If some JSON field has become “popular”, you can promote it to a generated column: its value is computed from the JSON automatically, but it is indexed like an ordinary column and looks simple in queries. SQLite lets ALTER TABLE add only a VIRTUAL (not stored) one:

SQL
CREATE TABLE product_specs (
  product_id INTEGER PRIMARY KEY REFERENCES products(id),
  specs      TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
  (1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
  (2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
  (3, '{"color": "black", "wireless": true}'),
  (9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');

ALTER TABLE product_specs
  ADD COLUMN color TEXT GENERATED ALWAYS AS (specs ->> '$.color') VIRTUAL;
CREATE INDEX idx_specs_color ON product_specs (color);

SELECT product_id, color FROM product_specs WHERE color = 'black' ORDER BY product_id;
Expected output
product_id | color
2 | black
3 | black

Mini project: importing orders from an API. The healthiest place for JSON is the border of the system: a service sends JSON, and we turn it into ordinary tables right away.

  1. Keep the raw JSON

    The incoming request body goes into api_orders with a json_valid check — if something goes wrong, you still have the original.

  2. Unnest the array

    json_each(a.body, '$.items') gives one row per item.

  3. Write to the relational table

    INSERT INTO orders ... SELECT — the ->> values land in ordinary columns.

  4. Check with plain SQL

    Customer and product names and amounts via JOIN.

SQL
CREATE TABLE api_orders (id INTEGER PRIMARY KEY, body TEXT CHECK (json_valid(body)));
INSERT INTO api_orders (body) VALUES
  ('{"customer_id": 4, "date": "2025-08-01",
     "items": [{"product_id": 3, "qty": 2}, {"product_id": 5, "qty": 3}]}'),
  ('{"customer_id": 7, "date": "2025-08-02",
     "items": [{"product_id": 10, "qty": 1}]}');

INSERT INTO orders (customer_id, product_id, quantity, order_date)
SELECT a.body ->> '$.customer_id', i.value ->> '$.product_id',
       i.value ->> '$.qty',        a.body ->> '$.date'
FROM api_orders AS a, json_each(a.body, '$.items') AS i;

SELECT o.id, c.name AS customer, p.name AS product, o.quantity,
       ROUND(o.quantity * p.price, 2) AS amount
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products  AS p ON p.id = o.product_id
WHERE o.id > 12
ORDER BY o.id;
Expected output
id | customer | product | quantity | amount
13 | Olga Ivanova | Headphones | 2 | 241
14 | Olga Ivanova | Pen set | 3 | 23.7
15 | Mehmet Kaya | Chess set | 1 | 42
Two JSON documents became three ordinary order rows.

Notice that JSON lived only at the border of the system — after the import the data is relational again, and foreign keys, indexes and plain GROUP BY all work. This is the healthiest use of JSON in SQL.

Exercise

From the product_specs table in the starter code, show the name and the number of ports (ports, with json_array_length) of every product that has a ports array. Sort by the number of ports descending, then by name.

Exercise · SQL
CREATE TABLE product_specs (
  product_id INTEGER PRIMARY KEY REFERENCES products(id),
  specs      TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
  (1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
  (2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
  (3, '{"color": "black", "wireless": true}'),
  (9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');

SELECT p.name
FROM product_specs AS s
JOIN products AS p ON p.id = s.product_id;
Expected output
name | ports
Laptop | 2
Monitor | 2
Smartphone | 1

Exercise

Build a category list for an API: for each category, the number of products (products) and a JSON array of their names in alphabetical order (names). Sort by category.

Exercise · SQL
SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
ORDER BY category;
Expected output
category | products | names
Accessories | 2 | ["Backpack","Water bottle"]
Electronics | 4 | ["Headphones","Laptop","Monitor","Smartphone"]
Games | 1 | ["Chess set"]
Home | 1 | ["Desk lamp"]
Stationery | 2 | ["Notebook","Pen set"]

Remember

  • SQLite stores JSON as TEXT (or a BLOB via jsonb()); CHECK (json_valid(...)) keeps rubbish out.
  • -> returns a piece of JSON, ->> and json_extract an ordinary SQL value; a missing path gives NULL.
  • json_each unnests an array into rows; json_object and json_group_array build JSON from a table.
  • In PostgreSQL almost always use jsonb: @> and ? are sped up by a GIN index, ->> = needs an expression index.
  • Stable, related and validated fields stay in columns; JSON is for truly variable attributes and the system's border.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

12 questions. Every correct answer earns XP.

Question 1 / 12

Question 1 / 12. What does '{"color": "red"}' -> '$.color' return in SQLite?