JSON Paths, json_extract, -> / ->>, and Value Construction

Learning outcomes

Once a JSON document is deliberately part of the schema, path expressions become its read interface. The important beginner habit is to inspect one level at a time and always ask whether the result is JSON text or an ordinary SQL scalar.

Start with one readable document

Use this nested document as the chapter's reference shape. Pretty formatting is for humans; SQLite accepts the equivalent compact representation.

SELECT json_pretty(json('{"firmware":{"version":"3.7.2","channel":"stable"},"sensors":[{"kind":"temperature","unit":"C"},{"kind":"vibration","unit":"mm/s"}],"calibration":{"due":"2026-10-01","certified":true},"alias":null}'));

The logical structure is an object at the root, containing nested objects, an array of sensor objects, and a JSON null value.

Path syntax: root, members, and arrays

A SQLite JSON path begins with exactly one $. A dot selects an object label and square brackets select an array element. Array indexes start at zero. The special #-1 form selects the final array element and works as part of a path on the course baseline.

WITH sample(doc) AS (  VALUES ('{"firmware":{"version":"3.7.2"},"sensors":[{"kind":"temperature"},{"kind":"vibration"}]}'))SELECT json_extract(doc,'$.firmware') AS firmware_object,       json_extract(doc,'$.firmware.version') AS firmware_version,       json_extract(doc,'$.sensors[0].kind') AS first_sensor,       json_extract(doc,'$.sensors[#-1].kind') AS last_sensorFROM sample;

json_extract(): container JSON versus SQL scalar

With one path, json_extract() returns SQL NULL/TEXT/INTEGER/REAL for JSON null, strings, booleans, and numbers, while arrays and objects come back as JSON text. With multiple paths, the result is a JSON array represented as TEXT. This is an SQLite behavior worth knowing because similarly named functions in other products can return different types.

WITH sample(doc) AS (  VALUES ('{"name":"Pump 7","count":3,"enabled":true,"alias":null,"tags":["pump","critical"]}'))SELECT json_extract(doc,'$.name') AS name,       typeof(json_extract(doc,'$.name')) AS name_type,       json_extract(doc,'$.count') AS count_value,       typeof(json_extract(doc,'$.count')) AS count_type,       json_extract(doc,'$.enabled') AS enabled,       typeof(json_extract(doc,'$.enabled')) AS enabled_type,       json_extract(doc,'$.alias') AS alias_value,       json_extract(doc,'$.tags') AS tags_jsonFROM sample;

Expected runtime types are TEXT for name, INTEGER for count and true (1), SQL NULL for JSON null, and TEXT containing JSON for the tags array.

-> versus ->>: same location, different representation

SQLite added the extraction operators in 3.38.0. The left side may be JSON text or JSONB. -> returns an RFC-8259 JSON text representation of the selected component; ->> returns an ordinary SQL scalar representation. For a JSON string, that difference means quotes versus dequoted text. For JSON null it means the text null versus SQL NULL.

WITH sample(doc) AS (  VALUES ('{"name":"Pump 7","count":3,"alias":null,"tags":["pump","critical"]}'))SELECT doc -> '$.name' AS name_json,       doc ->> '$.name' AS name_sql,       doc -> '$.count' AS count_json,       typeof(doc ->> '$.count') AS count_sql_type,       doc -> '$.alias' AS alias_json,       doc ->> '$.alias' AS alias_sql,       doc -> '$.tags' AS tags_jsonFROM sample;

Direct negative integer operands such as doc -> -1 were added in SQLite 3.47.0. For broader compatibility, a path like '$[#-1]' communicates the intent explicitly.

Missing path and JSON null are not the same fact

json_extract() returns SQL NULL both when a selected JSON value is JSON null and when the path does not exist. Use json_type() when that distinction matters: it returns the text null for a JSON null, but SQL NULL for a missing path.

WITH sample(doc) AS (VALUES ('{"alias":null}'))SELECT json_extract(doc,'$.alias') AS alias_value,       json_type(doc,'$.alias') AS alias_json_type,       json_extract(doc,'$.owner') AS owner_value,       json_type(doc,'$.owner') AS owner_json_typeFROM sample;

An API contract may care deeply about “field explicitly present with null” versus “field absent”. Do not use one extraction result to erase that distinction accidentally.

Construct values without string concatenation

Construction helpers understand SQL values. Ordinary SQL text passed as a value becomes a JSON string, even if it looks like JSON. If a value comes directly from another JSON function—or from ->—SQLite understands it as JSON structure. The json() wrapper validates/canonicalizes JSON text; json_quote() converts an SQL scalar into its JSON representation.

SELECT json_object(         'device','PUMP-007',         'active',1,         'tags',json_array('pump','critical'),         'network',json('{"protocol":"modbus","port":502}'),         'note',json_quote('O''Brien valve')       ) AS document;SELECT json_object('payload','[1,2]') AS quoted_text,       json_object('payload',json('[1,2]')) AS nested_array;

The second query is a useful debugging pair: the first output contains a JSON string "[1,2]"; the second contains an actual JSON array [1,2].

Build report columns before building an API object

Do not jump from a large document directly to one giant JSON-producing expression. First expose ordinary relational report columns. That makes types, missing values, and joins observable.

SELECT d.device_code,       s.site_name,       d.status,       p.installed_at,       json_extract(p.metadata,'$.firmware.version') AS firmware_version,       json_extract(p.metadata,'$.firmware.channel') AS firmware_channel,       json_extract(p.metadata,'$.calibration.due') AS calibration_dueFROM device AS dJOIN site AS s ON s.site_id=d.site_idJOIN device_profile AS p ON p.device_id=d.device_idORDER BY d.device_code;

Only after this report is correct should an application decide whether to return rows, construct a JSON object per row, or aggregate rows into an API response.

Failure case: confusing SQL text with JSON structure

A common bug is to pass text that contains JSON punctuation into json_object() and assume SQLite will parse it automatically. It will not, because accepting arbitrary-looking strings as structure would make value boundaries ambiguous.

-- Produces {"sensor":"{"kind":"temperature"}"}SELECT json_object('sensor','{"kind":"temperature"}');-- Produces {"sensor":{"kind":"temperature"}}SELECT json_object('sensor',json('{"kind":"temperature"}'));

When the input is untrusted external text, validation is an advantage, not an inconvenience.

Lab: extract then reconstruct a device summary

WITH report AS (  SELECT d.device_code,         s.site_name,         d.status,         json_extract(p.metadata,'$.firmware.version') AS firmware_version,         json_extract(p.metadata,'$.network.protocol') AS protocol  FROM device AS d  JOIN site AS s ON s.site_id=d.site_id  JOIN device_profile AS p ON p.device_id=d.device_id)SELECT device_code,       json_object(         'device_code',device_code,         'site',site_name,         'status',status,         'firmware_version',firmware_version,         'protocol',protocol       ) AS api_shapeFROM reportORDER BY device_code;

Inspect both columns. If a protocol path is absent, the SQL value is NULL and json_object() emits JSON null for that value. Decide whether your API wants explicit null or omission; those are different contracts.

Verification checkpoint

Production judgment and bridge

Paths let one row expose nested values, but real JSON documents often contain arrays that must be searched or joined. Lesson 3 turns those nested elements into rows using SQLite table-valued JSON traversal.

Authoritative references

• SQLite JSON path, extraction, and construction documentation
• SQLite expression syntax
• SQLite release history