SQLite JSON Functions: Extract, Validate and Expand Arrays

Extract scalar values

With a single path, json_extract returns a SQL scalar for a JSON scalar. This example returns mobile and integer 3, rather than JSON strings with extra quotes.

SELECT json_extract('{"device":"mobile","count":3}','$.device') AS device,json_extract('{"count":3}','$.count') AS count;

Handle invalid input deliberately

Malformed JSON can raise an error. A CASE guard lets this example return 1 for the valid document and NULL for the invalid one. Missing paths and JSON null can both produce SQL NULL; use json_type when you need to distinguish them.

WITH docs(id,body) AS (VALUES(1,'{"n":1}'),(2,'not json')) SELECT id,CASE WHEN json_valid(body) THEN json_extract(body,'$.n') END AS n FROM docs ORDER BY id;

Expand an array into rows

json_each is a table-valued function. The query returns keys 0 and 1 paired with red and blue. Use it in FROM or a join, not as a scalar SELECT expression.

SELECT key,value FROM json_each('["red","blue"]') ORDER BY key;

Practice with event properties

The product analytics dataset stores device and value properties in event JSON. Follow its exercises to group by device, count daily activity and rank event types by country. Keep JSON values separate from SQL syntax rather than concatenating untrusted text into a query.