Why does a JSON missing-field report also list explicit null values?

Prepare the report input

Original queue lists documents whose note key is absent. json_extract returns SQL NULL for both an absent path and JSON null. json_type returns the text “null” for an explicit JSON null, but SQL NULL for an absent path. Every payload is valid JSON here.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE documents(id TEXT PRIMARY KEY,payload TEXT NOT NULL CHECK(json_valid(payload))); INSERT INTO documents VALUES('A','{"note":null}'),('B','{}'),('C','{"note":"ready"}');

Compare misleading and corrected results

SELECT id FROM documents WHERE json_extract(payload,'$.note') IS NULL ORDER BY id;

SELECT id FROM documents WHERE json_type(payload,'$.note') IS NULL ORDER BY id;

The misleading list includes A and B; only B actually lacks the key. A’s explicit null is retained in the source instead of being treated as an absent field.

Check a fresh boundary case

A supplied empty note string also means the key exists and does not enter the missing-key queue.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

INSERT INTO documents VALUES('D','{"note":""}');
SELECT id FROM documents WHERE json_type(payload,'$.note') IS NULL ORDER BY id;