Why does a section report count nested details as another section?

Prepare the report input

The fixture requires sections to be an array; the report counts its direct object entries. The first section contains a details object, which is not a second section. json_tree walks descendants recursively; json_each at the sections path visits the direct children only.

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)) CHECK(json_type(payload,'$.sections') IS 'array')); INSERT INTO documents VALUES('A','{"sections":[{"title":"First","details":{"memo":"x"}},{"title":"Second"}]}');

Compare misleading and corrected results

SELECT d.id,(SELECT COUNT(*) FROM json_tree(d.payload,'$.sections') WHERE type='object') AS sections FROM documents d ORDER BY d.id;

SELECT d.id,(SELECT COUNT(*) FROM json_each(d.payload,'$.sections') WHERE type='object') AS sections FROM documents d ORDER BY d.id;

Recursive object count is three, while direct section count is two. A scalar correlated count keeps each document visible even when its sections list is empty.

Check a fresh boundary case

Replace the list with []. The corrected section count becomes zero for the same document.

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.

UPDATE documents SET payload='{"sections":[]}' WHERE id='A';
SELECT d.id,(SELECT COUNT(*) FROM json_each(d.payload,'$.sections') WHERE type='object') AS sections FROM documents d ORDER BY d.id;