JSON — SQL Technical Interview Questions & Answers

JSON

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.

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

Code Examples for JSON

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.

How to Explain JSON to an Interviewer

When JSON in MySQL?

Optional extra fields Not a replacement for tables