SQL - JSON

Overview

Modern engines offer JSON functions. MySQL and SQL Server have rich built-ins; SQLite supports JSON via the JSON1 extension (may not be enabled in every build).

At a glance

Prefer typed columns for queryable attributes; store JSON for flexible or sparse fields.
Use JSON path functions (JSON_EXTRACT/JSON_VALUE) sparingly on hot paths; index computed columns if supported.
Validate JSON on write to avoid malformed payloads.

-- JSON in MySQL
SET @doc = '{"name":"Ada","skills":["sql","python"],"age":33}';
SELECT JSON_EXTRACT(@doc, '$.name') AS name,
       JSON_UNQUOTE(JSON_EXTRACT(@doc,'$.skills[0]')) AS skill0,
       JSON_EXTRACT(@doc, '$.age') AS age;

-- Update a key
SELECT JSON_SET(@doc, '$.age', 34) AS updated;

-- JSON in SQL Server
DECLARE @doc nvarchar(max) = N'{"name":"Ada","skills":["sql","python"],"age":33}';
SELECT JSON_VALUE(@doc, '$.name') AS name,
       JSON_QUERY(@doc, '$.skills') AS skills,
       JSON_VALUE(@doc, '$.age') AS age;

-- OPENJSON example
SELECT [key], value
FROM OPENJSON(@doc, '$.skills');

-- SQLite with JSON1 (if enabled)
SELECT json_extract('{"name":"Ada","age":33}', '$.name') AS name;
-- Note: JSON1 may not be available in all builds.

Example Input

{
  "name": "Ada",
  "skills": ["sql", "python"],
  "age": 33
}

Expected Output (conceptual)

name	skill0	age
Ada	sql	33

« Prev: SQL - PIVOT/UNPIVOT Next: SQL - Full-Text Search »