SQL · Theory

JSON

Theory

321/439

JSON

MySQL 5.7+ has a JSON type. Store a document: extra JSON. JSON_EXTRACT(extra, '$.city') or extra->>'$.city' reads a field. Still not MongoDB — you don’t get flexible nested queries as the main model. Use JSON for optional blobs, not the whole college schema.

Generated columns can index a JSON path if you must search it often. Otherwise keep city as a real VARCHAR.

Trap — storing everything in one JSON column and then wondering why WHERE is painful.

On the example next to this theory: JSON: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

extra JSON, extra->>'$.city'. Prefer a city column if you filter on it.

JSON — sample query

-- JSON
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

JSON: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefJSON type for a document blob. ->> extracts text.
RuleReal columns for things you search. JSON for extras.
TrapWhole schema as one JSON column.

SQL · Theory

JSON

← All stacks

Theory

321/439

JSON

Generated columns can index a JSON path if you must search it often. Otherwise keep city as a real VARCHAR.

Trap — storing everything in one JSON column and then wondering why WHERE is painful.

On the example next to this theory: JSON: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

extra JSON, extra->>'$.city'. Prefer a city column if you filter on it.

JSON — sample query

-- JSON
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

JSON: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefJSON type for a document blob. ->> extracts text.
RuleReal columns for things you search. JSON for extras.
TrapWhole schema as one JSON column.

JSON

JSON — sample query

Short notes

Questions

JSON

JSON — sample query

Short notes

Questions