Why does an empty-lines report treat an object as an empty array?

Prepare the report input

Original report needs an actual empty lines array. json_array_length returns zero for a located non-array too, so length zero alone admits an object. Require json_type=array as well as zero length. Payload syntax is valid but shape may differ.

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','{"lines":[]}'),('B','{"lines":{}}'),('C','{"lines":[1]}');

Compare misleading and corrected results

SELECT id FROM documents WHERE json_array_length(payload,'$.lines')=0 ORDER BY id;

SELECT id FROM documents WHERE json_type(payload,'$.lines')='array' AND json_array_length(payload,'$.lines')=0 ORDER BY id;

A has [], B has {} and C has [1]. Only A is an empty array. B is a different shape requiring separate review, not a zero-line document.

Check a fresh boundary case

Add lines:null. It remains outside the empty-array result.

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','{"lines":null}');
SELECT id FROM documents WHERE json_type(payload,'$.lines')='array' AND json_array_length(payload,'$.lines')=0 ORDER BY id;